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)
how to verify if Audit Trail is capturing the latest [message #683142] Tue, 08 December 2020 08:06 Go to next message
wtolentino
Messages: 341
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
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 Go to previous messageGo to next message
gazzag
Messages: 1097
Registered: November 2010
Location: Bedwas, UK
Senior Member
Oracle version is the first question, Warren Smile
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 Go to previous messageGo to next message
EdStevens
Messages: 1330
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67918
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
wtolentino
Messages: 341
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/

I use this query:
select * from dba_audit_trail 
order by timestamp desc;

[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 Go to previous messageGo to next message
Michel Cadot
Messages: 67918
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

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 Go to previous messageGo to next message
wtolentino
Messages: 341
Registered: March 2005
Senior Member


/foru/forum/fa/14433/0/

screen shot for dba_stmt_audit_opts.
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 Go to previous messageGo to next message
wtolentino
Messages: 341
Registered: March 2005
Senior Member

screen shot for dba_obj_audit_opts.

/foru/forum/fa/14434/0/
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 Go to previous messageGo to next message
wtolentino
Messages: 341
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 Go to previous messageGo to next message
EdStevens
Messages: 1330
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.
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 Go to previous messageGo to next message
EdStevens
Messages: 1330
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.
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 Go to previous messageGo to next message
wtolentino
Messages: 341
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67918
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
wtolentino
Messages: 341
Registered: March 2005
Senior Member
current audit_trail parameter value is 'DB, EXTENDED'.
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 Go to previous messageGo to next message
John Watson
Messages: 8576
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 )
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 Go to previous messageGo to next message
wtolentino
Messages: 341
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 …".


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 Go to previous messageGo to next message
EdStevens
Messages: 1330
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.
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 Go to previous messageGo to next message
John Watson
Messages: 8576
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.


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 Go to previous messageGo to next message
wtolentino
Messages: 341
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.
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 Go to previous messageGo to next message
John Watson
Messages: 8576
Registered: January 2010
Location: Global Village
Senior Member
And if you do the grant in a fresh session?
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 Go to previous messageGo to next message
wtolentino
Messages: 341
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 Go to previous messageGo to next message
John Watson
Messages: 8576
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';
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 Go to previous messageGo to next message
wtolentino
Messages: 341
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/
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 Go to previous messageGo to next message
John Watson
Messages: 8576
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.
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 Go to previous message
wtolentino
Messages: 341
Registered: March 2005
Senior Member
thank you John i learned something new. i see there are rows on the view sys.UNIFIED_AUDIT_TRAIL.
Previous Topic: DB Time spent nowhere in AWR
Next Topic: sys.x$dbgalertext alert log info text access
Goto Forum:
  


Current Time: Fri Jul 23 10:12:47 CDT 2021