First, we create a table to record those logins:
CREATE TABLE HELPDESK.FAILED_LOGINS
(
USERNAME VARCHAR2 (30 BYTE),
OSUSER VARCHAR2 (30 BYTE),
TERMINAL VARCHAR2 (30 BYTE),
IP VARCHAR2 (64 BYTE),
MODULE VARCHAR2 (48 BYTE),
TIME DATE
);
CREATE INDEX HELPDESK.FAILED_LOGINS_IDX
ON HELPDESK.FAILED_LOGINS (TIME);
The following trigger will check some database users and will allow only specific os users to login.
CREATE OR REPLACE TRIGGER SYS.AUTH_LOGIN
AFTER LOGON
ON DATABASE
DECLARE
BEGIN
IF DBMS_STANDARD.LOGIN_USER IN
('APPS',
'APPLSYS',
'AP',
'AR',
'AX',
'GL',
'INV',
'ONT',
'PO',
'HR',
'XXFSM')
AND SYS_CONTEXT ('USERENV', 'OS_USER') NOT IN ('oracle', 'oracol')
THEN
INSERT INTO HELPDESK.FAILED_LOGINS (USERNAME,
OSUSER,
TERMINAL,
IP,
MODULE,
TIME)
VALUES (DBMS_STANDARD.LOGIN_USER,
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'TERMINAL'),
SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT ('USERENV', 'MODULE'),
SYSDATE);
COMMIT;
RAISE_APPLICATION_ERROR (-20001, 'Unauthorized login');
END IF;
END;
/
We try to login as APPS and we are not allowed:
SQL> CONN APPS@COLDB_RP1
Enter password: ****
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Unauthorized login
ORA-06512: at line 31
The attempt is recorded:
SQL> SELECT * FROM HELPDESK.FAILED_LOGINS;
USERNAME | OSUSER | TERMINAL | IP | MODULEL | TIME |
APPS | etsekouras | ETSEKOUR | 10.100.25.60 | sqlplusw.exe | 29-MAR-12 |
If I add my os username in the trigger, I am allowed access:
SQL> CONN APPS@COLDB_RP1
Enter password: ****
Connected.
No comments:
Post a Comment