Unified Auditing in Oracle 18c

Oracle database Unified Audit Trail was introduced in Oracle 12cR1 , as a mechanism to unify different oracle database audits (based on different features) under one view. As you may know “mixed mode” auditing is enabled by default starting with 12cR1 release. One of the limitations when switching from “standard auditing” to “unified auditing” in both Oracle 12cR1 and 12cR2 is you can’t push audits to syslog anymore. This has changed in Oracle 18c, you can push audits to SYSLOG in Unix/Linux OS and to windows event log.

A new init parameter has been introduced “unified_audit_systemlog”

https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/UNIFIED_AUDIT_SYSTEMLOG.html#GUID-C4E65709-0FE3-4945-8F39-7B0867E2494A

Picture1

In window OS I have set the parameter as TRUE as shown below:

Picture2

For simulation through RMAN I have executed command to take controlfile backup then check the windows event log:

 

Picture3.png

Another new feature in 18c , is the ability to export and import unified audit trail !

Command to export:

expdp system/XXXXXXXX full=y directory=DUMP_DIR logfile=exp_unified18c_log.log dumpfile=exp_unified18c.dmp INCLUDE=AUDIT_TRAILS

Picture4

 

 

 

 

 

 

 

 

 

ALTER USER system privilege in Oracle 12cR2

In Oracle 12cR2 a new security enhancement has been introduced, the “alter user” system privilege when granted to an account….this account won’t be able to change “SYS” password anymore.

Let us explore “ALTER USER” system privilege in Oracle 12cR1 first:

1

2

 

3

 

4

while in Oracle 12cR2:

i have granted SYSTEM account the “ALTER USER” system privilege:

SQL> SELECT * FROM DBA_SYS_PRIVS where grantee=’SYSTEM’;

11

As a SYSTEM user I can’t alter the SYS user password as shown below:

12

The oracle error raised is:

ORA-01031: insufficient privileges

Not only you can’t change the password, you can’t change any activity that is related to the “ALTER USER” command such as changing the “profile”, “quota” ….etc

This feature will be very helpful if you have a segregation of duties in your database infrastructure  where account management is maintained by different entity which will definitely be granted “ALTER USER” system privilege for password management and reset….in this case they wont have the power to change the “SYS” user password.

Is there any workaround to go over this security restriction ?

Yes.

Under SYS schema create the following procedure:

SQL> create procedure sys.sys_pass_change(my_pass in varchar2) as

begin

execute immediate ‘alter user sys identified by ‘||my_pass;

end;

/

SQL> grant execute on sys.sys_pass_change to system;

Then, accessing the database as “SYSTEM” user:

SQL> exec sys.sys_pass_change(‘yarn_9ns’);

33

in this example The SYSTEM account will execute the procedure with “definer’s” right.

 

 

 

 

 

 

 

 

 

 

Oracle Virtual Private Database

The idea of Virtual Private Database is to enable users having access to a table to see ONLY subset of
data within the table. This technique is based on a function that will enable SQL execution to append a WHRE clause predicate. The subset of data can be from row-level or column-level perspective.

For demonstration, I will be using a table called EMP under SCOTT schema.

1.PNG

I will also create 2 accounts:
SQL> create user manager identified by mono670;
SQL>grant create session to to manager;
SQL>grant select on SCOTT.EMP to manager;
SQL>create user clerk identified by clerk332;

SQL>grant create session to clerk;
SQL>grant select on SCOTT.EMP to clerk;
connecting as accounts MANAGER & CLERK I can see the full table data !!!

2

3.PNG

Now I will create security administrator account:
SQL> alter session set container=CDB$root;
SQL> create user security_admin identified by zorro3_g;
SQL> grant create session to security_admin;
SQL> grant create procedure to security_admin;
Now I am going to create the function:
SQL> CREATE OR REPLACE FUNCTION VPD_FUNC
(schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN ‘upper(ename) = SYS_CONTEXT(”USERENV”, ”CURRENT_USER”) OR upper(job) =
SYS_CONTEXT(”USERENV”, ”CURRENT_USER”)’;
END;
/

 

4

Then as a sys user in the pluggable database PDB_ORIGIN configure the policy:
SQL> begin
sys.dbms_rls.add_policy(
object_schema=>’SCOTT’,
object_name=>’EMP’,
policy_name=>’VPD1_POLICY’,
function_schema=>’security_admin’,
policy_function=>’VPD_FUNC’,
statement_types => ‘SELECT’);
end;
/
Connecting as account “manager” I can see data related to “manager” records:

5

Same to “CLERK

6

This is a very nice security feature that many organizations can use to restrict  access to data within the table itself.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

INHERIT PRIVILEGES in Oracle 12c

Before Oracle 12c release, an Invoker Rights  unit always ran with the privileges of its invoker. If its invoker had higher privileges than its owner (for example, SYS, SYSTEM, or account with DBA role) then the IR unit might perform operations unintended by, or not authorized by the owner.

To explore first the behavior of invoker rights in 11g release, I have performed the following simulation test:

Oracle Database Release: 11.2.0.4.161018

Created a new user called “developer” with limited privileges:

CREATE USER developer

IDENTIFIED BY dodo_983

DEFAULT TABLESPACE TS_USER_DATA_01

TEMPORARY TABLESPACE TS_TEMP_DATA_01

PROFILE DEFAULT

ACCOUNT UNLOCK;

grant create session to developer ;

grant create procedure to developer ;

then the account will create a procedure:

CREATE OR REPLACE PROCEDURE priv_up  AUTHID CURRENT_USER AS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

EXECUTE IMMEDIATE ‘GRANT DBA TO developer’;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

/

Now the developer asks the DBA to execute multiple procedures under the schema (to trick him):

As SYS account you execute the SQL statement:

SQL> execute developer.priv_up();

 

11g-sys

 

And developer account now has DBA role !!!

Now this is a big security problem and its called “Privilege Escalation” …if your are a DBA and dealt with many third party applications, many of them provide *.sql scripts (tens of them sometimes) and you don’t have the time to inspect all sql code, this very difficult and hectic job.

 

In 12c This has been changed Invoker Rights unit can run with the privileges of its invoker only if its owner has either the INHERIT PRIVILEGES privilege on the invoker or the INHERIT ANY PRIVILEGES privilege.

A new account “developer” with the following definition:

CREATE USER developer

IDENTIFIED BY dodo_983

DEFAULT TABLESPACE TS_USER_DATA_01

TEMPORARY TABLESPACE TS_TEMP_DATA_01

PROFILE DEFAULT

ACCOUNT UNLOCK;

 

grant create session to developer ;

grant create procedure to developer ;

 

Now the developer creates a new function and a procedure (that has embedded SQL statement for granting):

 

CREATE OR REPLACE PROCEDURE priv_up  AUTHID CURRENT_USER AS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

EXECUTE IMMEDIATE ‘GRANT DBA TO developer’;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

/

A new account “super_user” with the following definition:

 

CREATE USER super_user

IDENTIFIED BY roro_983

DEFAULT TABLESPACE TS_USER_DATA_01

TEMPORARY TABLESPACE TS_TEMP_DATA_01

PROFILE DEFAULT

ACCOUNT UNLOCK;

 

grant create session to super_user ;

 

grant dba to super_user ;

 

SELECT grantee FROM   dba_role_privs WHERE  granted_role = ‘DBA’ ORDER BY grantee ;

 

users-with-dba

 

Connecting now as “superuser” account:

 

The powerful account “super_user” was tricked to execute the procedure:

SQL> execute developer.priv_up();

Now “developer” account has the “DBA” role!!!!

 

developer-dba-role

 

To remediate this:

 

SQL> REVOKE INHERIT PRIVILEGES ON USER super_user FROM PUBLIC;

 

revoke-inherit-privileges-from-public

 

Now if the super user tries to execute the procedure he will receive the below error:

 

ora06598

 

So, the procedure called by super_user wants to exercise one of super_user account privileges that the creator of the procedure (developer user) lacks.

Important Remark: executing the same procedure using “SYS” or “SYSTEM” accounts will by default lead to the same error ORA-06598 which is great since it will protect the SYS account from executing undesired (untrusted) SQL code.

its worth mentioning also, that if you try perform export data pump backup for schema  using SYS user after revoking INHERIT PRIVILEGE from all accounts from public, you will receive in the export log the below error:

ORA-06598: insufficient INHERIT PRIVILEGES privilege

To fix this export error:

SQL> GRANT INHERIT PRIVILEGES ON USER  SYS TO schema_Account;

 

This is a very nice security enhancement in 12c …..Thank You Oracle 🙂

TDE in Oracle 11g

What is TDE ?

TDE stands for “transparent data encryption”. TDE is part of Oracle Advanced Security Option for Enterprise Edition. Data at rest are encrypted and ONLY authorized users who have access control privilege will read de-crypted data. So, TDE protects your from direct access to the database host server or backup media that has copies of your data files.

TDE Master Key encryption can be managed through Oracle Wallet Manager.

Example of column data encryption:

The following table will be created with column “salary_information”

SQL> create table EMPLOYEE.EMPLOYEE_INFORMATION(first_name varchar2(20),last_name varchar2(30), EMPLOYEE_ID char(18), salary_information number(10,2) ENCRYPT using ‘AES256’ NO SALT);

 

For Tablespace Encryption:

  • You cannot encrypt an existing tablespace.
  • You can use” Data Pump” method OR “alter table move” to move data to newly encrypted tablespace.

 

Example of creating encrypted tablespace :

CREATE TABLESPACE tablespace_new

DATAFILE  ‘/app/oracle/oradata/tablespace2.ORA’  SIZE 200M

ENCRYPTION USING ‘AES25’ DEFAULT STORAGE (ENCRYPT);