Tuesday, December 29, 2015

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). 

Monday, December 14, 2015

Oracle Multiprocess and Multithreaded Architecture

Introduction

The Oracle process architecture of Windows operating system is a different process architecture comparing to Linux/Unix operating systems. On Windows, each Oracle instance has one single process that is multithreded, i.e. each background process is a thread within the single "father" process, therefore the "processes" tab in the the task manager will display only one "oracle.exe" process per each instance, as you can see in the following screenshot (taken from one of our Oracle environments):




























On Linux/Unix on the other hand, each background process is a different OS process so by looking for all the processes you should expect to get a long list of Oracle processes, as you can see the following screenshot (taken from one of our Oracle on Linux environments):






















































Oracle 12c Multiprocess and Multithreaded Architecture

By default, the pre-12c Oracle process architecture remains the same in Oracle 12c. However, in Oracle 12c you can enable the multithreaded architecture on Unix/Linux that will cause Oracle processes on Linux/Unix to run as threads - similar to the process architecture on Windows. In order to do so, simply set the THREADED_EXECUTION initialization parameter to TRUE (default value is FALSE). This is a static parameter (SCOPE=SPFILE), therefore requires restarting the instance in order for the changes to take effect.

Demonstration

First, I will connect to one of our 12c Oracle Linux environments and set the THREADED_EXECUTION to be TRUE:
SQL> alter system set THREADED_EXECUTION=true scope=spfile;
System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             692060264 bytes
Database Buffers          373293056 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

Now I will check how many Oracle processes I have on the Linux environment:













As you can see, now there are much less processes than before. Most of the background processes now run as threads instead of processes but some of them might still run as operating system processes (like the PMON and the DB Writer processes).

Important things to keep in mind

  • OS authentication is not allowed with the multithreaded architecture. If you will try to connect via an OS authentication (for example, by connecting with "/ AS SYSDBA") you will encounter "ORA-01017: invalid username/password; logon denied", therefore you must use a password file for connecting with a SYSDBA/SYSOPER user.
  • When the THREADED_EXECUTION parameter is set to TRUE, you must set the DEDICATED_THROUGH_BROKER_LISTENER parameter to ON in your listener.​ora file. When that parameter is set, the listener knows that it should not spawn an OS process when a connect request is received, instead it passes the request to the database so that a database thread is spawned and answers the connection.​ 
  • In RAC environments all nodes must have the same value for the THREADED_EXECUTION parameter.

Summary

In this post I've reviewed the process architecture in Linux/Unix vs. Windows and demonstrated the new 12c Multithreaded option. This feature can be useful in order to reduce the number of OS processes in scenarios where you have many Oracle instances on the same machine which may cause a higher overhead as a result of a very high number of OS processes.

Useful Links


Wednesday, December 9, 2015

Oracle 12c ADR enhancements

Introduction

Oracle 11g introduced new locations for storing the diagnostics-related logs (e.g. alert log, background trace files, foreground trace files, core dumps, etc.). This feature named ADR (Automatic Diagnostic Repository). The base directory for the ADR is defined by the DIAGNOSTIC_DEST parameter. You can also obtain the specific location of each ADR file type via V$DIAG_INFO as follows:
SQL> show parameter diagnostic
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      C:\APP\ORACLE

SQL> select name,value from v$diag_info;
NAME                           VALUE
------------------------------ ------------------------------------------------------------
Diag Enabled                   TRUE
ADR Base                       C:\APP\ORACLE
ADR Home                       C:\APP\ORACLE\diag\rdbms\orcl11\orcl11
Diag Trace                     C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\trace
Diag Alert                     C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\alert
Diag Incident                  C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\incident
Diag Cdump                     C:\app\oracle\diag\rdbms\orcl11\orcl11\cdump
Health Monitor                 C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\hm
Default Trace File             C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\trace\orcl11_ora_1010
The ADRCI (ADR Command Interpreter) that was introduced in Oracle 11gR1 is a command line tool that is being installed as part of the Oracle software installation (similar to SQL*PLUS) and its purpose is to view and manage ADR diagnostic data needed for troubleshooting issues. More informatin about the ADRCI is available here.


DDL Logging

In Oracle 12c you can enable DDL logging which will audit all the DDL statements. Oracle Database 12c turns DDL logging off by default. The parameter ENABLE_DDL_LOGGING must be set to TRUE to activate DDL logging.​ DDL logs are stored in separate files and directories. DDL logs can be found in the $ADR_HOME/log/ddl. Let's see a quick demonstration of this feature:
SQL> show parameter enable_ddl_logging
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
enable_ddl_logging                   boolean     FALSE

SQL> alter system set enable_ddl_logging=true;
System altered.

SQL> create table test_ddl_logging (id number);
Table created.

SQL> drop table test_ddl_logging;
Table dropped.

SQL> alter system set enable_ddl_logging=false;
System altered.
Now I will connect to the machine via putty in order to view the DDL log file:





As you can see the log file prints the CREATE TABLE and DROP TABLE commands.
It's also possible to view the contents of the DDL log file via the ADRCI "SHOW LOG" command which will show you the actual statements and the execution date for each statements that was executed.

DEBUG Logging

Some low-severity issues that don't require immediate action can trigger a warning which will be recorded in a debug log file (instead of the alert log). These messages might be needed to diagnose a future problem and the debug information is included as part of the incident Packaging Service (IPS) which can be shipped to the Oracle support when needed.
The location of the debug log file is $ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/debug

Useful Links

Tuesday, December 8, 2015

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