Access Control in Oracle

Buğra PARLAYAN

1 year ago

In today’s article, we will learn how to see users who access Oracle Database or users who get an error while accessing.

SELECT username                               AS "DB USER",
          os_username                         AS "OS USER",
          userhost                            AS "HOSTNAME",
          TO_CHAR (timestamp, 'DD/MM/YY HH24:MI:SS')"TIMESTAMP",
          DECODE (
              returncode,
              00911, 'INCORRECT CHARACTER INPUT',
              01004, 'ACCESS DISABLED',
              01005, 'NULL PASSWORD ENTERED',
              01017, 'WRONG USER OR PASSWORD',
              01031, 'UNAUTHORIZED TRIAL',
              01045, 'TRIAL WITHOUT CREATE SESSION AUTHORITY',
              01918, 'NON-DB USER',
              01920, 'NON-DB ROLE',
              28000, 'LOCKED ACCOUNT TRIAL',
              28001, 'PASSWORD EXPIRED',
              28009, 'SYSDBA OR SYSOPER TRIAL')
              AS return code
     FROM SYS.DBA_AUDIT_SESSION
    WHERE returncode != 0 AND -- timestamp > SYSDATE - 1 of 9 -- AND returncode IN (1017, 28000)
ORDER BY extended_timestamp DESC

SELECT username AS "DB USER",

os_username AS "OS USER",

userhost AS "HOSTNAME",

TO_CHAR (timestamp, 'DD/MM/YY HH24:MI:SS')"TIMESTAMP",

DECODE (

returncode,

00911, 'INCORRECT CHARACTER INPUT',

01004, 'ACCESS DISABLED',

01005, 'NULL PASSWORD ENTERED',

01017, 'WRONG USER OR PASSWORD',

01031, 'UNAUTHORIZED TRIAL',

01045, 'TRIAL WITHOUT CREATE SESSION AUTHORITY',

01918, 'NON-DB USER',

01920, 'NON-DB ROLE',

28000, 'LOCKED ACCOUNT TRIAL',

28001, 'PASSWORD EXPIRED',

28009, 'SYSDBA OR SYSOPER TRIAL')

AS return code

FROM SYS.DBA_AUDIT_SESSION

WHERE returncode != 0 AND -- timestamp > SYSDATE - 1 of 9 -- AND returncode IN (1017, 28000)

ORDER BY extended_timestamp DESC