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.
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>
SQL> audit select any table, delete any table, update any table by access; Audit succeeded. SQL>
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>
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>
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>
The audit trails are collected in the dba_audit_command_trail
and dba_audit_trail
dictionary views.
18.118.37.154