Comments for Auditing Enhancements (Audit Policies and Unified Audit Trail) in Oracle Database 12c Release 1 (12.1)
Daniel said...Hi Tim!
First of all, thanks for these articles, they are really helping me on preparing the 12c Certification.
Just one silly remark: grant create index to user won't work, for some unknown reason you have to specify ANY index.
Again, thank you for these articles.
Daniel said...Hi again Tim,
as I followed the article and executed the code, I realised you probably wanted to grant sequence instead since later on test user will be creating sequences not indexes.
Also, I'm on windows and moving orauniaud12.dll worked fine for me.
Whoops. Sorry. I've corrected the mistake to SEQUENCE. :)
Thanks for the feedback about the Windows stuff. It's good to know the docs are correct. :)
Daniel said...That was fast!
Let me ask you a silly question, Have you taken the 1z0-060 test already? Any recommendations? I've got the book (Mathew Morris one), your articles and my 10g OCP certification.
Any feedback would be much appreciated!
No. Not sat it yet. I've still got 2 more articles to write before I sit it. :)
I've heard other people say they used Matthew's book and they were fine.
Daniel said...Thanks Tim!
Wow 60 articles isn't enough...I'm so screwed.
Tim... said...Didn't realise there were that many. :)
Missing the ILM and RMAN articles, then I will sit it. If you have questions, mail me directly using "tim@" this domain name. :)
Daniel said...ILM and RMAN, those are going to be BIG articles, especially the ILM one.
DB Audit Specific said...Hi,
We have DB, Extended audit trail log on oracle 12c. Now we want to write specific terminal or IP log and leave others. For example: We want write audit from TERMINAL1 host and we don't want to write from TERMINAL2 host.
Is it possible?
That would be the audit condition. Like:
WHEN 'SYS_CONTEXT(''USERENV'', ''TERMINAL'') = ''TERMINAL1'''
You can see an example of this based on users in the article.
Own table audit log said...I don't want SELECT action because this action will write too many logs. So I am using SELECT ANY TABLE privileges in the my audit policy. But this privilege is not writing select own schema's table. I can't configure specify objects because we have too many tables. Question is How can I write logs that select from own schema's table??? OR How can I configure few schema's all table?
SELECT ANY TABLE will track the granting and revoking of this system privilege, not people selecting tables.
This is what conditions are for. For example.
CREATE AUDIT POLICY select_scott_policy
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT'''
EVALUATE PER SESSION
CONTAINER = CURRENT;
Own table audit log said...What is difference following 2 policies?
CREATE AUDIT POLICY select_scott_policy ACTIONS SELECT WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT''' EVALUATE PER SESSION CONTAINER = CURRENT;
CREATE AUDIT POLICY select_scott_policy ACTIONS SELECT; audit policy select_scott_policy by scott
Own table audit log said...I mean when a user selects from its own tables, SELECT ANY TABLE system privilege is not being used and as such I will not have any audit records. If I use SELECT actions in the policy and I configured that policy for SCOTT user. Then SCOTT user connecting to DB by TOAD.EXE there are more than 50 records only LOGON. So What should I do??? :)))
Tim... said...I see what your problem is. You only want to audit when tables owned by SCOTT are queried. That has to be done on a per-object basis in an audit policy. There is no SCHEMA level action for the target, only for the user triggering the audit.
You can just omit them when querying the audit trail, so you don't have to display them.
Tim... said...BTW: The difference between the two policies you listed is scope. One is applied at database level, but limits schema based on the WHEN clause. The other is applied on a user basis, not on the whole DB, so it doesn't need the WHEN clause.
Own table audit log said...We have multiple database and there are multiple users in those databases. So I need centralized user management product. Is there any product?
You can use directory services to authenticate.
I don't see this a lot as most applications now connect using a single DB user and handle authentication internally using LDAP, rather than expecting it to be done by the DB.
Own table audit log said...Hi Tim,
Thank you for your answer.We have a big problem.I configured SYSTEM_POLICY for system user.It was working no problem in last few days. But now "ORA-03113: end-of-file on communication channel" when system user connecting to db.
Audit_condition: SYS_CONTEXT('USERENV','CLIENT_PROGRAM_NAME') != 'Spotlight.exe'
Own table audit log said...Audit_option: Theare are multiple system privilege and actions.
Own table audit said...I don't know why it is not working. Please help me!!! Thanks
Own table audit said...It is logging to db when noaudit system_policy by system user. But system user can't logging to db when audit policy system_policy by system user.
Log on to the database using "/ as sysdba" (switch to the relevant container if using multitenant) and remove the policy completely. Then practice what you actually want in a test system before using it for something real.
Marcin said...Hi Tim
I can not find details about some columns and values in view AUDIT_UNIFIED_POLICIES.
What does it mean ALL value in AUDIT_OPTION or STANDARD_ACTION in AUDIT_OPTION_TYPE.
They are explained in the CREATE AUDIT POLICY documentation.
warren said...we used to select from this DBA_AUDIT_STATEMENT view to find info about grants of privileges on an object. currently we are now in 19c and using the unified auditing -- UNIFIED_AUDIT_TRAIL. how do we audit whenever someone issues the command grant...
grant select on table1 to user1;
grant select on table to role1;
Anonymous said...Hi Tim,
Could you please explain in brief about database audit and unified audit as I am not able to get it.
Warren: There are examples of audit policies for GRANT here.
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.