The view UNIFIED_AUDIT_TRAIL is now a UNION ALL on AUD$UNIFIED and GV$UNIFIED_AUDIT_TRAIL.
We can see in the definition of the table AUD$UNIFIED that the SQL and binds are held in a secure file still ( here is a snippet from $ORACLE_HOME/rdbms/admin/catuat.sql) :
CREATE TABLE AUDSYS.AUD$UNIFIED (
INST_ID NUMBER,
...
OBJECT_TYPE NUMBER
)
LOB (SQL_TEXT, SQL_BINDS, RLS_INFO) STORE AS SECUREFILE (TABLESPACE SYSAUX)
PARTITION BY RANGE (EVENT_TIMESTAMP) INTERVAL(INTERVAL '1' DAY)
(PARTITION aud_unified_p0 VALUES LESS THAN
(TO_TIMESTAMP('2014-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
TABLESPACE SYSAUX) TABLESPACE SYSAUX;
Also of interest is the fact that this table is partitioned even if the customer does not have a partitioning license. We can see this in the SQL file (another snippet from $ORACLE_HOME/rdbms/admin/catuat.sql):
...
em Project 46892 - Introduce a new relational table AUDSYS.AUD$UNIFIED.
Rem Structure of this table should be same as that of GV$UNIFIED_AUDIT_TRAIL.
Rem ER 13716158 - Add CURRENT_USER column to capture the effective user name
Rem Bug 24974960 - Make this table as always partitioned, irrespective of db
Rem editions
Rem
Rem ER 30532917 - Make one day as default interval partition for
Rem AUDSYS.AUD$UNIFIED
-- Turns off partition check --
-- We would like to create a partitioned table even when Partitioning Option
-- is not Enabled.
alter session set events '14524 trace name context forever, level 1';
...
create table...
...
-- Turns on partition check --
alter session set events '14524 trace name context off';
grant read on sys.gv_$unified_audit_trail to audsys;
grant read on sys.all_unified_audit_actions to PUBLIC;
Rem Project 46892
Rem UNIFIED_AUDIT_TRAIL is now UNION ALL on gv$unified_audit_trail and
Rem new relational table AUDSYS.AUD$UNIFIED
So, the check for partitioning is controlled by an event so that the lack of a license in this case can be ignored. Also note that the table is created by SYS in the AUDSYS schema.
The focus of this set of articles though is the security of the audit trail and user. There are two main facts about the unified audit trail and these are 1) no one can log into the schema owner AUDSYS and 2) the audit trail itself is READONLY - which is not strictly true as it allows INSERT by the audit C code itself in the database engine as audit records are added and the DBMS_AUDIT_MGMT package can be used to truncate/delete based on factors such as delete records older than a date in the past. BUT, its not possible to delete specific records in the audit trail as could be done in the past with AUD$ direct via SQL
It is also worth pointing out a third security aspect is that parts of the audit trail can be written to SYSLOG since 18c. The record lengths are limited to avoid going over the 1024 line length set by the syslog RFC. Because the logs can be written to syslog it means that even if an attacker manages to find a way to circumvent the security of AUDSYS and AUD$UNIFIED the remaining audit trail can be verified against events written to syslog.
Connect as AUDSYS
So, Oracle has made AUDSYS so that it cannot be connected as. The creation of the user is without a password and locked as we can see in this snippet from $ORACLE_HOME/rdbms/admin/dsec.bsq:
REM LRG 22544275: Having no default tablespace for AUDSYS user could result in
REM ORA-01950 for temp object creations during say DBMS_REDEFINITION
create user AUDSYS no authentication account lock default tablespace SYSAUX
/
revoke inherit privileges on user AUDSYS from PUBLIC
/
alter user AUDSYS quota unlimited on SYSAUX
/
grant create table to AUDSYS
/
Nothing special or extra here that would stop a login after a password is added and unlocked. Let us try:
SQL> alter user audsys account unlock;
alter user audsys account unlock
*
ERROR at line 1:
ORA-65146: account cannot be unlocked in a PDB while it is locked in the root
SQL>
SQL> alter session set container = cdb$root;
Session altered.
SQL> alter user audsys account unlock;
User altered.
SQL>
SQL> alter user audsys identified by audsys;
User altered.
SQL>
SQL> connect audsys/audsys@//192.168.56.33:1539/xepdb1
ERROR:
ORA-46370: cannot connect as AUDSYS user
Warning: You are no longer connected to ORACLE.
SQL>
We cannot unlock or add a password for the AUDSYS user in the PDB as it is a COMMON user. So we did it in the root container and tried to log in but get an ORA-46370 error.
How does this work? :
SQL> set serveroutput on
SQL> @sc_print 'select * from dba_users where username=''''AUDSYS'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_users where username=''AUDSYS''','''','''''');
Executing Query [select * from dba_users where username='AUDSYS']
USERNAME : AUDSYS
USER_ID : 8
PASSWORD :
ACCOUNT_STATUS : LOCKED
LOCK_DATE : 20-JAN-22
EXPIRY_DATE :
DEFAULT_TABLESPACE : SYSAUX
TEMPORARY_TABLESPACE : TEMP
LOCAL_TEMP_TABLESPACE : TEMP
CREATED : 17-AUG-21
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS :
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : NONE
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : Y
INHERITED : YES
DEFAULT_COLLATION : USING_NLS_COMP
IMPLICIT : NO
ALL_SHARD : NO
EXTERNAL_SHARD : NO
PASSWORD_CHANGE_DATE :
MANDATORY_PROFILE_VIOLATION : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
The user details above do not show anything special that stands out and the creation of the user from the .bsq scripts did not show anything either so we must assume that Oracle internally checks if the connection is as AUDSYS and block it.
NOTE: Because Oracle do not want us to connect as AUDSYS we must assume that if we could connect as AUDSYS there is probably a way to either directly update audit records or delete them OR there is a way to change or nullify the protection on the audit trail
One other factor of AUDSYS is that it is dictionary protected in the same way as SYS. So that privileges with the keyword %ANY% do not work on AUDSYS objects. This is another layer of protection that would be harder to achieve without this feature. In fact it would need a Database Vault Realm. This is the feature that the O7_DICTIONARY_ACCESSIBILITY was used for in earlier releases. in 23c there is now a fine grained DICTIONARY PROTECTION that can be added to Oracle Maintained users.
In the case of AUDSYS then Oracle clearly do not want to allow %ANY% access to the audit trails.
We can prove this is the case. First create a user with SELECT ANY TABLE and check if we can access AUDSYS.AUD$UNIFIED:
SQL> @cs
Connected.
USER is "SYS"
SQL> create user aud1 identified by aud1;
User created.
SQL> grant create session, select any table to aud1;
Grant succeeded.
SQL>
SQL> connect aud1/aud1@//192.168.56.33:1539/xepdb1
Connected.
SQL> select count(*) from audsys.aud$unified;
select count(*) from audsys.aud$unified
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
We cannot access the table with SELECT ANY TABLE. What if we have SEELCT ANY DICTIONARY:
SQL> @cs
Connected.
USER is "SYS"
SQL> create user aud2 identified by aud2;
User created.
SQL> grant create session, select any dictionary to aud2;
Grant succeeded.
SQL>
SQL> connect aud2/aud2@//192.168.56.33:1539/xepdb1
Connected.
SQL> select count(*) from audsys.aud$unified;
COUNT(*)
----------
29743
SQL>
Yes, we can. So AUDSYS is treated like SYS but there is no obvious syntax in its creation to allow this so again probably hard coded in the C engine of Oracle.
Can we do DML/DDL on AUDSYS.AUD$UNIFIED?
First lets see if we can truncate the unified audit trail directly:
SQL> truncate table audsys.aud$unified;
truncate table audsys.aud$unified
*
ERROR at line 1:
ORA-46385: DML and DDL operations are not allowed on table
"AUDSYS"."AUD$UNIFIED".
SQL>
No, we are not allowed to truncate the AUD$UNIFIED table. What if we try and delete from the table:
SQL> delete from audsys.aud$unified;
delete from audsys.aud$unified
*
ERROR at line 1:
ORA-46385: DML and DDL operations are not allowed on table
"AUDSYS"."AUD$UNIFIED".
SQL>
This is interesting as we cannot directly change the AUD$UNIFIED table. I am aware of many possible ways this could be achieved and we will look at how Oracle might have implemented this in the next part as well as checking if certain technologies were used.
Why am I interested in the AUDSYS user and AUDSYS.AUD$UNIFIED table? it should be obvious really. If Oracle have ways in the database to create a user that cannot be logged into and where an asset can be protected or used only in ways that we allow then I want to do the same for my customers.
In my Secure Coding in PL/SQL Class I discuss a design pattern to secure something in the Oracle database. This could be a privilege, a resource, access externally or a database object. I have taught and used this design pattern for more than 15 years so when I see Oracle doing similar I want to know more. It looks that oracle use internal protections BUT it would be good if we could use the exact same design patterns.
If I was Oracle and i wanted to protect something like this i would probably hard code the rules in the C or at much better use some internal security policies that are not normally visible so that the rules and options can be extended in the future. For instance internal security policies can be as close to the C as possible and not end user/customer configurable but it means more rules can be added more easily. If we use standard database features to achieve the same then there is always a way to turn it off.
But, that is why I am interested. I want to have secure ways to block or control things BUT if Oracle made it generic someone can turn it off.
#oracleace #sym_42 #oracle #security #audit #trail #hacking #databreach #audittrail #unifiedaudit #grants #permissions #readonly
