Audit for Create/Alter/Drop User/Role/Profile and Grant/Revoke object?

[mingichu]

Hi! All experts:

We just turned on Audit_Trail = DB. Recently, we are trying to create audit reports for the following actions:

ALTER PROFILE
CREATE ROLE
CREATE USER
ALTER USER
DROP USER
GRANT OBJECT
REVOKE OBJECT

In the DBA_Audit_Trail, it records the Action_Name, Obj_Name and Grantee, but it doesn't record the details such as
what object/system privileges have been granted to the roles or users, what attributes of the profiles have been changed, what roles/profile have been granted to the users, ...etc. We looked up other views related to auditing, but couldn't find any detail information either.

Does anyone know how to do it? Thanks in advance!!!

 

[jee]

I think if you want this level of detail, you will have to use system triggers (you can have triggers on CREATE, DROP, and ALTER, among others). Then within the trigger (as part of the WHEN clause or in the body) use the DICTIONARY_OBJ_TYPE attribute to determine the type of object on which the operation that fired the trigger is being performed. The DICTIONARY_OBJ_NAME attribute will tell you name of the object and DICTIONARY_OBJ_OWNER will give you the schema name of the object. To determine details of what changed, you will have to query system views.