Auditing privileges

Generally complex applications use multiple schemas to query and save data. Also an attacker who connects successfully to a schema, such as system, may quickly attempt to exploit the additional access provided by select any, delete any, insert, and update any privileges.

To track these activities we need to audit these higher level privileges in order to ensure that we are capturing the use of them.

In this recipe we will grant select any table, delete any table, and update any table to users SMAVRIS and DREPHNEAL. Next, we will start to audit these statements and execute select, delete, and update statements against the hr_emp_details_aud table.

Getting ready

All steps will be performed on the HACKDB database.

How it works...

  1. Connect as the user system, grant select any table, delete any table, and update any table to users DRAPHEAL and SMAVRIS as follows:
    SQL> conn system
Enter password:
Connected.
SQL>
SQL> grant select any table,delete any table,update any table to drapheal, smavris;

Grant succeeded.

SQL>
  2. Start auditing these privileges by access as follows:
    SQL> audit select any table, delete any table, update any table by access;

Audit succeeded.

SQL>
  3. As user SMAVRIS, issue the following statements:
    SQL> update hr.hr_emp_details_aud set salary=10 where department_name='Executive';

3 rows updated.

SQL> rollback
  2  ;

Rollback complete.

SQL> delete hr.hr_emp_details_aud where department_name='Executive';

3 rows deleted.

SQL> rollback;

Rollback complete.

SQL>
  4. As user DRAPHEAL issue a SELECT statement against the hr.emp_details_aud table as follows:
    SQL> select salary,first_name,last_name from hr.hr_emp_details_aud where Department_name='Executive';

    SALARY FIRST_NAME           LAST_NAME
---------- -------------------- -------------------------
     24000 Steven               King
     17000 Neena                Kochhar
     17000 Lex                  De Haan

SQL>
  5. Select from dba_audit_trail by adding the PRIV_USED in(' SELECT ANY TABLE', 'UPDATE ANY TABLE' and 'DELETE ANY TABLE') condition as follows:
    SQL> select os_username,username,terminal,timestamp,action,sql_text from dba_audit_trail where PRIV_USED in ('SELECT ANY TABLE','UPDATE ANY TABLE','DELETE ANY TABLE')
  2  /

OS_USERNAME   USERNAME  TERMINAL   TIMESTAMP  ACTION  SQL_TEXT
nodeorcl1orcl DRAPHEAL NODEORCL1  29-APR-12  3        select salary,first_name,last_name from hr.hr_emp_details_aud where Department_name='Executive'

nodeorcl1orcl  SMAVRIS  NODEORCL1  29-APR-12  6  update hr.hr_emp_details_aud set salary=10 where department_name='Executive'

nodeorcl1orcl SMAVRIS   NODEORCL1  29-APR-12  7   delete hr.hr_emp_details_aud where department_name='Executive'

SQL>

How it works...

The audit trails are collected in the dba_audit_command_trail and dba_audit_trail dictionary views.

There's more...

To find out which privileges are audited, use the DBA_PRIV_AUDIT_OPTS dictionary view.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.34.39