Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: November 14, 2018 - 11:40 am UTC

Last updated: November 14, 2019 - 10:24 pm UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

Hello, Ask TOM Team.

I want to know if there's a straightforward (not using triggers or things like that lol) way to log DML, DDL and DCL user activity on specific objects (12c). I do not know if Database Vault can help me with that. Any Documentation or advice?

Thanks in advanced.

Regards,

and Chris said...

It sounds like you want to enable auditing. This will capture who did what, for example:

audit all 
  by chris by access;
audit select table, update table, insert table, delete table 
  by chris by access;
audit execute procedure 
  by chris by access;

conn chris/chris

create table audit_this (
  c1 int
);
insert into audit_this values ( 1 );
commit;
select * from audit_this;

select username,
       extended_timestamp,
       owner,
       obj_name,
       action_name
from   dba_audit_trail
where  owner = user
and    obj_name = 'AUDIT_THIS'
order by timestamp desc;

USERNAME   EXTENDED_TIMESTAMP                           OWNER   OBJ_NAME     ACTION_NAME    
CHRIS      14-NOV-18 16.40.54.788806000 EUROPE/LONDON   CHRIS   AUDIT_THIS   SELECT         
CHRIS      14-NOV-18 16.40.53.235856000 EUROPE/LONDON   CHRIS   AUDIT_THIS   CREATE TABLE   
CHRIS      14-NOV-18 16.40.53.759381000 EUROPE/LONDON   CHRIS   AUDIT_THIS   INSERT  


You can read more about this at:

https://docs.oracle.com/en/database/oracle/oracle-database/18/dbseg/introduction-to-auditing.html#GUID-94381464-53A3-421B-8F13-BD171C867405
https://oracle-base.com/articles/10g/auditing-10gr2

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Review

Geraldo Peralta, November 16, 2018 - 4:28 pm UTC

Great!

Thanks for the response.

Does the solution work for Standard edition?

A reader, March 09, 2019 - 1:44 am UTC

We are using Oracle 12.1.0.2 Standard edition ? Above solution for audit for user does not seem to work.

Our initialization parameter audit_trail is set to DB.
Connor McDonald
March 09, 2019 - 2:59 am UTC

We'd need to see a full test case

SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

1 row selected.

SQL> audit all
  2    by scott by access;

Audit succeeded.

SQL> audit select table, update table, insert table, delete table
  2    by scott by access;

Audit succeeded.

SQL> audit execute procedure
  2    by scott by access;

Audit succeeded.

SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL> create table audit_this (
  2    c1 int
  3  );

Table created.

SQL> insert into audit_this values ( 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from audit_this;

        C1
----------
         1

1 row selected.

SQL>
SQL> conn / as sysdba
Connected.
SQL> select username,
  2         extended_timestamp,
  3         owner,
  4         obj_name,
  5         action_name
  6  from   dba_audit_trail
  7  where  obj_name = 'AUDIT_THIS'
  8  order by timestamp desc;

USERNAME             EXTENDED_TIMESTAMP                                                          OWNER
-------------------- --------------------------------------------------------------------------- -------------
OBJ_NAME
--------------------------------------------------------------------------------------------------------------
ACTION_NAME
----------------------------
SCOTT                09-MAR-19 10.58.52.137000 AM +08:00                                         SCOTT
AUDIT_THIS
CREATE TABLE

SCOTT                09-MAR-19 10.58.52.137000 AM +08:00                                         SCOTT
AUDIT_THIS
SELECT

SCOTT                09-MAR-19 10.58.52.137000 AM +08:00                                         SCOTT
AUDIT_THIS
INSERT


3 rows selected.



Thanks! working now

A reader, March 12, 2019 - 3:42 pm UTC

Thank you Connor. It is working in Standard Edition.

My test case was using which succeeded but does not work.

grant select ANY table by scott by access;

Also, any change to audit of a user takes effect only for new session of the user.
Chris Saxon
March 12, 2019 - 3:50 pm UTC

Great to hear.

It is not working for me in the AWS RDS oracle instance

ANISH, November 14, 2019 - 9:24 am UTC

select banner from v$version where rownum = 1;


Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
audit all  by act_dev by access;
audit select table, update table, insert table, delete table   by act_dev by access;
audit execute procedure  by act_dev by access;

CREATE TABLE test_audit1 (
    c1   INT
);

INSERT INTO test_audit1 VALUES ( 1 );

COMMIT;

SELECT
    *
FROM
    test_audit1;


select username,
       extended_timestamp,
       owner,
       obj_name,
       action_name
from   dba_audit_trail;


Below output shows nothing in the dba_audit_trail.
SYSTEM 09-NOV-16 08.45.19.888019000 PM ASIA/CALCUTTA PUBLIC PRODUCT_PROFILE DROP PUBLIC SYNONYM
SYSTEM 09-NOV-16 08.45.19.907211000 PM ASIA/CALCUTTA PUBLIC PRODUCT_USER_PROFILE DROP PUBLIC SYNONYM
SYSTEM 09-NOV-16 08.45.19.750495000 PM ASIA/CALCUTTA LOGON
CTXSYS 09-NOV-16 08.45.46.066837000 PM ASIA/CALCUTTA LOGON
SYSTEM 09-NOV-16 08.45.19.897967000 PM ASIA/CALCUTTA PUBLIC PRODUCT_PROFILE CREATE PUBLIC SYNONYM
SYSTEM 09-NOV-16 08.45.19.912750000 PM ASIA/CALCUTTA PUBLIC PRODUCT_USER_PROFILE CREATE PUBLIC SYNONYM
ORACLE_OCM 09-NOV-16 08.45.19.520220000 PM ASIA/CALCUTTA LOGOFF
SYSTEM 09-NOV-16 08.45.19.915432000 PM ASIA/CALCUTTA LOGOFF
CTXSYS 09-NOV-16 08.45.46.383068000 PM ASIA/CALCUTTA LOGOFF

Connor McDonald
November 14, 2019 - 10:24 pm UTC

That is something you'll need to take up with RDS....

SQL> conn system/admin
Connected.

SQL>
SQL> drop user act_dev cascade;

User dropped.

SQL>
SQL> create user act_dev identified by act_dev;

User created.

SQL> grant connect, resource to act_Dev;

Grant succeeded.

SQL> alter user act_Dev quota 200m on users;

User altered.

SQL>
SQL> audit all  by act_dev by access;

Audit succeeded.

SQL> audit select table, update table, insert table, delete table   by act_dev by access;

Audit succeeded.

SQL> audit execute procedure  by act_dev by access;

Audit succeeded.

SQL>
SQL> conn act_dev/act_dev
Connected.

SQL>
SQL> CREATE TABLE test_audit1 (
  2      c1   INT
  3  );

Table created.

SQL>
SQL> INSERT INTO test_audit1 VALUES ( 1 );

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT
  2      *
  3  FROM
  4      test_audit1;

        C1
----------
         1

1 row selected.

SQL>
SQL> conn system/admin
Connected.

SQL>
SQL> select username,
  2         extended_timestamp,
  3         owner,
  4         obj_name,
  5         action_name
  6  from   dba_audit_trail
  7  where username = 'ACT_DEV'
  8  @pr
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.421000 AM +08:00
OWNER                         : ACT_DEV
OBJ_NAME                      : TEST_AUDIT1
ACTION_NAME                   : CREATE TABLE
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.343000 AM +08:00
OWNER                         :
OBJ_NAME                      :
ACTION_NAME                   : LOGON
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.436000 AM +08:00
OWNER                         : ACT_DEV
OBJ_NAME                      : TEST_AUDIT1
ACTION_NAME                   : INSERT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.343000 AM +08:00
OWNER                         : SYSTEM
OBJ_NAME                      : SQLPLUS_PRODUCT_PROFILE
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.343000 AM +08:00
OWNER                         : SYSTEM
OBJ_NAME                      : PRODUCT_PRIVS
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.343000 AM +08:00
OWNER                         : SYSTEM
OBJ_NAME                      : SQLPLUS_PRODUCT_PROFILE
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.343000 AM +08:00
OWNER                         : SYSTEM
OBJ_NAME                      : PRODUCT_PRIVS
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.358000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : DUAL
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.358000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : X$KSUXSINST
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.358000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : X$KVIT
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.358000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : X$QUIESCE
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.358000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : X$KJIDT
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.358000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : GV$INSTANCE
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.358000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : V$INSTANCE
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.358000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : V_$INSTANCE
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.483000 AM +08:00
OWNER                         : ACT_DEV
OBJ_NAME                      : TEST_AUDIT1
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.343000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : DUAL
ACTION_NAME                   : SELECT
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.358000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : DBMS_APPLICATION_INFO
ACTION_NAME                   : EXECUTE PROCEDURE
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.421000 AM +08:00
OWNER                         : SYS
OBJ_NAME                      : DBMS_STANDARD
ACTION_NAME                   : EXECUTE PROCEDURE
==============================
USERNAME                      : ACT_DEV
EXTENDED_TIMESTAMP            : 15-NOV-19 06.23.19.499000 AM +08:00
OWNER                         :
OBJ_NAME                      :
ACTION_NAME                   : LOGOFF

PL/SQL procedure successfully completed.


SQL>


More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.