Oracle Database 19c auditing with Multitenant Architecture

First I installed Oracle 19c in Docker container , enabled unified auditing, and created multiple pluggable databases:

export ORACLE_SID=ORCLCDB
cd $ORACLE_HOME/bin
#connect to root container
./sqlplus / as sysdba

CREATE PLUGGABLE DATABASE testpdb1 ADMIN USER admin1 IDENTIFIED BY dbpasswd1
    FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed', '/opt/oracle/oradata/ORCLCDB/testpdb1');

Pluggable database created.

CREATE PLUGGABLE DATABASE testpdb2 ADMIN USER admin2 IDENTIFIED BY dbpasswd2
    FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed', '/opt/oracle/oradata/ORCLCDB/testpdb2');

Pluggable database created.

Now I can list them with the following queries:

select PDB_NAME, STATUS from DBA_PDBS;

select NAME, OPEN_MODE from V$PDBS;

To be able to query the list of pluggable databases in SQL Developer I connect with SID, but not with Service Name:

Then I opened my test databases:

ALTER PLUGGABLE DATABASE testpdb1 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE testpdb2 OPEN READ WRITE;

Pluggable database altered.

Now their status is NORMAL and READ WRITE:

At this point /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora file content is

ORCLCDB=localhost:1521/ORCLCDB
ORCLPDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

and I am able to connect to PDB2 (and so in a similar way to PDB1) as follows:

export ORACLE_SID=ORCLCDB
cd $ORACLE_HOME/bin
./sqlplus sys as sysdba

ALTER SESSION SET CONTAINER=testpdb2;

To connect with admin1 and admin2 users I installed vim in Docker container:

yum install vim

and updated /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora file:

ORCLCDB=localhost:1521/ORCLCDB
ORCLPDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

TESTPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testpdb1)
    )
  )

TESTPDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testpdb2)
    )
  )

then I granted various permissions including the permission to create and enable audit policies to admin2 (and admin1 in the same way):

ALTER SESSION SET CONTAINER=testpdb2;
GRANT CREATE SESSION TO admin2;
GRANT CREATE ANY TABLE TO admin2;
GRANT AUDIT_ADMIN TO admin2;

Now I connect to testpdb2 with SQL Developer as follows:

or from the command line:

cd $ORACLE_HOME/bin
./sqlplus admin2/dbpasswd2@testpdb2

If I did multiple attempts with a wrong password I unlock the user:

ALTER USER admin1 IDENTIFIED BY dbpassword1 ACCOUNT UNLOCK;

Then I checked what audit options are enabled by default:

SHOW CON_NAME;
select value from v$option where lower(parameter)='unified auditing';
select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES;

then checked all available audit options we may in future consider adding:

select distinct POLICY_NAME from AUDIT_UNIFIED_POLICIES;

A query to check what options an audit policy includes:

select AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where POLICY_NAME='ORA_ACCOUNT_MGMT';

Created a test audit policy:

create audit policy my_sqldvper_audit_testpdb2 ACTIONS SELECT,DELETE,INSERT,UPDATE ON ADMIN2.TESTAUDIT;
AUDIT POLICY my_sqldvper_audit_testpdb2;

Created a sample table and inserted some rows:

DROP TABLE TESTAUDIT;
CREATE TABLE TESTAUDIT(ID NUMBER, NAME VARCHAR2(20));
GRANT SELECT, INSERT, UPDATE, DELETE ON TESTAUDIT TO admin2;

INSERT INTO TESTAUDIT (ID, NAME) VALUES (1,'Dmitry');
INSERT INTO TESTAUDIT (ID, NAME) VALUES (2,'Smith');
INSERT INTO TESTAUDIT (ID, NAME) VALUES (3,'Agent');
INSERT INTO TESTAUDIT (ID, NAME) VALUES (4,'Mortheus');
INSERT INTO TESTAUDIT (ID, NAME) VALUES (5,'Trinity');
commit;

Queried the audit events:

select dbusername, event_timestamp, sql_text, CLIENT_PROGRAM_NAME
    from unified_audit_trail order by event_timestamp desc;

Other queries I used:

select distinct AUDIT_OPTION_TYPE from AUDIT_UNIFIED_POLICIES;

AUDIT_OPTION_TYPE
------------------
OBJECT ACTION
DV ACTION
XS ACTION
STANDARD ACTION
SYSTEM PRIVILEGE

SELECT up.AUDIT_OPTION, uep.SUCCESS, uep.FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES uep, AUDIT_UNIFIED_POLICIES up 
    WHERE uep.POLICY_NAME = up.POLICY_NAME and up.AUDIT_OPTION_TYPE ='STANDARD ACTION';

Queries to audit logon/logoff:

select action_name, dbusername, event_timestamp, sql_text, CLIENT_PROGRAM_NAME from unified_audit_trail 
    where action_name ='LOGON' order by event_timestamp desc;

select action_name, dbusername, event_timestamp, sql_text, CLIENT_PROGRAM_NAME from unified_audit_trail 
    where action_name ='LOGOFF' order by event_timestamp desc;

An example of a custom policy (standard policies available does not support LOGON/LOGOFF, they track only failures):

CREATE AUDIT POLICY my_custom_actions ACTIONS
    CREATE TABLE,DROP TABLE,ALTER TABLE,GRANT,REVOKE,
    CREATE VIEW,DROP VIEW,CREATE PROCEDURE,
    ALTER PROCEDURE,RENAME,AUDIT,NOAUDIT,
    ALTER DATABASE,ALTER USER,ALTER SYSTEM,
    CREATE USER,CREATE ROLE,SET ROLE,DROP USER,
    DROP ROLE,CREATE TRIGGER,ALTER TRIGGER,
    DROP TRIGGER,CREATE PROFILE,DROP PROFILE,
    ALTER PROFILE,DROP PROCEDURE,
    CREATE MATERIALIZED VIEW,DROP MATERIALIZED VIEW,
    ALTER ROLE,TRUNCATE TABLE,CREATE FUNCTION,
    ALTER FUNCTION,DROP FUNCTION,CREATE PACKAGE,
    ALTER PACKAGE,DROP PACKAGE,CREATE PACKAGE BODY,
    ALTER PACKAGE BODY,DROP PACKAGE BODY,LOGON,LOGOFF,
    CREATE DIRECTORY,DROP DIRECTORY,CREATE JAVA,
    ALTER JAVA,DROP JAVA,PURGE TABLE,
    CREATE PLUGGABLE DATABASE,ALTER PLUGGABLE DATABASE,
    DROP PLUGGABLE DATABASE,CREATE AUDIT POLICY,
    ALTER AUDIT POLICY,DROP AUDIT POLICY,
    CREATE FLASHBACK ARCHIVE,ALTER FLASHBACK ARCHIVE,
    DROP FLASHBACK ARCHIVE;

AUDIT POLICY my_custom_actions;

We audit CDB in the same way:

SHOW CON_NAME;

CON_NAME 
------------------------------
CDB$ROOT

but audit policies are a bit different:

select distinct POLICY_NAME from AUDIT_UNIFIED_POLICIES;

and a bit different audit options:

select dbusername,event_timestamp,sql_text,CLIENT_PROGRAM_NAME
    from unified_audit_trail order by event_timestamp desc;

To audit all the containers I created a common user (because CDB does not have local users):

CREATE USER c##auditadmin IDENTIFIED BY aapassword1 CONTAINER=ALL;
GRANT AUDIT_ADMIN TO c##auditadmin CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##auditadmin CONTAINER=ALL;
GRANT CREATE SESSION TO c##auditadmin CONTAINER=ALL;

so I connect with command line:

./sqlplus c##auditadmin/aapassword1@orclcdb
./sqlplus c##auditadmin/aapassword1@testpdb2

The following query displays audit events from all the containers only if executed against CDB:

select dbusername, event_timestamp, sql_text, CLIENT_PROGRAM_NAME
    from CONTAINERS(unified_audit_trail) order by event_timestamp desc;

to make it work with my c##auditadmin user, I did the following:

./sqlplus sys as sysdba

CREATE VIEW c##auditadmin.MY_CDB_UNIFIED_AUDIT_TRAIL AS SELECT * FROM CDB_UNIFIED_AUDIT_TRAIL;

GRANT SELECT ON c##auditadmin.MY_CDB_UNIFIED_AUDIT_TRAIL TO c##auditadmin;

./sqlplus c##auditadmin/aapassword1@orclcdb

SELECT COUNT(*), CON_ID FROM MY_CDB_UNIFIED_AUDIT_TRAIL GROUP BY CON_ID;

select count(*) from MY_CDB_UNIFIED_AUDIT_TRAIL;

select dbusername, event_timestamp, sql_text, CLIENT_PROGRAM_NAME
    from MY_CDB_UNIFIED_AUDIT_TRAIL order by event_timestamp desc;

The owner of unified_audit_trail views in all the containers is AUDSYS user:

select OWNER, OBJECT_NAME, CON_ID from containers(DBA_OBJECTS) where OBJECT_NAME = UPPER('unified_audit_trail') AND OBJECT_TYPE = 'VIEW';

The owner of CDB_UNIFIED_AUDIT_TRAIL view:

select OWNER, OBJECT_NAME, CON_ID from containers(DBA_OBJECTS) where OBJECT_NAME = UPPER('CDB_UNIFIED_AUDIT_TRAIL');

The definition of CDB_UNIFIED_AUDIT_TRAIL view:

select DBMS_METADATA.GET_DDL ('VIEW','CDB_UNIFIED_AUDIT_TRAIL','AUDSYS') from dual;

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "AUDSYS"."CDB_UNIFIED_AUDIT_TRAIL"  CONTAINER_DATA 
 ("AUDIT_TYPE", "SESSIONID", "PROXY_SESSIONID", "OS_USERNAME", "USERHOST", "TERMINAL", "INSTANCE_ID", "DBID", "AUTHENTICATION_TYPE", "DBUSERNAME", "DBPROXY_USERNAME", "EXTERNAL_USERID", "GLOBAL_USERID", "CLIENT_PROGRAM_NAME", "DBLINK_INFO", "XS_USER_NAME", "XS_SESSIONID", "ENTRY_ID", "STATEMENT_ID", "EVENT_TIMESTAMP", "EVENT_TIMESTAMP_UTC", "ACTION_NAME", "RETURN_CODE", "OS_PROCESS", "TRANSACTION_ID", "SCN", "EXECUTION_ID", "OBJECT_SCHEMA", "OBJECT_NAME", "SQL_TEXT", "SQL_BINDS", "APPLICATION_CONTEXTS", "CLIENT_IDENTIFIER", "NEW_SCHEMA", "NEW_NAME", "OBJECT_EDITION", "SYSTEM_PRIVILEGE_USED", "SYSTEM_PRIVILEGE", "AUDIT_OPTION", "OBJECT_PRIVILEGES", "ROLE", "TARGET_USER", "EXCLUDED_USER", "EXCLUDED_SCHEMA", "EXCLUDED_OBJECT", "CURRENT_USER", "ADDITIONAL_INFO", "UNIFIED_AUDIT_POLICIES", "FGA_POLICY_NAME", "XS_INACTIVITY_TIMEOUT", "XS_ENTITY_TYPE", "XS_TARGET_PRINCIPAL_NAME", "XS_PROXY_USER_NAME", "XS_DATASEC_POLICY_NAME", "XS_SCHEMA_NAME", "XS_CALLBACK_EVENT_TYPE", "XS_PACKAGE_NAME", "XS_PROCEDURE_NAME", "XS_ENABLED_ROLE", "XS_COOKIE", "XS_NS_NAME", "XS_NS_ATTRIBUTE", "XS_NS_ATTRIBUTE_OLD_VAL", "XS_NS_ATTRIBUTE_NEW_VAL", "DV_ACTION_CODE", "DV_ACTION_NAME", "DV_EXTENDED_ACTION_CODE", "DV_GRANTEE", "DV_RETURN_CODE", "DV_ACTION_OBJECT_NAME", "DV_RULE_SET_NAME", "DV_COMMENT", "DV_FACTOR_CONTEXT", "DV_OBJECT_STATUS", "OLS_POLICY_NAME", "OLS_GRANTEE", "OLS_MAX_READ_LABEL", "OLS_MAX_WRITE_LABEL", "OLS_MIN_WRITE_LABEL", "OLS_PRIVILEGES_GRANTED", "OLS_PROGRAM_UNIT_NAME", "OLS_PRIVILEGES_USED", "OLS_STRING_LABEL", "OLS_LABEL_COMPONENT_TYPE", "OLS_LABEL_COMPONENT_NAME", "OLS_PARENT_GROUP_NAME", "OLS_OLD_VALUE", "OLS_NEW_VALUE", "RMAN_SESSION_RECID", "RMAN_SESSION_STAMP", "RMAN_OPERATION", "RMAN_OBJECT_TYPE", "RMAN_DEVICE_TYPE", "DP_TEXT_PARAMETERS1", "DP_BOOLEAN_PARAMETERS1", "DIRECT_PATH_NUM_COLUMNS_LOADED", "RLS_INFO", "KSACL_USER_NAME", "KSACL_SERVICE_NAME", "KSACL_SOURCE_LOCATION", "PROTOCOL_SESSION_ID", "PROTOCOL_RETURN_CODE", "PROTOCOL_ACTION_NAME", "PROTOCOL_USERHOST", "PROTOCOL_MESSAGE", "CON_ID", "CON$NAME", "CDB$NAME", "CON$ERRNUM", "CON$ERRMSG") AS 
  SELECT k."AUDIT_TYPE",k."SESSIONID",k."PROXY_SESSIONID",k."OS_USERNAME",k."USERHOST",k."TERMINAL",k."INSTANCE_ID",k."DBID",k."AUTHENTICATION_TYPE",k."DBUSERNAME",k."DBPROXY_USERNAME",k."EXTERNAL_USERID",k."GLOBAL_USERID",k."CLIENT_PROGRAM_NAME",k."DBLINK_INFO",k."XS_USER_NAME",k."XS_SESSIONID",k."ENTRY_ID",k."STATEMENT_ID",k."EVENT_TIMESTAMP",k."EVENT_TIMESTAMP_UTC",k."ACTION_NAME",k."RETURN_CODE",k."OS_PROCESS",k."TRANSACTION_ID",k."SCN",k."EXECUTION_ID",k."OBJECT_SCHEMA",k."OBJECT_NAME",k."SQL_TEXT",k."SQL_BINDS",k."APPLICATION_CONTEXTS",k."CLIENT_IDENTIFIER",k."NEW_SCHEMA",k."NEW_NAME",k."OBJECT_EDITION",k."SYSTEM_PRIVILEGE_USED",k."SYSTEM_PRIVILEGE",k."AUDIT_OPTION",k."OBJECT_PRIVILEGES",k."ROLE",k."TARGET_USER",k."EXCLUDED_USER",k."EXCLUDED_SCHEMA",k."EXCLUDED_OBJECT",k."CURRENT_USER",k."ADDITIONAL_INFO",k."UNIFIED_AUDIT_POLICIES",k."FGA_POLICY_NAME",k."XS_INACTIVITY_TIMEOUT",k."XS_ENTITY_TYPE",k."XS_TARGET_PRINCIPAL_NAME",k."XS_PROXY_USER_NAME",k."XS_DATASEC_POLICY_NAME",k."XS_SCHEMA_NAME",k."XS_CALLBACK_EVENT_TYPE",k."XS_PACKAGE_NAME",k."XS_PROCEDURE_NAME",k."XS_ENABLED_ROLE",k."XS_COOKIE",k."XS_NS_NAME",k."XS_NS_ATTRIBUTE",k."XS_NS_ATTRIBUTE_OLD_VAL",k."XS_NS_ATTRIBUTE_NEW_VAL",k."DV_ACTION_CODE",k."DV_ACTION_NAME",k."DV_EXTENDED_ACTION_CODE",k."DV_GRANTEE",k."DV_RETURN_CODE",k."DV_ACTION_OBJECT_NAME",k."DV_RULE_SET_NAME",k."DV_COMMENT",k."DV_FACTOR_CONTEXT",k."DV_OBJECT_STATUS",k."OLS_POLICY_NAME",k."OLS_GRANTEE",k."OLS_MAX_READ_LABEL",k."OLS_MAX_WRITE_LABEL",k."OLS_MIN_WRITE_LABEL",k."OLS_PRIVILEGES_GRANTED",k."OLS_PROGRAM_UNIT_NAME",k."OLS_PRIVILEGES_USED",k."OLS_STRING_LABEL",k."OLS_LABEL_COMPONENT_TYPE",k."OLS_LABEL_COMPONENT_NAME",k."OLS_PARENT_GROUP_NAME",k."OLS_OLD_VALUE",k."OLS_NEW_VALUE",k."RMAN_SESSION_RECID",k."RMAN_SESSION_STAMP",k."RMAN_OPERATION",k."RMAN_OBJECT_TYPE",k."RMAN_DEVICE_TYPE",k."DP_TEXT_PARAMETERS1",k."DP_BOOLEAN_PARAMETERS1",k."DIRECT_PATH_NUM_COLUMNS_LOADED",k."RLS_INFO",k."KSACL_USER_NAME",k."KSACL_SERVICE_NAME",k."KSACL_SOURCE_LOCATION",k."PROTOCOL_SESSION_ID",k."PROTOCOL_RETURN_CODE",k."PROTOCOL_ACTION_NAME",k."PROTOCOL_USERHOST",k."PROTOCOL_MESSAGE",k."CON_ID", k.CON$NAME, k.CDB$NAME, k.CON$ERRNUM, k.CON$ERRMSG 
    FROM CONTAINERS("AUDSYS"."UNIFIED_AUDIT_TRAIL") k

The user AUDSYS is locked for the security reason and we can’t unlock it with

ALTER USER AUDSYS IDENTIFIED BY audpassword1 ACCOUNT UNLOCK;
GRANT CREATE SESSION TO AUDSYS;

but we can switch to it from SYS user:

ALTER SESSION SET CURRENT_SCHEMA = AUDSYS;

and then create MY_CDB_UNIFIED_AUDIT_TRAIL view.

CDB_UNIFIED_AUDIT_TRAIL can be joined with gv$instance as it has INSTANCE_ID column, see the following sample queries:

select distinct CON_ID from CDB_UNIFIED_AUDIT_TRAIL;
select distinct INSTANCE_ID from CDB_UNIFIED_AUDIT_TRAIL;
select * from gv$instance;

A Query that displays my admin users:

select USERNAME, CON_ID, ACCOUNT_STATUS, COMMON from containers(DBA_USERS) where USERNAME like '%ADMIN%';

A query that displays common users:

select distinct USERNAME, ACCOUNT_STATUS from containers(DBA_USERS) where USERNAME like '%ADMIN%' and COMMON='YES';

Finally, one possible alternative of how to configure the user for audit is:

ALTER SESSION SET CURRENT_SCHEMA = SYS;
CREATE USER c##auditadmin IDENTIFIED BY aapassword1 CONTAINER=ALL;
GRANT SELECT ON CDB_UNIFIED_AUDIT_TRAIL TO c##auditadmin CONTAINER=ALL;
GRANT CREATE SESSION TO c##auditadmin CONTAINER=ALL;
ALTER SESSION SET CURRENT_SCHEMA = AUDSYS;
CREATE VIEW c##auditadmin.MY_CDB_UNIFIED_AUDIT_TRAIL AS SELECT * FROM CDB_UNIFIED_AUDIT_TRAIL;

/* test */
ALTER SESSION SET CURRENT_SCHEMA = c##auditadmin;
select CON_ID, dbusername, event_timestamp, sql_text, CLIENT_PROGRAM_NAME
    from MY_CDB_UNIFIED_AUDIT_TRAIL order by event_timestamp desc;

Links:

1 Response to Oracle Database 19c auditing with Multitenant Architecture

JAZIMA says:

May 28, 2023 at 6:07 pm

Thanks for the detailed explanation about unified auditing.
I have a scenario where an unknown process is deleting the DB services , I am trying to setup unified auditing at PDB level to capture the following

BEGIN DBMS_SERVICE.delete_service( service_name => 'TEST1' ); END;

I tired this at PDB level but it is not working as expected

ALTER AUDIT POLICY OIPL_AUDIT ADD PRIVILEGES EXECUTE ANY PROCEDURE;

This one is not working for PACKAGE

ALTER AUDIT POLICY OIPL_AUDIT ADD PRIVILEGES EXECUTE ANY PACKAGE;

Please help

DeveloperNote.com

A software developer's blog