In the previous post - [ Click Here ], I outlined how we can audit database users logon and logoff activities using database triggers but here we will be using in-build database auditing feature for the same audit process hence no need to worry about coding database triggers for it.
Here we will be doing the simplest way to audit database users logon/logoff info using oracle database in-build feature - Audit Connect.
Login as sysdba and execute the following command to enable the oracle database users logon/logoff information.
SQL> audit connect;
Audit succeeded.
Let's connect with SCOTT user to check if it audits the logon.
SQL> conn scott
SQL> conn scott
Enter password:
Connected.
SQL> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> !date
Sun May 15 19:25:16 IST 2016
SQL> conn /as sysdba
Connected.
SQL> select OS_USERNAME, USERNAME, USERHOST, EXTENDED_TIMESTAMP, DBID from dba_audit_trail where username='SCOTT';
OS_USERNAME USERNAME USERHOST EXTENDED_TIMESTAMP DBID
--------------- --------------- ------------------------- ---------------------------------------- ----------
aime SCOTT dadvfa1015 14-AUG-09 12.05.45.081543 PM +05:30 4023503584
aime SCOTT dadvfa1015 14-AUG-09 12.05.45.266236 PM +05:30 4023503584
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 03.08.01.824926 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 03.08.32.194633 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 03.08.39.830102 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 03.15.52.717458 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 03.15.54.017694 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 03.34.03.395507 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 03.34.38.646395 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 07.24.10.688440 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 07.24.12.903179 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 07.25.12.692897 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 07.25.14.894100 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 07.40.10.746310 PM +05:30 515168651
oracle SCOTT rac2.rajdbsolutions.com 15-MAY-16 07.40.21.921919 PM +05:30 515168651
Following information can be obtained from DBA_AUDIT_TRAIL:
SQL> desc dba_audit_trail;
Name Null? Type
----------------------------------- -------- ------------------------
OS_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP DATE
OWNER VARCHAR2(30)
OBJ_NAME VARCHAR2(128)
ACTION NOT NULL NUMBER
ACTION_NAME VARCHAR2(28)
NEW_OWNER VARCHAR2(30)
NEW_NAME VARCHAR2(128)
OBJ_PRIVILEGE VARCHAR2(16)
SYS_PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(1)
GRANTEE VARCHAR2(30)
AUDIT_OPTION VARCHAR2(40)
SES_ACTIONS VARCHAR2(19)
LOGOFF_TIME DATE
LOGOFF_LREAD NUMBER
LOGOFF_PREAD NUMBER
LOGOFF_LWRITE NUMBER
LOGOFF_DLOCK VARCHAR2(40)
COMMENT_TEXT VARCHAR2(4000)
SESSIONID NOT NULL NUMBER
ENTRYID NOT NULL NUMBER
STATEMENTID NOT NULL NUMBER
RETURNCODE NOT NULL NUMBER
PRIV_USED VARCHAR2(40)
CLIENT_ID VARCHAR2(64)
ECONTEXT_ID VARCHAR2(64)
SESSION_CPU NUMBER
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME Z
ONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTIONID RAW(8)
SCN NUMBER
SQL_BIND NVARCHAR2(2000)
SQL_TEXT NVARCHAR2(2000)
OBJ_EDITION_NAME VARCHAR2(30)
DBID NUMBER
No comments:
Post a Comment