OraFAQ Forum: Server Administration » how to verify if Audit Trail is capturing the latest

Home » RDBMS Server » Server Administration » how to verify if Audit Trail is capturing the latest (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to verify if Audit Trail is capturing the latest [message #683142]

Tue, 08 December 2020 08:06

wtolentino
Messages: 421
Registered: March 2005

Senior Member

I checked the parameters and see this values.

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB, EXTENDED

however, when I checked the dba_audit_trail view it looks like the rows in there are too old and is way back from 2019 (timestamp column). how do I let the audit trail to records the latest?

thank you,
warren

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683143 is a reply to message #683142]

Tue, 08 December 2020 08:10

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Oracle version is the first question, Warren Smile

Smile

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683144 is a reply to message #683142]

Tue, 08 December 2020 08:31

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Besides the oracle version, it would be most helpful to know exactly _how_ you "checked the dba_audit_trail view". And perhaps even what client program you are using - a GUI client like SQL Dev will paginate the results, so it could be you are only looking at the first page .... who knows what you are doing? We certainly don't.

select max(timestamp) newest,
       min(timestamp) oldest
from dba_audit_trail;

Also consider that audit rules can be changed. Perhaps new activity is simply not being audited.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683145 is a reply to message #683144]

Tue, 08 December 2020 09:21

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In addition, "audit_trail" parameter indicates that audit is activated, it does not mean you actually audit something.
Query "dba_stmt_audit_opts", "dba_priv_audit_opts", "dba_obj_audit_opts", "all_def_audit_opts" for old audit options.
"dba_audit_policies", "audit_unified_enabled_policies"... for fine-grained and unified audit.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683147 is a reply to message #683145]

Tue, 08 December 2020 10:50

wtolentino
Messages: 421
Registered: March 2005

Senior Member

database version is: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

/foru/forum/fa/14431/0/

/foru/forum/fa/14431/0/

I use this query:

select * from dba_audit_trail 
order by timestamp desc;

Attachment: dba_audit_trail_01.JPG
(Size: 35.34KB, Downloaded 2186 times)

[Updated on: Tue, 08 December 2020 10:51]

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683149 is a reply to message #683147]

Tue, 08 December 2020 10:55

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What about Ed's query which is more important than your abstract?
And my remarks?

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683150 is a reply to message #683145]

Tue, 08 December 2020 10:56

wtolentino
Messages: 421
Registered: March 2005

Senior Member

/foru/forum/fa/14433/0/

screen shot for dba_stmt_audit_opts.

Attachment: dba_stmt_audit_opts.JPG
(Size: 49.40KB, Downloaded 2154 times)

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683151 is a reply to message #683145]

Tue, 08 December 2020 10:57

wtolentino
Messages: 421
Registered: March 2005

Senior Member

screen shot for dba_obj_audit_opts.

/foru/forum/fa/14434/0/

/foru/forum/fa/14434/0/

Attachment: dba_obj_audit_opts.JPG
(Size: 33.32KB, Downloaded 2129 times)

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683152 is a reply to message #683151]

Tue, 08 December 2020 11:07

wtolentino
Messages: 421
Registered: March 2005

Senior Member

all_def_audit_opts returns values '-/-'
dba_audit_policies returns no rows.

SQL> select max(timestamp) newest,
  2         min(timestamp) oldest
  3  from dba_audit_trail;

NEWEST    OLDEST
--------- ---------
27-DEC-19 23-JUL-10

62030340 rows on dba_audit_trail

[Updated on: Tue, 08 December 2020 12:21]

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683153 is a reply to message #683150]

Tue, 08 December 2020 13:27

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

many sites block attachments/links. Many forum users (myself included) refuse to open them for the same reasons others block them. And if you are working from sqlplus (preferred) there is zero reason for screen shots. Text can easily be copied out of a command session.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683154 is a reply to message #683152]

Tue, 08 December 2020 13:35

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

wtolentino wrote on Tue, 08 December 2020 11:07

all_def_audit_opts returns values '-/-'
dba_audit_policies returns no rows.

If that is true, then you are not currently auditing anything at all.

Quote:

SQL> select max(timestamp) newest,
  2         min(timestamp) oldest
  3  from dba_audit_trail;

NEWEST    OLDEST
--------- ---------
27-DEC-19 23-JUL-10

62030340 rows on dba_audit_trail

And while you are currently not auditing anything, you have over 62 million audit records dating as far back as 10 years ago. I'd say it's long past time for a review of your audit policies and a cleanup of your audit trail. You need to investigate DBMS_AUDIT_MGMT, first to make sure your audit tables are in their own dedicated tablespace (not in SYSTEM or SYSAUX) and secondly to implement some housekeeping. I leave it as an exercise for the student to review DBMS_AUDIT_MGMT in the official docs, and ask specific questions about it as they arise.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683265 is a reply to message #683154]

Mon, 21 December 2020 09:44

wtolentino
Messages: 421
Registered: March 2005

Senior Member

the audit trail is a trigger based like right? for example if someone drops a table, creates a procedure, provides a privileges, … it will be recorded to the audit trail table.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683267 is a reply to message #683265]

Mon, 21 December 2020 10:05

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

it will be recorded to the audit trail table

Only if you audit these actions and/or privileges and if audit_trail instance parameter is set to DB or YES at the time these actions are executed or privileges used.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683331 is a reply to message #683267]

Wed, 30 December 2020 07:45

wtolentino
Messages: 421
Registered: March 2005

Senior Member

current audit_trail parameter value is 'DB, EXTENDED'.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683332 is a reply to message #683331]

Wed, 30 December 2020 08:32

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Warren, nothing is audited unless you configure the the database to audit it. That parameter controls where the audit records go, not whether they are generated. You need to issue commands such as these:

AUDIT SELECT TABLE, UPDATE TABLE BY scott, blake BY ACCESS;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE
      BY ACCESS
      WHENEVER NOT SUCCESSFUL;

(above examples from the docs, https://docs.oracle.com/cd/E11882_01/network.112/e36292/loe.htm )

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683436 is a reply to message #683332]

Wed, 13 January 2021 13:44

wtolentino
Messages: 421
Registered: March 2005

Senior Member

thanks John i read the link. for starters i just want to see all those that was given grants (roles, select, delete, insert, and update) like "grant select on … to …".

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683439 is a reply to message #683436]

Wed, 13 January 2021 20:08

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

wtolentino wrote on Wed, 13 January 2021 13:44

thanks John i read the link. for starters i just want to see all those that was given grants (roles, select, delete, insert, and update) like "grant select on … to …".

If you weren't specifically auditing for a selected event when the even occurred, then the db did not capture it. It you WERE specifically auditing for a selected event when the even occurred, then it will be in the audit trail. Just take a look at what is available in DBA_AUDIT_TRAIL and select accordingly. Don't ask someone to write your query for you. Give it your best shot, and if you can't get it then show your work and we'll try to lead you to self-discovery.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683442 is a reply to message #683436]

Thu, 14 January 2021 02:03

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

wtolentino wrote on Wed, 13 January 2021 19:44

thanks John i read the link. for starters i just want to see all those that was given grants (roles, select, delete, insert, and update) like "grant select on … to …".

If you want to audit when users are granted a role:

AUDIT GRANT ANY ROLE;

and so on.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683476 is a reply to message #683442]

Wed, 20 January 2021 11:08

wtolentino
Messages: 421
Registered: March 2005

Senior Member

thanks John I execute the command "AUDIT GRANT ANY ROLE" and checked what statement audits are in place by using the "sys.dba_stmt_audit_opts".

select audit_option, success, failure from sys.dba_stmt_audit_opts;

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS
SYSTEM AUDIT                             BY ACCESS  BY ACCESS
CREATE SESSION                           BY ACCESS  BY ACCESS
CREATE USER                              BY ACCESS  BY ACCESS
BECOME USER                              BY ACCESS  BY ACCESS
ALTER USER                               BY ACCESS  BY ACCESS
DROP USER                                BY ACCESS  BY ACCESS
ROLE                                     BY ACCESS  BY ACCESS
DIRECTORY                                BY ACCESS  BY ACCESS
CREATE ANY TABLE                         BY ACCESS  BY ACCESS
ALTER ANY TABLE                          BY ACCESS  BY ACCESS
DROP ANY TABLE                           BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS
GRANT ANY ROLE                           BY ACCESS  BY ACCESS
ALTER DATABASE                           BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS
ALTER PROFILE                            BY ACCESS  BY ACCESS
DROP PROFILE                             BY ACCESS  BY ACCESS
GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS
CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS
CREATE ANY JOB                           BY ACCESS  BY ACCESS
CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS
PLUGGABLE DATABASE                       BY ACCESS  BY ACCESS

then I issued this simple grant

grant APP_EXECUTE_ROLE_00 to appcodeowner;

when I query the dba_audit_trail by

select * from dba_audit_trail order by timestamp desc;

I do not see the most recent grant.

I checked again by this query:

SQL> select max(timestamp) latest_timestamp,
  2         min(timestamp) prior_timestamp
  3  from dba_audit_trail;

LATEST_TI PRIOR_TIM
--------- ---------
19-DEC-19 23-JUL-10

SQL>

I think I am missing something.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683477 is a reply to message #683476]

Wed, 20 January 2021 11:13

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

And if you do the grant in a fresh session?

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683478 is a reply to message #683477]

Wed, 20 January 2021 13:02

wtolentino
Messages: 421
Registered: March 2005

Senior Member

tried that by disconnect and connecting again. still not goes to the audit_trail.

SQL> select to_char(sysdate,'dd-Mon-yyyy hh:mi:ss am') dt from dual;

DT
-----------------------
20-Jan-2021 01:53:48 pm

SQL> grant APP_EXECUTE_ROLE_00 to appcodeowner;

Grant succeeded.

SQL> select max(timestamp) latest_timestamp,
  2         min(timestamp) prior_timestamp
  3  from dba_audit_trail;

LATEST_TI PRIOR_TIM
--------- ---------
19-DEC-19 23-JUL-10

SQL>

[Updated on: Wed, 20 January 2021 13:05]

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683479 is a reply to message #683478]

Wed, 20 January 2021 13:35

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

It would help if you showed the whole process: logon, enable the audit, logoff, logon, make the grant, query the audit trail. All in one SQL*Plus session, and do not do it as SYS.

Apart from that, have you relinked Oracle to disable traditional audit? You'll see that with

select value from v$option where parameter='Unified Auditing';

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683481 is a reply to message #683479]

Thu, 21 January 2021 09:13

wtolentino
Messages: 421
Registered: March 2005

Senior Member

thanks John. if i am correct audit is currently enable

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> select substr(user,1,20) username, to_char(sysdate,'dd-Mon-yyyy hh:mi:ss am') dt from dual;

USERNAME             DT
-------------------- -----------------------
W*********           21-Jan-2021 09:59:20 am

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB, EXTENDED
SQL>
SQL> select value from v$option where parameter='Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

SQL> grant APP_EXECUTE_ROLE_00 to appcodeowner;

Grant succeeded.

SQL> select max(timestamp) latest_timestamp,
  2         min(timestamp) prior_timestamp
  3  from dba_audit_trail;

LATEST_TI PRIOR_TIM
--------- ---------
19-DEC-19 23-JUL-10

SQL>

/foru/forum/fa/14466/0/

Attachment: debug_audit_trail.jpg
(Size: 107.68KB, Downloaded 1942 times)

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683482 is a reply to message #683481]

Thu, 21 January 2021 09:42

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Quote:

SQL> select value from v$option where parameter='Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

This shows that you have relinked Oracle to disable all traditional audit: you have only Unified Audit. So this whole topic has been a waste of time.

Report message to a moderator

Re: how to verify if Audit Trail is capturing the latest [message #683483 is a reply to message #683482]

Thu, 21 January 2021 14:35

wtolentino
Messages: 421
Registered: March 2005

Senior Member

thank you John i learned something new. i see there are rows on the view sys.UNIFIED_AUDIT_TRAIL.

Report message to a moderator

Previous Topic:	DB Time spent nowhere in AWR
Next Topic:	sys.x$dbgalertext alert log info text access

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 18:14:18 CST 2025