SYSBACKUP and SYSDG permissions in Oracle 12c

Introduction

In many companies there is a clear separation of duties for various Oracle Database related tasks such as administering ASM and backing up/restoring Oracle databases.

In the past, DBAs used SYSDBA permission for administering ASM and RMAN. As you probably know, SYSDBA is the most powerful permission in Oracle Database which even allows viewing all the application data.

Oracle realized that they need to address the separation of duties requirement of many customers and therefore they have provided in Oracle 11g a dedicated permission for administering ASM - I've written a dedicated blog post in the past for this matter. The SYSASM permission cannot access application data, but it can perform various ASM related management tasks (such as altering diskgroup, adding disks, etc.)

What about RMAN?

Until Oracle Database version 12cR1, there wasn't a good solution from a separation of duties when it comes to RMAN backups as users had to use SYSDBA which also allows them to access any application data (as well as other strong permissions).

In Oracle 12cR1, Oracle introduced the SYSBACKUP permission which allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus.

You can view here the full list of operations allowed by this administrative privilege

And what about Data Guard?

Very similar to RMAN, Oracle also introduced in version 12cR1 a dedicated privilege named SYSDG which can be used with the Data Guard Broker and the DGMGRL command-line interface. 

Demo

First, we can connect to a 12c instance and look for those accounts. Next step would be to connect / AS SYSBACKUP since I'm logged with a user that has OS permissions to connect without any username and password

SQL> SELECT username, account_status
  FROM dba_users
 WHERE username LIKE '%SYS%';

USERNAME   ACCOUNT_STATUS
---------- --------------------------------
SYS        OPEN
SYSTEM     OPEN
SYS$UMF    EXPIRED & LOCKED
APPQOSSYS  EXPIRED & LOCKED
GGSYS      EXPIRED & LOCKED
WMSYS      EXPIRED & LOCKED
SYSBACKUP  EXPIRED & LOCKED
SYSRAC     EXPIRED & LOCKED
AUDSYS     EXPIRED & LOCKED
SYSKM      EXPIRED & LOCKED
SYSDG      EXPIRED & LOCKED

SQL> connect / as sysbackup
Connected.
SQL> show user
USER is "SYSBACKUP"

I can also create a new user and grant him the SYSBACKUP or SYSDG permissions

SQL> connect / as sysdba
Connected.

SQL> create user C##PINI identified by PINI;
User created.

SQL> grant SYSBACKUP to C##PINI;
Grant succeeded.

SQL> select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;

USERNAME   SYSBA SYSDG
---------- ----- -----
SYS        FALSE FALSE
SYSDG      FALSE TRUE
SYSBACKUP  TRUE  FALSE
SYSKM      FALSE FALSE
C##PINI    TRUE  FALSE

Note that in order to connect to the database as either SYSDG or SYSBACKUP using a password, there must be a password file for it because it is possible to connect even when the database is not up and running, as follows

SQL> connect / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  8793400 bytes
Variable Size             989856456 bytes
Database Buffers          637534208 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> connect c##pini/pini
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Warning: You are no longer connected to ORACLE.

SQL> connect c##pini/pini as SYSBACKUP;
Connected.

Summary

In this post we've reviewed the SYSDG and SYSBACKUP users and permissions in Oracle 12c which could be useful in case that in your company there is a requirement to have a separation of duties for backup/recovery as well as for Data Guard related administration tasks. I hope you find it useful for you.

Oracle 12c Privilege Analysis

Introduction

In this post I'd like to demonstrate another new Oracle 12c security feature - Privilege Analysis. Using this feature, the DBA can easily understand which privileges are actually being used by users. Once the analysis has been completed, the DBA can revoke all of the unnecessary privileges/roles. This feature is only available for Enterprise Edition with Oracle Database Vault (extra cost option).

How does it work?

Oracle introduced a new package, DBMS_PRIVILEGE_CAPTURE which allows you to create a new privilege analysis policy, enable/disable it, generate the results of the analysis and drop the policy once the analysis is over and the policy is not needed anymore. In order to use the DBMS_PRIVILEGE_CAPTURE package you need to be grnted the CAPTURE_ADMIN role.

Demonstration

In order to create a new privilege analysis policy, use the following syntax:

DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
   name              VARCHAR2, 
   description       VARCHAR2 DEFAULT NULL, 
   type              NUMBER DEFAULT DBMS_PRIVILEGE_CAPTURE.G_DATABASE, 
   roles             ROLE_NAME_LIST DEFAULT ROLE_NAME_LIST(), 
   condition         VARCHAR2 DEFAULT NULL);

Let's start by creating a user named "pini" and grant him the DBA role. Afterwards, we'll create and enable a simple policy named "capture_pini_privs" that simply captures all the privileges used by user pini:

SQL> create user pini identified by pini default tablespace users;
User created.

SQL> grant dba to pini;
Grant succeeded.

SQL> BEGIN
  2    DBMS_PRIVILEGE_CAPTURE.create_capture(
  3      name        => 'capture_pini_privs',
  4      type        => DBMS_PRIVILEGE_CAPTURE.g_context,
  5      condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''PINI'''
  6    );
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_PRIVILEGE_CAPTURE.enable_capture('capture_pini_privs');
PL/SQL procedure successfully completed.

Explanation of the above code:

- In line 3 I set the name of the policy to be "capture_pini_privs"
- In line 4 I set the type to be DBMS_PRIVILEGE_CAPTURE.g_context to captrue privileges of the sessions defined in line 5
- In line 5 I used SYS_CONTEXT to specify capture privileges for user "PINI" only

After we have created the new policy we can verify it via DBA_PRIV_CAPTURES dictionary view that lists all the policies as follows:

SQL> SELECT TYPE, enabled, context FROM DBA_PRIV_CAPTURES;

TYPE             ENABLED    CONTEXT
---------------- ---------- --------------------------------------------------
CONTEXT          Y          SYS_CONTEXT('USERENV', 'SESSION_USER') = 'PINI'

Note that there are several other types that you can use (in line 4) like capturing privileges of the entire database users except of use SYS (using DBMS_PRIVILEGE_CAPTURE.G_DATABASE), or capturing the privileges of specific sessions with specific roles (DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT) which requires also to specify the "roles" parameter. You can read more about these options in the official documentation for the DBMS_PRIVILEGE_CAPTURE package.

Now, after we have created and enabled to policy, the analysis period has been started.
Next, let's connect with user pini and execute a few commands to test this feature:

SQL> connect pini/pini
Connected.

SQL> create table EMP (id number, name varchar2(20), constraint id_pk PRIMARY KEY (id));
Table created.

SQL> insert into EMP values (1, 'DAVID');
1 row created.

SQL> commit;
Commit complete.

SQL> CREATE OR REPLACE FUNCTION return_num_of_emps
  2     RETURN NUMBER IS
  3     num_of_emps NUMBER;
  4     BEGIN
  5        select count(*)
  6        into num_of_emps
  7        from EMP;
  8        RETURN(num_of_emps);
  9      END;
 10  /
Function created.

SQL> select return_num_of_emps from dual;
RETURN_NUM_OF_EMPS
------------------
                 1
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
o1264np

SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

Now, I will disable the capture policy and generate the report as follows:

SQL> execute DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE('capture_pini_privs');
PL/SQL procedure successfully completed.

SQL> execute DBMS_PRIVILEGE_CAPTURE.generate_result('capture_pini_privs');
PL/SQL procedure successfully completed.

The output of the report will be printed to various dictionary views like DBA_USED_SYSPRIVS and DBA_USED_OBJPRIVS as you can see in the following demonstration:

SQL> SELECT SYS_PRIV
  2    FROM DBA_USED_SYSPRIVS
  3   WHERE username = 'PINI';

SYS_PRIV
---------------------------------------
CREATE SESSION
CREATE TABLE
CREATE ANY INDEX
CREATE PROCEDURE
UNLIMITED TABLESPACE

SQL> SELECT obj_priv, object_owner, object_name
  2    FROM DBA_USED_OBJPRIVS
  3   WHERE username = 'PINI';

OBJ_PRIV                                 OBJECT_OWNER         OBJECT_NAME
---------------------------------------- -------------------- ----------------------------------------
SELECT                                   SYS                  V_$INSTANCE
SELECT                                   SYS                  V_$DATABASE

Once the analysis has been completed, you can drop the policy. This step is optional and it's goal is to clean to policy-related information from the data-dictionary views. In order to do that use the following syntax:

SQL> execute DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('capture_pini_privs');
PL/SQL procedure successfully completed.

Summary

Granting minimum privileges for users is a security best practice, however, many DBAs are struggling with identifying which exact privileges are actually needed and which privileges are unnecessary - and therefore, should be revoked. This feature comes to solve this challenge. I would expect this useful security feature to be available for all Oracle Editions but unfortunately, it's only available for Enterprise Edition with Oracle Database Vault (extra cost option). 

Demonstration of Oracle 12c Data Redaction

Introduction

In this post I'd like to demonstrate the new Oracle 12c security feature - Data Redaction. 

Although it was introduced in Oracle 12c, a similar feature named "column masking" has been introduced back in 2003 with Oracle version 10g. Data Redaction allows you to protect confidential data by masking the sensitive information from unauthorized users. The way for Oracle to know if the user is authorized to see the real data is by using the EXEMPT REDACTION POLICY system privilege. If the user doesn't have the EXEMPT REDACTION POLICY system privilege it means that he is unauthorized and therefore if a redaction policy is defined for the data the the user attempts to query, the output will be redacted rather than the real data.

Data Redaction Policies

Data Redaction policy for a table or view defines which column/s will be redacted and in which manner. 

The management of redaction policies is done via the DBMS_REDACT package. 

For example, you can create a new policy DBMS_REDACT.ADD_POLICY procedure, modify an existing policy using the DBMS_REDACT.ALTER_POLICY, disable a policy using DBMS_REDACT.DISABLE_POLICY and drop a policy using DBMS_REDACT.DROP_POLICY.

Demonstration

First, let's create a table and insert a 2 records to the table.

SQL> create table EMPLOYEE (id number, name varchar2(20), join_date timestamp);
Table created.

SQL> insert into EMPLOYEE values (1, 'PINI',sysdate);
1 row created.

SQL> insert into EMPLOYEE values (2, 'DAVID',sysdate);
1 row created.
SQL> commit;
Commit complete.

SQL> select * from EMPLOYREE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         1 PINI                 08-DEC-15 03.33.27.000000 PM
         2 DAVID                08-DEC-15 03.33.28.000000 PM

Let's start by creating a new redaction policy named "REDACT_EMPLOYEE" for the "Name" column

SQL> BEGIN
  2     DBMS_REDACT.add_policy (object_name     => 'EMPLOYEE',
  3                             column_name     => 'ID',
  4                             policy_name     => 'REDACT_EMPLOYEE',
  5                             function_type   =>  DBMS_REDACT.full,
  6                             expression      => '1=1');
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> select * from EMPLOYEE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         0 PINI                 08-DEC-15 03.33.27.000000 PM
         0 DAVID                08-DEC-15 03.33.28.000000 PM

As we can see, the value of the ID column has been changed to 0. You may ask yourself, why 0? How Oracle decides which values to use for the masking? We'll discuss about it in a minute. Now, let's redact the rest of the columns (Name and JOIN_DATE) using the ALTER_POLICY procedure. In order to do that, we'll set the value of "action" parameter to be the DBMS_REDACT.ADD_COLUMN constant, as follows:

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                             column_name     => 'NAME',
  4                             policy_name     => 'REDACT_EMPLOYEE',
  5                             function_type   => DBMS_REDACT.full,
  6                             expression      => '1=1',
  7                             action          => DBMS_REDACT.ADD_COLUMN);
  8  END;
  9  /
PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                             column_name     => 'JOIN_DATE',
  4                             policy_name     => 'REDACT_EMPLOYEE',
  5                             function_type   => DBMS_REDACT.full,
  6                             expression      => '1=1',
  7                             action          => DBMS_REDACT.ADD_COLUMN);
  8  END;
  9  /
PL/SQL procedure successfully completed.

SQL> select * from EMPLOYEE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         0                      01-JAN-01 01.00.00.000000 AM
         0                      01-JAN-01 01.00.00.000000 AM

As we can see, Oracle changed all values of the EMPLOYEE table with fixed values and that's because we set the function_type parameter to be DBMS_REDACT.FULL which is by the way, the default behaviour so if you will omit the function_type parameter, it will use a FULL redaction, i.e. it will use fixed values for the data redaction.
The default redaction values for the FULL redaction can be obtained via the REDACTION_VALUES_FOR_TYPE_FULL dictionary view.

As you can see, the default value for NUMBER is 0, for characther data types it's a single space and for date-time data types is the first day of January, 2001, which appears as 01-JAN-01.
In order to change the default masking valuess when using a FULL redaction, you can use the UPDATE_FULL_REDACTION_VALUES Procedure. In addition to the default FULL redaction type, you can also choose to work with other function types:

• NONE - No redaction
• PARTIAL - Partial redaction, redact a portion of the column data
• RANDOM - Random redaction, each query results in a different random value
• REGEXP - Regular expression based redaction

For example, you can change the function_type of all the columns to be RANDOM as follows:

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                               column_name     => 'ID',
  4                               policy_name     => 'REDACT_EMPLOYEE',
  5                               function_type   => DBMS_REDACT.RANDOM,
  6                               action          => DBMS_REDACT.MODIFY_COLUMN);
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                               column_name     => 'NAME',
  4                               policy_name     => 'REDACT_EMPLOYEE',
  5                               function_type   => DBMS_REDACT.RANDOM,
  6                               action          => DBMS_REDACT.MODIFY_COLUMN);
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                               column_name     => 'JOIN_DATE',
  4                               policy_name     => 'REDACT_EMPLOYEE',
  5                               function_type   => DBMS_REDACT.RANDOM,
  6                               action          => DBMS_REDACT.MODIFY_COLUMN);
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> select * from EMPLOYEE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         8 N(#i                 23-AUG-80 05.25.47.000000 AM
         5 SG*cK                11-DEC-90 04.19.26.000000 AM

As I've mentioned in the beginning of this post, once a user has the EXEMPT REDACTION POLICY system privilege it means that he is authotized to view the original data. Let's connect with user SYS and grant EXEMPT REDACTION POLICY to user "pini":

SQL> grant EXEMPT REDACTION POLICY to pini;
Grant succeeded.

SQL> connect pini/pini@//isrvmrh541:1521/pinidb
Connected.
SQL> select * from EMPLOYEE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         1 PINI                 08-DEC-15 03.33.27.000000 PM
         2 DAVID                08-DEC-15 03.33.28.000000 PM

As you can see, now user "pini" is able to view the original "confidential" data because he has the EXEMPT REDACTION POLICY privilege.

License

The data redaction feature is available as part of the "Advanced Security" option. The "Advanced Security" option is an extra cost option to the Enterprise Edition.

Useful Links

• Oracle Documentation - Oracle Data Redaction Features and Capabilities
• Oracle Documentation - DBMS_REDACT package

Review of Oracle 12c Unified Auditing

Introduction

Prior to Oracle 12c there were several tables in the Database that stored audit trails:

1. SYS.AUD$ (also accessible through DBA_AUDIT_TRAIL view) which is the main database audit trail 
2. SYS.FGA_LOG$ (also accessible through DBA_FGA_AUDIT_TRAIL view)  for the Fine-Grained auditing records
3. DVSYS.AUDIT_TRAIL$ for the Label security and DB Vault

In Oracle Database version 12c, Oracle introduced a new feature named "Unified Auditing" which basically consolidates all the audit trails into one single view - UNIFIED_AUDIT_TRAIL. There is no option to write audit records to the operating system.

This feature is also introducing a new set of predefined audit policies and you can also create your own audit policies - they could be either simple or more complex depends on what you need. 

For example, you can create audit policy that audits delete statements on specific tables (ORDERS and ORDER_LINE tables for example) by a specific user (user SALES for example) on a specific PDB (PDB named PROD for example).

You can choose whether you want to have one policy that will contain all the audit settings for your database or several audit policies. Personally, I believe that having less policies and even one single policy enabled for your database is better because it has lower overhead on your Oracle instance.

You can view all the audit policies that are configured in your database using AUDIT_UNIFIED_POLICIES dictionary view as you can see in the following example:

SQL> select distinct policy_name
     from audit_unified_policies;

POLICY_NAME
-----------------------------------
ORA_CIS_RECOMMENDATIONS
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_DATABASE_PARAMETER
ORA_RAS_SESSION_MGMT
ORA_ACCOUNT_MGMT
ORA_DV_AUDPOL
ORA_SECURECONFIG

8 rows selected.

Each policy contains one or more auditing options. Each auditing option has its type. Let's view all the auditing option types that the "ORA_SECURECONFIG" policy contains:

SQL> select distinct audit_option_type
     from audit_unified_policies
     where policy_name ='ORA_SECURECONFIG';

AUDIT_OPTION_TYPE
------------------
SYSTEM PRIVILEGE
STANDARD ACTION
OBJECT ACTION

Now, let's view all the audit options that defined in the "ORA_SECURECONFIG" policy and their type is "SYSTEM PRIVILEGE".

SQL> select policy_name, audit_option
     from audit_unified_policies
     where policy_name = 'ORA_SECURECONFIG' AND audit_option_type = 'SYSTEM PRIVILEGE';

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     LOGMINING
ORA_SECURECONFIG     TRANSLATE ANY SQL
ORA_SECURECONFIG     EXEMPT REDACTION POLICY
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE EXTERNAL JOB
ORA_SECURECONFIG     CREATE ANY JOB

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY
ORA_SECURECONFIG     CREATE ANY LIBRARY
ORA_SECURECONFIG     GRANT ANY PRIVILEGE
ORA_SECURECONFIG     DROP ANY PROCEDURE
ORA_SECURECONFIG     ALTER ANY PROCEDURE
ORA_SECURECONFIG     CREATE ANY PROCEDURE
ORA_SECURECONFIG     ALTER DATABASE
ORA_SECURECONFIG     GRANT ANY ROLE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     DROP ANY TABLE
ORA_SECURECONFIG     ALTER ANY TABLE
ORA_SECURECONFIG     CREATE ANY TABLE
ORA_SECURECONFIG     DROP USER
ORA_SECURECONFIG     CREATE USER
ORA_SECURECONFIG     AUDIT SYSTEM
ORA_SECURECONFIG     ALTER SYSTEM

29 rows selected.

Is this feature enabled by default?

I have a new Oracle 12c instance so let's check if this feature is enabled using V$OPTION :

SQL> select value
  2  from V$OPTION
  3  where parameter = 'Unified Auditing';

VALUE
-------------------------------------------
FALSE

Does this mean that the feature is disabled?

Well, not Exactly. 

When you install a new Oracle Database 12c or upgrade to Oracle Database 12c then by default it's configured to work in a "Mixed Mode". Mixed Mode allows you to run the traditional auditing (like you used prior to Oracle 12c) and also benefit the new 12c audit policies. It even has the ORA_SECURECONFIG and ORA_LOGON_FAILURES audit policies enabled by default (so don't be surprised that you see some records in the new UNIFIED_AUDIT_TRAIL view). You can verify which audit policies are enabled in your database using AUDIT_UNIFIED_ENABLED_POLICIES dictionary view. See the following screenshot as an example:

SQL> select *
     FROM audit_unified_enabled_policies;

USER_NAME            POLICY_NAME          ENABLED_ SUCCESS    FAILURE
-------------------- -------------------- -------- ---------- ----------
ALL USERS            ORA_SECURECONFIG     BY       YES        YES
ALL USERS            ORA_LOGON_FAILURES   BY       NO         YES

As I've mentioned, in this configuration Oracle will support both the traditional auditing and the new 12c audit policies so you may find new records written into the pre-12c audit trail - DBA_AUDIT_TRAIL dictionary view and also into the new UNIFIED_AUDIT_TRAIL dictionary view.

Once you will decide that you want to enable the "Pure" unified auditing, Oracle will ignore the "old" AUDIT_TRAIL parameter and will also stop writing records to the old audit trails and will write trail records only to the new UNIFIED_AUDIT_TRAIL dictionary view.

What other benefits the Unified Auditing feature provides?

Performance

Prior to Oracle 12c, if you configures auditing per execution (per "Action"), for example, let's say you configured auditing for every INSERT command by users SALES; in this case, every insert statement by user SALES will insert a row to the SYS.AUD$ table as an autonomous transaction which will obviously generate redo, undo, and perform a commit at the end of the transaction. Until this autonomous transaction is completed, the session will have to wait. In Oracle 12c Unified Auditing, the audit records will be written into an SGA queue and periodically, a dedicated background process will write the records to the AUDSYS schema in the SYSAUX tablespace in order to ensure that the data is persistent.

Note:
The SGA queue for the unified auditing default size is 1 MB, but can be adjusted to a value of [1,30] via the UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter.

You may now ask yourself - does that mean that in a crash scenario soe audits records may be lost? The answer is yes; some audit records may be lost. There's always a trade-off between protection and performance. However, if you prefer to have 100% protection for the audit records in the cost of impacting your database performance you can change this default behavior and tell Oracle to write the audit trails records into the AUDSYS schema immediately (see the "Managing the Unified Audit Trail" section).

Security

Prior to Oracle 12c, if you chose to write the audit trail to the DB (audit_tail='db') then all the records will be written to SYS.AUD$ table, which means that the DBA can modify this table. In Oracle 12c Unified Auditing the DBA can't modify the audited information because the base X$ table behind the UNIFIED_AUDIT_TRAIL dictionary view is decrypted.

How to enable the Pure Unified Auditing

In order to enable to Pure Unified Auditing you will need to perform the following steps:

1. Shutdown your Oracle Databases and listeners that are associated to the Oracle Home
2. Relink the Oracle executable to support the Unified Auditing
3. Start your Oracle instances and listener

In order to relink the Oracle executable you will need to execute the "make" command from the $ORACLE_HOME/rdbms/lib directory:

make -f ins_rdbms.mk uniaud_on ioracle

Now, after the relink has been completed, let's query V$OPTION again to verify that the Unified Auditing feature is enabled:

SQL> select value
     from V$OPTION
     where parameter = 'Unified Auditing';

VALUE
-------------------------------------------
TRUE

Changing the default audit trail write mode 

As I've mentioned earlier in this article, this feature offers a great performance improvement by not writing the audit trails immediately, but rather writing into a dedicated SGA queue and periodically writing the audit trails persistenly.

You can change the default write mode (Queued-write mode) to be Immediate-write mode using the DBMS_AUDIT_MGMT package, as follows:

BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, 
  DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
END;
/

Summary

The Unified Auditing is a great security feature that was introduced in Oracle 12c. It offers a simple management for unified (consolidated) auditing that can be accessed via a single dictionary view - UNIFIED_AUDIT_TRAIL. It also provides major security and performance enhancements.

Useful Links

More information and details are available in the Oracle documentation:

• Oracle 12c Documentation - Introduction to Auditing
• Oracle 12c Documentation - Administering the Audit Trail

12c New feature - Invisible Rows A.K.A In-Database Archiving

Introduction

In Oracle Database 12c (12.1.0.1 to be more specific) Oracle introduced a new feature named "In-Database Archiving" which allows to mark specific rows as invisible (archived) so they will not be visible. For example, if someone queries a table that is configured with this feature enabled then all the rows that are marked as archived will be invisible unless the session has enabled to see archived data. The rows that are archived can be compressed in order to reduce the storage of the database and also improve backup performance.

So how does it work?

In order to configure a table with the In-Database Archiving feature enable, you will need to use the ROW ARCHIVAL clause during the CREATE TABLE command, or if the table already exists then you can use the ROW ARCHIVAL clause using the ALTER TABLE command.

Once you enable this feature, Oracle will create additional column named ORA_ARCHIVE_STATE. By default, this column contains the value '0' for each row which means that the row is visible. If you will change to value to be '1' then the row will be invisible. If you will disable this feature using the ALTER TABLE ... NO ARCHIVAL command then Oracle will automatically drop
this column.

Demonstration

In this demonstration I will perform the following steps:

1. Create a new table named "test" and enable the ROW Archival feature for this table
2. Populate the table with 2 rows
3. Query the table including the ORA_ARCHIVE_STATE column to see that that the additional column contains only the default '0' values:
4. Mark "David" as invisible by changing the ORA_ARCHIVE_STATE  value to be '1'
5. Query again the table to verify that "David" is actually invisible
6. Allow the session to view the invisible data using the ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL command
7. Query again the table to verify that "David" is now visible
8. Prohibit the session from viewing the invisible data using the ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE command
9. Query again the table to verify that "David" is now invisible again
10. Disable the In-Database Archiving feature for the table using the ALTER TABLE ... NO ARCHIVAL command
11. Query the table to verify that the ORA_ARCHIVE_STATE column has been dropped

Useful Links:

• CREATE TABLE ... ROW ARCHIVAL clause (Oracle Documentation)
• ALTER SESSION ... ROW ARCHIVAL clause (Oracle Documentation)
• Using In-Database Archiving (Oracle Documentation)

Wondering why you can't view data with a COMMON USER in Oracle 12c? You probably didn't use the CONTAINER_DATA clause

Introduction
Let's say you've created a common user in your Oracle 12c instance and you granted the user permissions to connect and select some specific dynamic views (e.g. V$PDBS, V$CONTAINERS).
Afterwards, you connect with that common user to the root container (CDB$ROOT), you query V$PDBS but no rows are returned.
This issue was raised in the OTN forum by a user and my answer to that user was  very simple - use the CONTAINER_DATA clause (See: https://community.oracle.com/message/13301017#13301017).

Basically, when a common user is connected to the ROOT and it executes a query on a container data object (As per Oracle Doc, container data objects include: V$, GV$, CDB_, and some Automatic Worklaod Repository DBA_HIST* view), then that query will only dispay data for the PDBs which are visible for that common user, and this is what you can set using the CONTAINER_DATA clause.

Demonstration
In the first step I'll create a common user, grant him permissions to connect and query V$PDBS and then I'll connect with that user and try to query V$PDBS.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBTEST                        MOUNTED
PINIDB                         READ WRITE

SQL> create user C##TEST identified by test;
User created.

SQL> grant connect to C##TEST;
Grant succeeded.

SQL> grant select on sys.v_$pdbs to C##TEST;
Grant succeeded.

SQL> connect C##TEST/test@isrvmrh541-cdb.world
Connected.

SQL> select * from v$pdbs;
no rows selected

As you can see, no rows are returned from the query.
Let's connect again with SYS and verify which PDBs are visible for user C##TEST using the CDB_CONTAINER_DATA dictionary view which displays information about the user-level and object-level CONTAINER_DATA attributes specific in the CDB:

SQL> connect sys@isrvmrh541-cdb.world as sysdba
Enter password:
Connected.

SQL> SELECT username,
  2         owner,
  3         object_name,
  4         all_containers container_name
  5    FROM CDB_CONTAINER_DATA
  6   WHERE username = 'C##TEST';
no rows selected

As you can see, user C##TEST has no container data attributes.
Let's specify that user C##TEST can see the data of V$PDBS from all the containers:

SQL> alter user C##TEST set container_data=all for sys.v_$pdbs container = current;
User altered.

SQL> SELECT username,
  2         owner,
  3         object_name,
  4         all_containers,
  5         container_name
  6    FROM CDB_CONTAINER_DATA
  7   WHERE username = 'C##TEST'
  8   ;

USERNAME           OWNER     OBJECT_NAME   ALL_CONTAINERS CONTAINER_NAME
--------------- ---------- --------------- ------------- --------------------
C##TEST            SYS      V_$PDBS           Y

As you can see, the CONTAINER_NAME is NULL because I sepcific in the aler command that the container_data will be visible for all containers.
If we would like to specify that the data of every container data object that the user has SELECT permissions to access, you can remove the "for" clause and execute the following command:

SQL> alter user C##TEST set container_data=all container = current;
User altered.

SQL> SELECT username,
  2         owner,
  3         object_name,
  4         all_containers,
  5         container_name
  6    FROM CDB_CONTAINER_DATA
  7   WHERE username = 'C##TEST';

USERNAME           OWNER    OBJECT_NAME    ALL_CONTAINERS CONTAINER_NAME
--------------- ---------- --------------- ------------- --------------------
C##TEST            SYS      V_$PDBS            Y
C##TEST                                        Y

As you can see, now user C##TEST has an access for all the objects which he will granted permissions to SELECT from. 

Summary

• Once you create a COMMON USER, you should also specify which PDBs data are visible to that common user for which objects using the CONTAINER_DATA clause
• You can specify the object-level CONTAINER_DATA attributes for a user using the ALTER USER command
• You can view the information about the user-level and object-level CONTAINER_DATA attributes via CDB_CONTAINER_DATA dictionary view

Useful Links:

• http://docs.oracle.com/database/121/ADMIN/cdb_mon.htm#ADMIN13931
• http://docs.oracle.com/database/121/SQLRF/statements_4003.htm#SQLRF01103

What's the difference between SYSDBA and SYSASM?

Introduction

ASM was first introduced in 2003 with Oracle 10gR1 and since then it's the recommended storage management solution by Oracle and it's being used as a filesystem and volumn manager.

In the "old" 10g days, we used to administer the ASM instance using the as SYSDBA role.

The Problem
The problem with using SYSDBA is that in many organization there is a clear seperation between the DBA to the ASM administrator -the DBA in some organization doesn't suppose to add disks, alter disk groups, etc.

The Solution
The solution for this problem was introduced in Oracle 11gR1 when Oracle introduced a new role, SYSASM that should be used by the ASM administrators to perform administrative tasks, such as CREATE/DROP/ALTER diskgroup, or startup/shutdown the ASM instance.
The SYSDBA should be used by the ASM for read-only operations such as qureying dynamic views (e.g. V$ASM_DISKGROUP, V$ASM_FILE, V$ASM_OPERATION, etc.)

Although SYSASM was introduced in Oracle 11gR1, SYSDBA role still had (in Oracle 11gR1) full administrative permissions, but every time an administrative command was executed (such as starting the ASM instance), a warning was reported in the ASM alert log file:
"WARNING: Deprecated privilege SYSDBA for command <…>"

Starting from Oracle 11gR2, Oracle enforced the seperation between the DBA to the ASM administrator and if you will try to connect as SYSDBA and perform administrative task you will get an error because administrative tasks can only be performed by ASM Administrators who connect AS SYSASM, as you can see in the following screenshot: