Auditing GRANT activities

[jigitty]

Objective
=========
I want to audit any grants made on my database objects. In addition to the basic information(timestamp, grantor, DB object, machine name, IP address etc.), I want to track the level of access granted (insert, delete, update, select) and the grantee.

Current Solution
================
1. Going forward - for new tables, I can execute "AUDIT GRANT TABLE BY ACCESS"
2. For existing tables - I have to execute "AUDIT GRANT ON tablename BY ACCESS" for every single table

For #2, I can generate a script off of SYS.ALL_OBJECTS

Questions
=========
1. Is there a simpler way of achieving the objective?
2. The OBJ_PRIVILEGE column in SYS.DBA_AUDIT_TRAIL has Y in certain positions, depending on which access was granted. I can run tests and figure out which ones correspond to insert/delete/update/select but is there a comprehensive list to explain which position corresponds to which access.

Thanks for all your help!

Jigitty

 

[SantaMufasa]

Is there a simpler way of achieving the objective?

Actually, I'm not clear or "the objective". Are you interested in a snapshot in time of WHO granted WHOM WHAT access on WHICH table? For that, you can

select GRANTEE, GRANTOR, TABLE_NAME, PRIVILEGE
from user_tab_privs;

For more granular monitoring of privileges down to the column level (which relates to your second question, then you can query on "user_col_privs", which has the following column information:

GRANTEE
OWNER
TABLE_NAME
COLUMN_NAME
GRANTOR
PRIVILEGE
GRANTABLE

To monitor grants at the moment when they occur, then your AUDIT suggestion is probably better. I just don't like using the AUDIT command because of the on-going overhead that it creates while it is on.



Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[jigitty]

Mufasa,

Tracking WHEN the grant occurred is the main objective, in addition to WHO, WHOM, WHAT & WHICH.

What are the main concerns with turning on the auditing for grants? I already have it on for DDL statements.

Also, I don't see any rows in user_tab_privs nor user_col_privs. I have rows in dba_audit_trail for grant statements. What am I missing?

Appreciate your input.

Jiggity.

 

[SantaMufasa]

Jigitty,

If WHEN is a main interest, then yes, AUDIT is your best alternative. If all you are auditing is GRANT and DDL, then you shouldn't be a major performance concern. Use AUDIT in good health .

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.