8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

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.
Best regards,
Daniel.

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.

Best regards,
Daniel

Tim... said...

Hi.

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. :)

Cheers

Tim...

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!

Best regards,
Daniel.

Tim... said...

Hi.

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.

Cheers

Tim...

Daniel said...

Thanks Tim!

Wow 60 articles isn't enough...I'm so screwed.

Best Regards,
Daniel.

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. :)

Cheers

Tim...

Daniel said...

ILM and RMAN, those are going to be BIG articles, especially the ILM one.

Thanks again,

Daniel

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?

Tim... said...

Hi.

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.

Cheers

Tim...

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?

Tim... said...

Hi.

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
ACTIONS SELECT
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT'''
EVALUATE PER SESSION
CONTAINER = CURRENT;

Cheers

Tim...

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.

Cheers

Tim...

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?

Tim... said...

Hi.

You can use directory services to authenticate.

http://docs.oracle.com/database/121/DBSEG/authentication.htm#GUID-19D6CA32-D7BC-4640-9BF3-93D169E7D7A4

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.

Cheers

Tim...

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.
Policy_name: SYSTEM_POLICY
Audit_condition: SYS_CONTEXT('USERENV','CLIENT_PROGRAM_NAME') != 'Spotlight.exe'
Condition_eval_opt: statement

Own table audit log said...

Audit_option: Theare are multiple system privilege and actions.
User_name: SYSTEM
POLICY_NAME: SYSTEM_POLICY
ENABLED_OPT: BY
SUCCESS: YES
FAILURE: YES


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.

Tim... said...

Hi.

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.

Cheers

Tim...

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.
Thank you

Tim... said...

Hi.

They are explained in the CREATE AUDIT POLICY documentation.

https://docs.oracle.com/database/121/SQLRF/statements_5001.htm#SQLRF56055

Cheers

Tim...

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...

for example:

grant select on table1 to user1;
grant select on table to role1;

thank you.

Anonymous said...

Hi Tim,
Could you please explain in brief about database audit and unified audit as I am not able to get it.

Regards,

Tim... said...

HI.

Read this.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/introduction-to-auditing.html#GUID-94381464-53A3-421B-8F13-BD171C867405

Cheers

Tim...

Tim... said...

Hi.

Warren: There are examples of audit policies for GRANT here.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-audit-policies.html#GUID-22CDB667-5AA2-4051-A262-FBD0236763CB

Cheers

Tim...

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.