Oracle DBA Forums > AUDIT - List ALL AUDIT activate

Full Version: AUDIT - List ALL AUDIT activate

Oracle DBA Forums > Oracle > Oracle Forum

paulogervasio

Jan 6 2012, 12:31 PM

Hi
I need to list all AUDIT command issued.

I'm using the DBA_STMT_AUDIT_OPTS view, but it doesn't list all of them.

For example if a run this command:
audit create session by my_user;

the DBA_STMT_AUDIT_OPTS will list it.

But if I run this command:
audit select on my_user.my_table;

The DBA_STMT_AUDIT_OPTS list only the first.

Why the DBA_STMT_AUDIT_OPTS doesn't list the second audit command?
How can I track all the AUDIT commands issued?

Thks.
Paulo.

burleson

Jan 6 2012, 02:14 PM

Hi Paulo,

Please read, you misunderstand this view:

http://www.dba-oracle.com/t_dba_stmt_audit_opts.htm

**********************************************
>> need to list all AUDIT command issued.

You want to audit auditing acticvity?

Then try seeing the security views that shows grantor details:

http://dba-oracle.com/googlesearchsite_pro...&q=grantor+

Just curious, why would you want to know this?

You shoukd only allow one ID to issue security grants . . .

burleson

Jan 6 2012, 03:52 PM

Hi again Paulo,

The dba_audit_statement view it for auditing the audits statements, and dba_audit_statement shows all the auditing activity done on the instance. Hence, dba_audit_statement is a great way to make sure that a dishonest person does not grant privileges to another user without proper permission.

Please read carefully:

http://www.dba-oracle.com/t_dba_audit_statement.htm

CODE

select
username,
owner,
action_name,
priv_used
from
dba_audit_object
where
obj_name like 'SOMETAB%';

And the result comes back as:

USERNAME OWNER ACTION_NAME PRIV_USED
--------------- --------------- --------------- ----------------
CLAIM_SCHEMA CLAIM_SCHEMA CREATE TABLE CREATE TABLE
ALLPOWERFUL CLAIM_SCHEMA CREATE TABLE GRANT EXECUTE
ALLPOWERFUL ALLPOWERFUL CREATE TABLE CREATE TABLE

paulogervasio

Jan 6 2012, 07:20 PM

Hi!
Thanks for answer.

I wanna to remove all audit actions granted for one user. So I did:

select 'NOAUDIT ' || audit_option || ' BY ' || user_name || ';' from DBA_STMT_AUDIT_OPTS where user_name='MY_USER';

The output is like this:
NOAUDIT CREATE SESSION BY MY_USER;
NOAUDIT DROP ANY TABLE BY MY_USER;
etc.

And after I execute these NOAUDIT commands...

But, now I wanna also to list the AUDIT for objects, because DBA_STMT_AUDIT_OPTS list the audit only for the system privileges.

Do you know some table that list these " audit grants"?

Thnks;
Paulo.

burleson

Jan 6 2012, 11:32 PM

Hi Paulo,

>> Do you know some table that list these " audit grants"?

Sorry, I gave you a bad link!

Yes, isn't dba_audit_statements what you want?

http://www.dba-oracle.com/t_dba_audit_statement.htm

Also, see all tables with a grantor column:

CODE

select *
from
dba_tab_columns
where
column_name = 'GRANTOR';

You know, you can download a complete set of auditing scripts if you don;t want to write your own:

http://www.dba-oracle.com/oracle_scripts.htm

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.