Why audit the SYS user, when SYS is super user? Since we deal with critical and confidential data it becomes highly necessary to monitor even the SYS user for data integrity. Data integrity refers to the validity of data; meaning data is consistent and correct. As the need to derive meaningful information from large sets of data becomes a higher priority both in the government and private sector, Data Integrity is what gives users the assurance that the information they see is trustworthy.

There are recent legislation requiring accountability of all users in the system, including RDBMS, thus auditing SYS actions has become a top requirement.

This can be done by the new parameter AUDIT_SYS_OPERATIONS

By default the SYS operations are not monitored.

NAME                           VALUE                          ISDEFAULT
—————————— —————————— ———
audit_sys_operations           TRUE                           FALSE
audit_file_dest                ?/rdbms/audit                  TRUE
audit_trail                    NONE                           TRUE

The file location will be $ORACLE_HOME/rdbms/admin.

Once the auditing is complete set the audit_sys_operations=FALSE . A bounce of the database may be required to make this parameter take effect, as it is static.

Sometimes, even thought auditing is disabled we may find that audit files are getting generated. Although the audit_sys_operations is set to FALSE, the audit files were still been generated audit_file_dest. There is nothing to panic about this.  Regardless of whether database auditing is enabled, Oracle always audits certain database-related operations and writes them to the operating system audit file.

Enabling Auditing for a particular user.

* alter system set audit_trail=db scope=spfile
* restart the database

Once, action plan is completed, we can get the detials from dba_audit_trail


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(3)
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 ZONE
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)

select * from dba_audit_trail where USERNAME=’UserToBeAudited’;

And dont forget to turn off auditing cos it will cos extra overhead on you system resource.


Viewers of this post also viewed...