Data Redaction in Oracle 12c

What is Data Redaction?
Oracle Data Redaction is one of the new features introduced in Oracle Database 12c. This new feature is part of the Advanced Security option and enables the protection of data shown to the user in real time, without requiring changes to the application. Oracle Database 12c applies protection at query execution time.

How It Works?
We can create redaction policies which specify conditions that must be met before the data gets redacted and returned to the user. During the definition of such policies, the DBA can specify which columns and the type of protection that must be applied.

The package used to create protection rules is called DBMS_REDACT. The package includes five procedures to manage the rules and an additional procedure to change the default value for full redaction policy.

DBMS_REDACT.ALTER_POLICY – allows changes to existing policies.
DBMS_REDACT.DISABLE_POLICY – disables an existing policy.
DBMS_REDACT.DROP_POLICY – drop an existing policy.
DBMS_REDACT.ENABLE_POLICY – enables an existing policy.
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES – change the default return value for full redaction. You must restart the database to take effect.

How many ways to protect data using data redaction?

You can protect data at the column level using one of the following methods::

Full redaction – All content of the column is protected and the type of value returned depends on the data type of the column. For numeric columns, the value zero will be returned. For columns of type character, a space will be returned. This setting can be changed at the database level.
Partial redaction – Only part of the information is changed. For example, the first digits of the credit card number are replaced by asterisks.
Regular expressions - You can use regular expressions to search for patterns of data that must be protected.
Random redaction – Returned values ​​are random; each time a query is executed, the displayed data will be different.
No redaction - Allows testing the inner workings of redaction policies, with no effect on the results of current running queries. This is widely used during testing phase of redaction policies that will eventually find their way to production environments.

Can be see here using graphical presentation:

What data types can be used for data redaction?

It can be used with the following column data types: NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB, CLOB, and NCLOB.


Examples to show use of data redaction features  

-- Find data redaction enabled or not

SQL> select * from redaction_policies;
no rows selected
SQL> 

SQL> GRANT EXECUTE ON sys.dbms_redact TO scott;
Grant succeeded.

SQL> connect scott/xxxxx;
Connected.
SQL> desc emp_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(38)
 NAME                                               VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 BOSS                                               NUMBER(38)
 HIREDATE                                           VARCHAR2(12)
 SALARY                                             NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(38)

SQL> 

SQL> select * from employee;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17         5200                    10
      7566 JONES      MANAGER         7839 1981-04-02         2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1100                    20
      7902 FORD       ANALYST         7566 1981-12-03         3000                    20
      7369 SMITH      CLERK           7902 1980-12-17          800                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         2850                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1500          0         30
      7900 JAMES      CLERK           7698 1981-12-03          950                    30
      7782 CLARK      MANAGER         7839 1981-06-09         2450                    10
      7934 MILLER     CLERK           7782 1982-01-23         1300                    10

14 rows selected.


-- Applying Full Data Redaction

SQL> connect / as sysdba
Connected.
SQL> sho user
USER is "SYS"

SQL> 

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'scott',
    object_name   => 'emp_test',
    column_name   => 'salary',
    policy_name   => 'redact_sal_info',
    function_type => DBMS_REDACT.full,
    expression    => '1=1'
  );
END;
/

PL/SQL procedure successfully completed.

-- after data redaction

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17         5200                    10
      7566 JONES      MANAGER         7839 1981-04-02         2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1100                    20
      7902 FORD       ANALYST         7566 1981-12-03         3000                    20
      7369 SMITH      CLERK           7902 1980-12-17          800                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         2850                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1500          0         30
      7900 JAMES      CLERK           7698 1981-12-03          950                    30
      7782 CLARK      MANAGER         7839 1981-06-09         2450                    10
      7934 MILLER     CLERK           7782 1982-01-23         1300                    10

14 rows selected.


-- See the applied policies

col OBJECT_OWNER for a20
col OBJECT_NAME for a20;
col POLICY_DESCRIPTION for a10
col POLICY_NAME for a20
col EXPRESSION for a10

SQL> select * from redaction_policies;

OBJECT_OWNER         OBJECT_NAME          POLICY_NAME          EXPRESSION ENABLE  POLICY_DES
-------------------- -------------------- -------------------- ---------- ------- ----------
SCOTT                EMP_TEST             redact_sal_info     1=1        YES
SQL> 


-- Data Redaction default values

SET LINESIZE 250
COLUMN char_value FORMAT A10
COLUMN varchar_value FORMAT A10
COLUMN nchar_value FORMAT A10
COLUMN nvarchar_value FORMAT A10
COLUMN timestamp_value FORMAT A27
COLUMN timestamp_with_time_zone_value FORMAT A32
COLUMN blob_value FORMAT A20
COLUMN clob_value FORMAT A10
COLUMN nclob_value FORMAT A10

SELECT * FROM   redaction_values_for_type_full;



-- Alter an Existing Policy
The ALTER_POLICY procedure allows you to make changes to an existing policy. The type of change being made is controlled using the ACTION parameter. Depending on the action required, the relevant parameters must be specified.

The following example changes the previously created redaction policy so that it uses partial redaction. Notice the FUNCTION_PARAMETERS are now specified to give instructions how the partial redaction should take place. For a numeric data type we specify a comma separated list of three elements (value to redact to, start point, end point), so in this case we want the first 12 characters of the number to always display as "111111111111".


SQL> show user
USER is "SYS"
SQL> BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => 'scott',
    object_name         => 'emp_test',
    policy_name         => 'redact_sal_info',
    action              => DBMS_REDACT.modify_column,
    column_name         => 'salary',
    function_type       => DBMS_REDACT.partial,
    function_parameters => '1,1,12'
  );
END;
/

PL/SQL procedure successfully completed.

SQL> 


-- Verify

SQL> select * from emp_test;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17         1111                    10
      7566 JONES      MANAGER         7839 1981-04-02         1111                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         1111                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1111                    20
      7902 FORD       ANALYST         7566 1981-12-03         1111                    20
      7369 SMITH      CLERK           7902 1980-12-17          111                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         1111                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1111        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1111        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1111       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1111          0         30
      7900 JAMES      CLERK           7698 1981-12-03          111                    30
      7782 CLARK      MANAGER         7839 1981-06-09         1111                    10
      7934 MILLER     CLERK           7782 1982-01-23         1111                    10

14 rows selected.

SQL> 


-- Drop an Existing Policy
The DROP_POLICY procedure is used to remove an existing redaction policy. The following example drops the redaction policy and queries the data, showing the redaction is no longer taking place.

CONN test/test@pdb1

BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => 'scott',
    object_name   => 'emp_test',
    policy_name   => 'redact_sal_info'
  );
END;
/

PL/SQL procedure successfully completed.

SQL> show user;
USER is "SCOTT"
SQL> select * from emp_test;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17         5200                    10
      7566 JONES      MANAGER         7839 1981-04-02         2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1100                    20
      7902 FORD       ANALYST         7566 1981-12-03         3000                    20
      7369 SMITH      CLERK           7902 1980-12-17          800                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         2850                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1500          0         30
      7900 JAMES      CLERK           7698 1981-12-03          950                    30
      7782 CLARK      MANAGER         7839 1981-06-09         2450                    10
      7934 MILLER     CLERK           7782 1982-01-23         1300                    10

14 rows selected.

SQL> 

-- Apply on Varchar Column

SQL> show user;
USER is "SYS"
SQL> 
SQL> BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'scott',
object_name => 'emp_test',
column_name => 'job',
policy_name => 'partially mask job',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVVVVVV,VVVVVVVVV,*,3,12',
expression => '1=1'
);
END;
/   

PL/SQL procedure successfully completed.

SQL> connect scott
Enter password: 
Connected.
SQL> show user
USER is "SCOTT"
SQL> select * from emp_test;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PR*******            1981-11-17         5200                    10
      7566 JONES      MA*****         7839 1981-04-02         2975                    20
      7788 SCOTT      AN*****         7566 1982-12-09         3000                    20
      7876 ADAMS      CL***           7788 1983-01-12         1100                    20
      7902 FORD       AN*****         7566 1981-12-03         3000                    20
      7369 SMITH      CL***           7902 1980-12-17          800                    20
      7698 BLAKE      MA*****         7839 1981-05-01         2850                    30
      7499 ALLEN      SA******        7698 1981-02-20         1600        300         30
      7521 WARD       SA******        7698 1981-02-22         1250        500         30
      7654 MARTIN     SA******        7698 1981-09-28         1250       1400         30
      7844 TURNER     SA******        7698 1981-09-08         1500          0         30
      7900 JAMES      CL***           7698 1981-12-03          950                    30
      7782 CLARK      MA*****         7839 1981-06-09         2450                    10
      7934 MILLER     CL***           7782 1982-01-23         1300                    10

14 rows selected.

SQL> 

---- Let us create new table to apply data redaction on varchar column

-- Create table
SQL> show user;
USER is "SCOTT"
SQL> 

create table creditcard (cust_id number(4) primary key, card_no varchar2(19), gate_name varchar2(20), expiry_date DATE, PIN number(4));

insert into creditcard values(1000,'1234-1234-1234-1234','VISA POWER','01-JAN-2022',5555);
insert into creditcard values(1001,'2323-2323-2323-2323','VISA POWER','01-MAR-2028',6666);
insert into creditcard values(1002,'4321-4321-4321-4321','RUPAY GATE','09-JAN-2026',9999);
insert into creditcard values(1003,'6789-6789-6789-6789','VISA POWER','31-DEC-2022',8888);
insert into creditcard values(1004,'1234-5678-1234-5678','RUPAY GATE','31-DEC-2024',7777);


SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
---------- ------------------- -------------------- ------------------ ----------
      1000 1234-1234-1234-1234 VISA POWER           01-JAN-22                5555
      1001 2323-2323-2323-2323 VISA POWER           01-MAR-28                6666
      1002 4321-4321-4321-4321 RUPAY GATE           09-JAN-26                9999
      1003 6789-6789-6789-6789 VISA POWER           31-DEC-22                8888
      1004 1234-5678-1234-5678 RUPAY GATE           31-DEC-24                7777

SQL> 


-- Applying data redaction on varchar and number col
-- Fisrt add column to data redaction

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'scott',
    object_name   => 'creditcard',
    column_name   => 'PIN',
    policy_name   => 'redact_card_pin',
    function_type => DBMS_REDACT.full,
    expression    => '1=1'
  );
END;
/

PL/SQL procedure successfully completed.

SQL> show user;
USER is "SCOTT"
SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
---------- ------------------- -------------------- ------------------ ----------
      1000 1234-1234-1234-1234 VISA POWER           01-JAN-22                   0
      1001 2323-2323-2323-2323 VISA POWER           01-MAR-28                   0
      1002 4321-4321-4321-4321 RUPAY GATE           09-JAN-26                   0
      1003 6789-6789-6789-6789 VISA POWER           31-DEC-22                   0
      1004 1234-5678-1234-5678 RUPAY GATE           31-DEC-24                   0

SQL> 

SQL> show user;
USER is "SYS"
SQL> 
BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => 'scott',
    object_name   => 'creditcard',
    policy_name   => 'redact_card_pin'
  );
END;
/

PL/SQL procedure successfully completed.

--- redact card_no

SQL> show user;
USER is "SYS"
SQL> 
SQL> BEGIN
        DBMS_REDACT.ADD_POLICY(
        object_schema => 'scott',
        object_name => 'creditcard',
        column_name => 'card_no',
        policy_name => 'partially mask cardno',
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,3,12', -- put '*' from 3rd position to 12th position
        expression => '1=1'
);
END;
/

PL/SQL procedure successfully completed.

SQL> 
SQL> connect scott/scott
Connected.
SQL> show user;
USER is "SCOTT"
SQL> set lines 222
SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
---------- ------------------- -------------------- ------------------ ----------
      1000 12**-****-****-1234 VISA POWER           01-JAN-22                5555
      1001 23**-****-****-2323 VISA POWER           01-MAR-28                6666
      1002 43**-****-****-4321 RUPAY GATE           09-JAN-26                9999
      1003 67**-****-****-6789 VISA POWER           31-DEC-22                8888
      1004 12**-****-****-5678 RUPAY GATE           31-DEC-24                7777

SQL> 

SQL>

-- Again redefine (put '*' from 1st position to 12th position)

SQL> show user
USER is "SYS"
SQL> BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => 'scott',
    object_name   => 'creditcard',
    policy_name   => 'partially mask cardno'
  );
END;
/  

PL/SQL procedure successfully completed.

SQL>

SQL> BEGIN
        DBMS_REDACT.ADD_POLICY(
        object_schema => 'scott',
        object_name => 'creditcard',
        column_name => 'card_no',
        policy_name => 'partially mask cardno',
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,3,12', -- put '*' from 3rd position to 12th position
        expression => '1=1'
);
END;
/

SQL> 
BEGIN
        DBMS_REDACT.ADD_POLICY(
        object_schema => 'scott',
        object_name => 'creditcard',
        column_name => 'card_no',
        policy_name => 'partially mask cardno',
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12',
        expression => '1=1'
);
END;
/

PL/SQL procedure successfully completed.
SQL> 

-- Now verify

SQL> show user
USER is "SCOTT"
SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
---------- ------------------- -------------------- ------------------ ----------
      1000 ****-****-****-1234 VISA POWER           01-JAN-22                5555
      1001 ****-****-****-2323 VISA POWER           01-MAR-28                6666
      1002 ****-****-****-4321 RUPAY GATE           09-JAN-26                9999
      1003 ****-****-****-6789 VISA POWER           31-DEC-22                8888
      1004 ****-****-****-5678 RUPAY GATE           31-DEC-24                7777

SQL> 

To be added more ...

Oracle Database Security Assessment Tool - dbsat

About dbsat:

1) DBSAT is a light weight utility that will not impair system performance in a measurable way.
2) Oracle Database Security Assessment Tool (DBSAT) analyzes database configurations and security policies to uncover security risks and improve the security posture of Oracle Databases within your organization.
3) DBSAT reports on the state of user accounts, role and privilege grants, and policies that control the use of various security features in the database.

Benefits of Using DBSAT:
1) Quickly identify security configuration errors in your databases
2) Promote security best practices
3) Improve the security posture of your Oracle Databases
4) Reduce the attack surface and exposure to risk

How to install and use dbsat tool?

You can follow following tasks to complete installation and use.

Task 1: Install the Database Security Assessment Tool

-- download and copy dbsat software

[oracle@example01 oracle]$ cd /u01/oracle/admin/scripts/dbsat
[oracle@example01 dbsat]$ ls
dbsat.zip
[oracle@example01 dbsat]$ unzip dbsat.zip 

-- list out softwares
[oracle@example01 dbsat]$ ls
dbsat  dbsat.bat  dbsat.zip  Discover  sat_analysis.py  sat_collector.sql  sat_reporter.py  xlsxwriter

-- Verify the python version
[oracle@example01 dbsat]$ python -V
Python 2.6.6
[oracle@example01 dbsat]$ 


Task 2: Run the DBSAT Collector

$ export ORACLE_SID=tempdb
$ sqlplus / as sysdba
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ---------
TEMPDB    READ WRITE

SQL> alter user system identified by xxxx;
User altered.

SQL> 
Run the DBSAT Collector:
$ dbsat collect <connect_string> <destination>

e.g.,

dbsat collect system/xxxx@tempdb tempdb_dbsat

-- You can do like following to find help

[oracle@example01 dbsat]$ ./dbsat

Database Security Assessment Tool version 2.0.1 (December 2017)

    Usage: dbsat collect [ -n ] <database_connect_string> <output_file>
           dbsat report [ -a ] [ -n ] [ -x <section> ] <input_file>
           dbsat discover [-n] -c <config_file> <output_file>

    Options:
       -a  Report about all user accounts, including locked, 
           Oracle-supplied users
       -n  No encryption for output
       -x  Specify sections to exclude from report (may be repeated for
           multiple sections)
       -c  Configuration file for discoverer

[oracle@example01 dbsat]$

Example: To run dbsat collector, you can use custom user ( described below)

[oracle@example01 dbsat]$ ./dbsat collect dbsat_user/oracle dbsat_collection

Database Security Assessment Tool version 2.0.1 (December 2017)
...
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Setup complete.
SQL queries complete.
OS commands complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
DBSAT Collector completed successfully.

Calling /u01/oracle/admin/scripts/product/12.1.0/dbhome_1/bin/zip to encrypt dbsat_collection.json...

Enter password: 
Verify password: 
  adding: dbsat_collection.json (deflated 83%)
zip completed successfully.
[oracle@example01 dbsat]$ 
[oracle@example01 dbsat]$ ls -lrt
total 556
-r-xr-xr-x 1 oracle oinstall  28216 Dec 20 16:35 sat_analysis.py
-r-xr-xr-x 1 oracle oinstall  43181 Jan  8 13:43 sat_collector.sql
-r-xr-xr-x 1 oracle oinstall  12579 Jan 11 11:21 dbsat.bat
-r-xr-xr-x 1 oracle oinstall  12433 Jan 11 11:21 dbsat
-r-xr-xr-x 1 oracle oinstall 247465 Jan 16 17:47 sat_reporter.py
drwxr-xr-x 5 oracle oinstall   4096 May 10 10:51 Discover
-r-------- 1 oracle oinstall  25197 May 10 11:24 sat_analysis.pyc
drwxr-xr-x 2 oracle oinstall   4096 May 10 11:24 xlsxwriter
-rwxrwxrwx 1 oracle oinstall 143361 May 15 07:08 dbsat_col_report.html
-rw------- 1 oracle oinstall  25352 May 23 07:38 dbsat_collection.zip
[oracle@example01 dbsat]$ 


Note: Remember your password given during collection.


Task 3: Run the DBSAT Reporter

Run the DBSAT Reporter.
[oracle@example01 sat]$ dbsat report [-a] [-n] [-x <section>] <pathname>

e.g.,

[oracle@example01 dbsat]$ ./dbsat report dbsat_collection

Database Security Assessment Tool version 2.0.1 (December 2017)
....

Archive:  tempdb_sat.zip
[tempdb_sat.zip] tempdb_sat.json password: 
  inflating: tempdb_sat.json         
DBSAT Reporter ran successfully.

Calling /usr/bin/zip to encrypt the generated reports...

Enter password: 
Verify password: 
        zip warning: tempdb_sat_report.zip not found or empty
  adding: tempdb_sat_report.txt (deflated 77%)
  adding: tempdb_sat_report.html (deflated 84%)
  adding: tempdb_sat_report.xlsx (deflated 3%)
  adding: tempdb_sat_report.json (deflated 82%)
zip completed successfully.
[oracle@example01 dbsat]$ 

-- Now Unzip the Report file

[oracle@example01 dbsat]$ ls *.zip
tempdb_sat_report.zip 

$ unzip tempdb_sat_report.zip

[oracle@example01 dbsat]$ unzip tempdb_sat_report.zip
Archive:  tempdb_sat_report.zip
[tempdb_sat_report.zip] tempdb_sat_report.txt password: 
  inflating: tempdb_sat_report.txt   
  inflating: tempdb_sat_report.html  
  inflating: tempdb_sat_report.xlsx  
  inflating: tempdb_sat_report.json  
[oracle@example01 dbsat]$ 

Task 4: Analyze report now

DBSAT Reports
DBSAT produces output in multiple formats for various audiences and purposes.

The HTML report provides detailed results of the assessment in a format that is easy to navigate. The Excel format provides a high-level summary of each finding without the detailed output included in the HTML report. It also allows you to add columns for your tracking and prioritization purposes. A report in text format makes it convenient to copy portions of the output for other usage.

You can create specified user to use dbsat with following configuration

1) Creating a Database User to run DBSAT:
Example:
grant create session to dbsat identified by oracle; 
grant select on sys.registry$history to dbsat; 
grant select_catalog_role to dbsat; 
grant audit_viewer to dbsat; 
grant capture_admin to dbsat; 
grant select on sys.dba_users_with_defpwd to dbsat; 
grant select on audsys.aud$unified to dbsat;

2) Start Collector

./dbsat collect dbsat/oracle dbsat_col

[oracle@example01 dbsat]$ ./dbsat collect dbsat/oracle dbsat_collection

Database Security Assessment Tool version 2.0.1 (December 2017)
This tool is intended to assist in you in securing your Oracle database

...
Setup complete.
SQL queries complete.
/bin/cat: /u01/oracle/admin/scripts/product/12.1.0/dbhome_1/network/admin/listener.ora: No such file or directory
Warning: Exit status 256 from OS rule: listener.ora
/bin/ls: cannot access /u01/oracle/admin/scripts/product/12.1.0/dbhome_1/network/admin/listener.ora: No such file or directory
Warning: Exit status 512 from OS rule: ls_listener.ora
OS commands complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
DBSAT Collector completed successfully.

Calling /u01/oracle/admin/scripts/product/12.1.0/dbhome_1/bin/zip to encrypt dbsat_col.json...

Enter password: 
Verify password: 
  adding: dbsat_col.json (deflated 86%)
zip completed successfully.
[oracle@example01 dbsat]$ 


3) Run DBSAT Reporter:

./dbsat report dbsat_col

[oracle@example01 dbsat]$ ./dbsat report dbsat_collection

Database Security Assessment Tool version 2.0.1 (December 2017)

This tool is intended to assist in you in securing your Oracle database
....
Archive:  dbsat_col.zip
[dbsat_col.zip] dbsat_col.json password: 
  inflating: dbsat_col.json          
DBSAT Reporter ran successfully.

Calling /usr/bin/zip to encrypt the generated reports...

Enter password: 
Verify password: 
        zip warning: dbsat_col_report.zip not found or empty
  adding: dbsat_col_report.txt (deflated 77%)
  adding: dbsat_col_report.html (deflated 84%)
  adding: dbsat_col_report.xlsx (deflated 3%)
  adding: dbsat_col_report.json (deflated 82%)
zip completed successfully.
[oracle@example01 dbsat]$ 

Identify sensitive data with DBSAT:

One big enhancement is the introduction of the new Discoverer component in DBSAT, which can be used to identify sensitive data within your database.
For this you need "discoverer" configuration to find out sensitive data. So it includes two major steps:

•Configuring the Discoverer
•Configure dbsat.config
•Configure the Pattern file
•Configure the Exclusion List file
•Running the Discoverer

What is the Discoverer?
The Discoverer component is a Java-based utility (discoverer.jar) which identifies sensitive data by executing SQL queries primarily against the Data Dictionary. Which objects (schemas, tables, columns) the Discoverer marks as sensitive, depends on its configuration files. At the end a Database Sensitive Data Assessment Report is generated as HTML and CSV file. You can run the Discoverer on the Database server or any other machine, e.g. your Laptop. To run the Discoverer you just need a Java Runtime Environment (JRE) 1.6 or higher.

Configuration:
Before you execute the Discoverer, you have to configure it using a file called dbsat.config (any name can be used). A sample configuration can be found in the subdirectory Discover/conf.

$ pwd
/u01/oracle/admin/scripts/dbsat/Discover
$ ls
bin  conf  lib
$ cd conf/
$ ls
sample_dbsat.config  sensitive_en.ini

$ chmod 775 sample_dbsat.config 
$ cp sample_dbsat.config dbsat.config
$ ls -lrt
total 24
-r--r--r-- 1 oracle oinstall 12796 Jan 16 22:58 sensitive_en.ini
-rwxrwxr-x 1 oracle oinstall  2385 Jan 16 22:58 sample_dbsat.config
-rwxr-xr-x 1 oracle oinstall  2385 May 24 07:54 dbsat.config

$ vi dbsat.config

DB_HOSTNAME = example01
DB_PORT = 1521
DB_SERVICE_NAME =  tempdb

-- To discover sensitive data

$ export JAVA_HOME=$ORACLE_HOME/jdk/jre
$ ./dbsat discover -c Discover/conf/dbsat.config tempdb_data

Example:

$ ./dbsat discover -c Discover/conf/dbsat.config tempdb_data
....
Enter username: dbsat
Enter password: 
DBSAT Discover ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...

Enter password: 
Verify password: 
        zip warning: tempdb_data_report.zip not found or empty
  adding: tempdb_data_discover.html (deflated 74%)
  adding: tempdb_data_discover.csv (deflated 44%)
Zip completed successfully.
$

[oracle@example01 dbsat]$ ls -lrt
total 404
-r-xr-xr-x 1 oracle oinstall  28216 Dec 20 16:35 sat_analysis.py
-r-xr-xr-x 1 oracle oinstall  43181 Jan  8 13:43 sat_collector.sql
-r-xr-xr-x 1 oracle oinstall  12579 Jan 11 11:21 dbsat.bat
-r-xr-xr-x 1 oracle oinstall  12433 Jan 11 11:21 dbsat
-r-xr-xr-x 1 oracle oinstall 247465 Jan 16 17:47 sat_reporter.py
drwxr-xr-x 5 oracle oinstall   4096 May 10 10:51 Discover
-r-------- 1 oracle oinstall  25197 May 10 11:24 sat_analysis.pyc
drwxr-xr-x 2 oracle oinstall   4096 May 10 11:24 xlsxwriter
-rw-r--r-- 1 oracle oinstall   6596 May 24 08:17 load_data.sql
-rw------- 1 oracle oinstall     68 May 24 08:22 discoverer.log
-rw------- 1 oracle oinstall   4278 May 24 08:23 tempdb_data_report.zip
[oracle@example01 dbsat]$ 

Reference:
https://docs.oracle.com/cd/E76178_01/SATUG/toc.htm#SATUG-GUID-DB214E36-E822-4EF7-AB83-16967FFDA669
http://christian-gohmann.de/2018/01/26/identify-sensitive-data-with-dbsat/
https://docs.oracle.com/cd/E93129_01/SATUG/toc.htm#SATUG-GUID-66685BC1-1306-417B-82C4-D28EDC895FF3

Configuring the Enterprise Manager Audit System - OEM 12c/13c

Audit Log Data:

Certain security operations should be audited to ensure compliance with the various act in different counties. In the EM environment, there is auditing for both the EM application and repository database.

EM Auditing

Enterprise Manager offers over 150 options for auditing that can be configured using EMCLI but by default, it audits a few basic and infrastructure operations and this auditing cannot be turned off. The audit data is stored in the repository and the volume of this data can impact the performance of the system so it is important to externalize or archive the data at regular intervals. An API called EM_AUDIT_EXTERNALIZATION is provided to externalize the data. The data is stored in XML format and the files are named NNNNN.xml where is a configurable value and NNNNN is a number. The audit externalization service is enabled/configured using the EMCLI update_audit_settings command and the externalization of the audit data is performed by a repository job called EM Audit Externalization Service. The status of this job can be seen in Cloud Control by clicking on Setup / Manage Cloud Control / Repository.

To enable and configure the externalization of the audit data, follow the commands below:

1. Connect to the database as sysdba and create an external directory

SQL> CONNECT / AS sysdba;

SQL> CREATE DIRECTORY EM_AUDIT AS '/u01/app/oracle/em_audit';

SQL> GRANT READ,WRITE ON DIRECTORY EM_AUDIT to SYSMAN;

e.g., Connect to OMS in console mode:

$ pwd

/u01/app/oracle/oem

$ mkdir em_audit

$ cd em_audit/

$ pwd

/u01/app/oracle/oem/em_audit

$ emtst

$ sqlplus / as sysdba

SQL> CREATE DIRECTORY EM_AUDIT AS '/u01/app/oracle/oem/em_audit';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY EM_AUDIT to SYSMAN;

Grant succeeded.

SQL>

2. Execute the command below to configure the externalization:

NOTE: be sure the select the specific operations that should be audited. Setting this to the value of “ALL” will mostly likely create more audit files than necessary.

Syntax:

emcli update_audit_settings

      -audit_switch="ENABLE/DISABLE"

      -operations_to_enable="name of the operations to enable, for all oprtations

       use ALL"

      -operations_to_disable="name of the operations to disable, for all

       oprtations use ALL"

      -externalization_switch="ENABLE/DISABLE"

      -directory_name="directory_name (DB Directory)"

      -file_prefix="file_prefix"

      -file_size="file_size (Bytes)"

      -data_retention_period="data_retention_period (Days)"

Example:

$ emcli update_audit_settings

-audit_switch="ENABLE"

-operations_to_enable="ALL"

-externalization_switch="ENABLE "

-directory="EM_AUDIT”

-file_prefix="EMAudit"

-data_retention_period="30"

3. The following command will show the settings:

The above commands will enable the externalization of the audit data using a name such as EMAudit00001.xml and will save the files in the directory /u01/app/oracle/em_audit. Once this is done, archiving of the mandatory audit records from the operating system can be done via Oracle Audit Vault or tape/disk backups. For more information, refer to the Enterprise Manager Cloud Control Security Guide.

Output: ( before enable audit) :

$ hostname

example01.company.com

$ export EMCLI_HOME=/u01/app/oracle/oem

$ export PATH=$PATH:$EMCLI_HOME/bin

$ emcli login -username=gmohapatra

Enter password :

Login successful

$ emcli show_audit_settings

User Activity Audit    : Enabled  (2 out of 171 operations)

Externalization Switch : Disabled

Directory              : Not configured

File Prefix            : em_audit

File Size              : 5000000 Bytes

Data Retention Period  : 365 Days

*Infrastructure Audit is always enabled

.................................................

$

Enabling Audit:

$ emcli show_audit_settings

User Activity Audit    : Enabled  (2 out of 171 operations)

Externalization Switch : Disabled

Directory              : Not configured

File Prefix            : em_audit

File Size              : 5000000 Bytes

Data Retention Period  : 365 Days

*Infrastructure Audit is always enabled

.................................................

$

$ emcli update_audit_settings 

-audit_switch="ENABLE" 

-operations_to_enable="ALL" 

-externalization_switch="ENABLE" 

-directory="EM_AUDIT" 

-file_prefix="EMAudit" 

-data_retention_period="30"

Successfully updated the audit settings.

$

$ emcli show_audit_settings

User Activity Audit    : Enabled  (For all Operations)

Externalization Switch : Enabled

Directory              : EM_AUDIT

File Prefix            : EMAudit

File Size              : 5000000 Bytes

Data Retention Period  : 30 Days

*Infrastructure Audit is always enabled

.................................................

$

Now Enable the Audit:

$ emcli enable_audit

Successfully enabled EM auditing.

$

Find Operations List:

emcli show_operations_list

e.g.,

$ emcli show_operations_list

Operation ID                         Operation Name                           Infrastructure Operation

ADD_AGENT_REGISTRATION_PASSWORD      Add Registration Password                 NO                     

ADD_CS_TARGET_ASSOC                  Add Standard-Target Association           NO                     

AGENT_REGISTRATION_PASSWORD_USAGE    Registration Password Usage               NO                     

AGENT_RESYNC                         Resync Agent                              NO                     

AG_AUD_CREATE                        Create Administration Groups              NO                     

AG_AUD_DELETE                        Delete Administration Groups              NO                     

AG_AUD_MODIFY                        Modify Administration Groups              NO                     

ALLOCATE_QUOTA                       Allocate quota                            NO                     

…..

Repository Database Auditing: ( As usual Database level audit):

Oracle always audits certain operations regardless of the database audit settings. This is referred to as Mandatory Auditing and the audit records are written to the operating system in the destination specified by the initialization parameter AUDIT_FILE_DEST.

Mandatory auditing includes these operations:

 » SYSDBA and SYSOPER logins

» CREATE/ALTER/DROP AUDIT POLICY

» AUDIT/NOAUDIT

» EXECUTE of the DBMS_FGA PL/SQL package

» EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package

» ALTER TABLE attempts on the AUDSYS audit trail table

» Top level statements by the administrative users SYS, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM, until the database opens. When the database opens, Oracle Database audits these users using the audit configurations in the system.

» All configuration changes that are made to Oracle Database Vault

The agent that resides on each OMS server logs into the repository every few minutes for self monitoring and generates an audit record for each login. Therefore, it is very important that the audit records are regularly archived and purged. The steps for doing this may vary according to a company’s security requirements but a sample setup is provided below.

Searching the Audit Data

You can search for audit data that has been generated over a specified period. You can also search for the following:

• Audit details of a specific user operation or all user operations
• Audit details of operations with a Success or Failure status or All operations.

From the Setup menu, select Security and then Audit Data. The Audit Data page is displayed. Specify the search criteria in the fields and click Go. The results are displayed in the Summary table.

Taking reports:
How can I take daily / weekly / monthly reports related to these reports? 

Navigate to above page in EM console to check the history of the EM user audits 

setup --> security --> audit data --> On the self of screen there is Search option 


Search with the Data Range as per your requirement "Most Recent" and "Time Interval" 

Frequently asked question:

Question: When .xml files grow in "em_audit" ( created folder for directory), can we delete older .xml files rather than reducing retention in case of file system full situations?  Or any thing to do? 

Ans: It is recommended to not to delete the files directly from the file system. 
To purge the audit data from repository database and then you can remove the files. 
How to purge audit data from the repository database?
Audit logs table will be purged if audit externalization job is executed and externalization switch is enabled.

EM Audit Externalization Service job is responsible for audit data purging. This DBMS Job is scheduled to run daily to export the audit data from the table SYSMAN.MGMT$AUDIT_LOG to xml files. Xml files with the format omsmyomshost_YYYY-MM-DD-xxxxx.xml will be created in the audit directory (example: EM_DIR) specified using emcli update_audit_settings command. The file size will not exceed 10000 bytes.
Setup --> Manage Cloud Control --> Repository -->

 Purge the records Manually:

This can be done manually or via a purge job that performs the purge at a specified time interval. The recommendation is to setup a job that will purge the records at a specified time interval and is the approach in the example shown below. Note that purging a large audit trail can take time to complete so it is wise to schedule the job so that it runs during a time when the database is not too busy. For further details on the process and an explanation for each parameter used in the example, refer to the Database Security Guide.

1. Initialize the audit trail cleanup operation.

SQL> begin

dbms_audit_mgmt.init_cleanup

( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

DEFAULT_CLEANUP_INTERVAL => 12);

end;

/

2. Setup an archive timestamp for the audit records. The RAC_INSTANCE_NUMBER refers to the instance number when using a RAC database. This must be set for each instance since the mandatory audit records are stored on the operating system and therefore for each instance. If this is a standalone database, use the number 0.

-- In Rac

SQL> begin

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP

(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

LAST_ARCHIVE_TIME => TO_DATE('2018-04-15 09:00:00','YYYY-MM-DD HH:MI:SS'), RAC_INSTANCE_NUMBER => 1);

END;

/

-- In Stand-alone

SQL> begin

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP

(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

LAST_ARCHIVE_TIME => TO_DATE('2018-04-15 09:00:00','YYYY-MM-DD HH:MI:SS'), RAC_INSTANCE_NUMBER => 0);

END;

/

3. Create and schedule the purge job

SQL> BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB

( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

AUDIT_TRAIL_PURGE_INTERVAL => 12,

AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_Cleanup',

USE_LAST_ARCH_TIMESTAMP => TRUE);

END;

/

Reference : 
How To Manage Cloud Control Audit Data with emcli show_audit_settings and emcli update_audit_settings ( Doc ID 1499609.1 ) 
Section : How to purge audit data from the repository database?