DBMS_PRIVILEGE_CAPTURE

The DBMS_PRIVILEGE_CAPTURE package provides an interface 
to database privilege analysis.

References:
http://docs.oracle.com/database/121/DVADM/priv_analysis.htm#DVADM591
http://docs.oracle.com/database/121/ARPLS/d_priv_prof.htm#ARPLS74328
https://oracle-base.com/articles/12c/capture-privilege-usage-12cr1

Tested on:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

1.) Create user to be analyzed:

create user checkprivs identified by test;
grant dba to checkprivs;
grant resource to checkprivs;
grant connect to checkprivs;

2.) Creating the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'CHECK_PRIVS',
    type        => DBMS_PRIVILEGE_CAPTURE.g_role_and_context,
    roles       => role_name_list('DBA', 'RESOURCE'),
    condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''CHECKPRIVS'''
  );

  DBMS_PRIVILEGE_CAPTURE.enable_capture(
    name        => 'CHECK_PRIVS'
  );
END;
/

3.) Some commands:

conn checkprivs/test;

create table check1 
(num number);

insert into check1 values (1);
commit;

select * from v$database;

truncate table check1;

insert into check1 values (2);
commit;

alter table check1 add (dt date);

update check1 set dt=sysdate;

commit;

4.) Stopping the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.disable_capture(
    name        => 'CHECK_PRIVS'
  );

  DBMS_PRIVILEGE_CAPTURE.generate_result(
    name        => 'CHECK_PRIVS'
  );
END;
/


5.) Checking the Results:
-- Thanks to ORACLE-BASE for the queries:

COLUMN username FORMAT A20
COLUMN sys_priv FORMAT A20

SELECT username, sys_priv
FROM   dba_used_sysprivs
WHERE  capture = 'CHECK_PRIVS'
ORDER BY username, sys_priv;

USERNAME             SYS_PRIV
-------------------- --------------
CHECKPRIVS           CREATE SESSION
CHECKPRIVS           CREATE TABLE

COLUMN username FORMAT A20
COLUMN used_role FORMAT A30
COLUMN sys_priv FORMAT A20
COLUMN path FORMAT A50
SET LINESIZE 200

SELECT username, sys_priv, used_role, path
FROM   dba_used_sysprivs_path
WHERE  capture = 'CHECK_PRIVS'
ORDER BY username, sys_priv;

USERNAME             SYS_PRIV             USED_ROLE                      PATH
-------------------- -------------------- ------------------------------ --------------------------------------------------
CHECKPRIVS           CREATE SESSION       EM_EXPRESS_BASIC               GRANT_PATH('CHECKPRIVS', 'DBA', 'EM_EXPRESS_ALL',
                                                                         'EM_EXPRESS_BASIC')

CHECKPRIVS           CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('CHECKPRIVS', 'DBA', 'DATAPUMP_EXP_FULL
                                                                         _DATABASE')

CHECKPRIVS           CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('CHECKPRIVS', 'DBA', 'DATAPUMP_EXP_FULL
                                                                         _DATABASE', 'EXP_FULL_DATABASE')
                                                                         
                                                                         COLUMN username FORMAT A20
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A20
COLUMN object_type FORMAT A11

SELECT username, obj_priv, object_owner, object_name, object_type 
FROM   dba_used_objprivs
WHERE  capture = 'CHECK_PRIVS';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          OBJECT_TYPE
-------------------- -------- --------------- -------------------- -----------
CHECKPRIVS           SELECT   SYS             V_$DATABASE          VIEW


COLUMN username FORMAT A20
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A20
COLUMN used_role FORMAT A20
COLUMN path FORMAT A30
SET LINESIZE 200

SELECT username, obj_priv, object_owner, object_name, used_role, path 
FROM   dba_used_objprivs_path
WHERE  capture = 'CHECK_PRIVS';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          USED_ROLE            PATH
-------------------- -------- --------------- -------------------- -------------------- ------------------------------
CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'IMP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'DATAPUMP_IMP_FULL_DATABASE'
                                                                                        , 'EXP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'EM_EXPRESS_ALL', 'EM_EXPRES
                                                                                        S_BASIC', 'SELECT_CATALOG_ROLE
                                                                                        ')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'DATAPUMP_EXP_FULL_DATABASE'
                                                                                        , 'EXP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'SELECT_CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'EXP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'DATAPUMP_IMP_FULL_DATABASE'
                                                                                        , 'IMP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')                                                                       

6.) Drop Capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.drop_capture(
    name        => 'CHECK_PRIVS'
  );
END;
/
 

7.) Change:

According with the results we can create a ROLE with the GRANTS below:

CREATE SESSION
CREATE TABLE  
SELECT ON SYS.V_$DATABASE

We can REVOKE DBA from user CHECKPRIVS;
Advertisements

Unified Audit

References:
http://docs.oracle.com/database/121/SQLRF/statements_5001.htm#SQLRF56055
http://docs.oracle.com/database/121/DBSEG/auditing.htm#DBSEG630
https://docs.oracle.com/database/121/TDPSG/GUID-BF747771-01D1-4BFB-8489-08988E1181F6.htm#TDPSG94453

Tested on:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

Please note that Unified Auditing options is enabled. Check your license
The link below describe how to enable.
https://docs.oracle.com/database/121/TDPSG/GUID-BF747771-01D1-4BFB-8489-08988E1181F6.htm#TDPSG50000

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

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

1.) Listing All privileges:

select name
FROM   system_privilege_map
ORDER BY name;

2.) Creating users:

create user T_AUD_1 identified by test;
create user T_AUD_2 identified by test;
create user T_AUD_3 identified by test;

grant connect, resource to     t_aud_1,t_aud_2,t_aud_3;
alter user t_aud_1 quota unlimited on users;
alter user t_aud_2 quota unlimited on users;
alter user t_aud_3 quota unlimited on users;
grant create view to t_aud_1;

3.) Policies:

CREATE AUDIT POLICY PLCY_T_AUD_01
  PRIVILEGES CREATE VIEW
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''T_AUD_1'''
  EVALUATE PER SESSION;    

audit policy PLCY_T_AUD_01;

CREATE AUDIT POLICY PLCY_T_AUD_02
  PRIVILEGES CREATE TABLE
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''T_AUD_2'''
  EVALUATE PER SESSION;    

audit policy PLCY_T_AUD_02;

CREATE AUDIT POLICY PLCY_T_AUD_03
  PRIVILEGES CREATE SEQUENCE
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''T_AUD_3'''
  EVALUATE PER SESSION;    

audit policy PLCY_T_AUD_03;

4.) Listing Policies:

col policy_name format a15
col audit_option format a30
col audit_condition format a60

SELECT policy_name,
       audit_option,
       condition_eval_opt,
       audit_condition,OBJECT_SCHEMA,OBJECT_NAME
FROM   audit_unified_policies
where policy_name like '%PLCY_T_AUD_0%';

POLICY_NAME     AUDIT_OPTION      CONDITION AUDIT_CONDITION                                      OBJECT_SCH OBJECT_NAME
--------------- ----------------- --------- ------------------------------------------------------------ ---------- ------
PLCY_T_AUD_01   CREATE VIEW       SESSION   SYS_CONTEXT('USERENV', 'SESSION_USER') = 'T_AUD_1'           NONE       NONE
PLCY_T_AUD_02   CREATE TABLE      SESSION   SYS_CONTEXT('USERENV', 'SESSION_USER') = 'T_AUD_2'           NONE       NONE
PLCY_T_AUD_03   CREATE SEQUENCE   SESSION   SYS_CONTEXT('USERENV', 'SESSION_USER') = 'T_AUD_3'           NONE       NONE

select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME             ENABLED_ SUC FAI
--------------- ----------------------- -------- --- ---
ALL USERS       ORA_SECURECONFIG        BY       YES YES
ALL USERS       ORA_LOGON_FAILURES      BY       NO  YES
ALL USERS       PLCY_T_AUD_03           BY       YES YES
ALL USERS       PLCY_T_AUD_01           BY       YES YES
ALL USERS       PLCY_T_AUD_02           BY       YES YES


5.) Testing:

conn t_aud_1/test;
drop table x;
create table x
(num number);

insert into x values (1);
insert into x values (2);
insert into x values (3);
commit;

create view v_aud1 as
select * from x;

conn t_aud_2/test
create table x2
(num number);

insert into x2
values
(1);

commit;

drop table x2;

conn t_aud_3/test
drop sequence seq_aud_3;
create sequence seq_aud_3;

6.) Checking audit trail

COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
FROM   unified_audit_trail
WHERE  dbusername like 'T_AUD%'
ORDER BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- ------------
04-MAR-16 10.08.19.096000 AM   T_AUD_1    LOGON
04-MAR-16 12.24.35.390000 PM   T_AUD_2    CREATE TABLE         T_AUD_2    X2
04-MAR-16 12.39.04.908000 PM   T_AUD_2    CREATE TABLE         T_AUD_2    X2
04-MAR-16 12.39.05.068000 PM   T_AUD_3    CREATE SEQUENCE      T_AUD_3    SEQ_AUD_3
04-MAR-16 12.40.00.640000 PM   T_AUD_1    CREATE VIEW          T_AUD_1    V_AUD1
04-MAR-16 12.40.00.641000 PM   T_AUD_1    SELECT               T_AUD_1    X

7.) Performance:

-- Write immediate on the AUDIT TRAIL (SYNC)
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
    audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode, 
    audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_immediate_write
  );
END;
/

-- Enqueue a message for AUDIT TRAIL (ASYNC)
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
    audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode, 
    audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_queued_write
  );
END;
/

8.) Drop POLICY

NOAUDIT POLICY PLCY_T_AUD_01;
DROP AUDIT POLICY PLCY_T_AUD_01;

NOAUDIT POLICY PLCY_T_AUD_02;
DROP AUDIT POLICY PLCY_T_AUD_02;

NOAUDIT POLICY PLCY_T_AUD_03;
DROP AUDIT POLICY PLCY_T_AUD_03;

DBMS_REDACT

http://docs.oracle.com/database/121/ARPLS/d_redact.htm#ARPLS73800
Tested ON:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
1.) Create users:
create user red1 identified by red1;
 grant connect, resource , select any table to red1;
create user red2 identified by red2;
 grant connect, resource , select any table to red2;
grant unlimited tablespace to red1;
grant execute on sys.dbms_redact TO red1;
2.) Create table:
create sequence red1.seq_info_id minvalue 1 maxvalue 999999999999 nocache order increment by 1;
create table red1.info
 (info_id number,
 passport varchar2(10),
 dt_create date,
 account_no number)
 tablespace users;
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'AA0000', sysdate, '123456789');
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'AA9999', sysdate, '123789456');
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'BB0000', sysdate, '123456000');
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'CC1234', sysdate, '123456888');
COMMIT;
SELECT * FROM red1.info;
 INFO_ID PASSPORT   DT_CREAT ACCOUNT_NO
 ---------- ---------- -------- ----------
 1 AA0000     29/02/16  123456789
 2 AA9999     29/02/16  123789456
 3 BB0000     29/02/16  123456000
 4 CC1234     29/02/16  123456888
3.) ADDING POLICY:
 conn red1/red1
Connected.
 RED1 @ > show user
 USER is "RED1"
BEGIN
 DBMS_REDACT.add_policy(
 object_schema => 'RED1',
 object_name   => 'INFO',
 column_name   => 'PASSPORT',
 policy_name   => 'REDACT_INFO',
 function_type => DBMS_REDACT.full,
 expression    => '1=1'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREAT ACCOUNT_NO
 ---------- ---------- -------- ----------
 1            29/02/16  123456789
 2            29/02/16  123789456
 3            29/02/16  123456000
 4            29/02/16  123456888
4.) Checking the POLICY (as sysdba) or need to GRANT SELECT to RED1.
select * from REDACTION_POLICIES
OBJECT_OWNER                   OBJECT_NAME                    POLICY_NAME                    EXPRESSION                               ENABLE  POLICY_DESCRIPTION
 ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ------- ------------------------------
 RED1                           INFO                           REDACT_INFO                    1=1                                      YES
5.) Changing the POLICY:
conn red1/red1
BEGIN
 DBMS_REDACT.alter_policy (
 object_schema       => 'RED1',
 object_name         => 'INFO',
 policy_name         => 'REDACT_INFO',
 action              => DBMS_REDACT.modify_column,
 column_name         => 'PASSPORT',
 function_type       => DBMS_REDACT.partial,
 function_parameters => 'VVVVVV,VVVVVV,#,1,4'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 ####00     29-FEB-16  123456789
 2 ####99     29-FEB-16  123789456
 3 ####00     29-FEB-16  123456000
 4 ####34     29-FEB-16  123456888
6.) Adding Another Column
BEGIN
 DBMS_REDACT.alter_policy (
 object_schema       => 'RED1',
 object_name         => 'INFO',
 policy_name         => 'REDACT_INFO',
 action              => DBMS_REDACT.add_column,
 column_name         => 'ACCOUNT_NO',
 function_type       => DBMS_REDACT.partial,
 function_parameters => '0,1,9'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 ####00     29-FEB-16          0
 2 ####99     29-FEB-16          0
 3 ####00     29-FEB-16          0
 4 ####34     29-FEB-16          0
7.) Filtering by CONTEXT - Column PASSPORT:
BEGIN
 DBMS_REDACT.alter_policy (
 object_schema       => 'RED1',
 object_name         => 'INFO',
 policy_name         => 'REDACT_INFO',
 action              => DBMS_REDACT.modify_expression,
 column_name         => 'PASSPORT',
 expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''RED1'''
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 AA0000     29-FEB-16  123456789
 2 AA9999     29-FEB-16  123789456
 3 BB0000     29-FEB-16  123456000
 4 CC1234     29-FEB-16  123456888
conn red2/red2
17:53:51 SQL> SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 ####00     29-FEB-16          0
 2 ####99     29-FEB-16          0
 3 ####00     29-FEB-16          0
 4 ####34     29-FEB-16          0
8.) Droping POLICY:
conn red1/red1
BEGIN
 DBMS_REDACT.drop_policy (
 object_schema => 'RED1',
 object_name   => 'INFO',
 policy_name   => 'REDACT_INFO'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 AA0000     29-FEB-16  123456789
 2 AA9999     29-FEB-16  123789456
 3 BB0000     29-FEB-16  123456000
 4 CC1234     29-FEB-16  123456888

VPD – Using Function

1.)
 create table cmagno.teste_vpd
 (num number,
 dt date,
 text varchar2(1000))
 tablespace users;
create sequence cmagno.seq_test 
minvalue 1 maxvalue 999999999999 
nocache order increment by 1;
2.)
declare
 i number;
 begin
for i in 1..1000 loop
 insert into cmagno.teste_vpd values
 (cmagno.seq_test.nextval, sysdate,'TEST :' || TO_CHAR(I));
 end loop;
 COMMIT;
 end;
 /
3.)
 SQL> SELECT COUNT(*) FROM cmagno.teste_vpd;
COUNT(*)
 ----------
 1000
1* select * from cmagno.teste_vpd where rownum<10
 SQL> /
NUM DT        TEXT
 ---------- --------- ----------------------------
 546 25-FEB-16 TEST :546
 547 25-FEB-16 TEST :547
 548 25-FEB-16 TEST :548
 549 25-FEB-16 TEST :549
 550 25-FEB-16 TEST :550
 551 25-FEB-16 TEST :551
 552 25-FEB-16 TEST :552
 553 25-FEB-16 TEST :553
 554 25-FEB-16 TEST :554
SQL>
 /
4.) Function
CREATE OR REPLACE FUNCTION CMAGNO.num_gt_500 
( v_schema IN VARCHAR2,  v_objname IN VARCHAR2)
RETURN VARCHAR2 AS
 v_where VARCHAR2 (200);
BEGIN
 v_where := 'num>=500';
 RETURN (v_where);
 END num_gt_500;
 /
5.) Add Policy
 BEGIN
 DBMS_RLS.ADD_POLICY (
 object_schema     => 'CMAGNO',
 object_name       => 'TESTE_VPD',
 policy_name       => 'NUM_GT_500_PLCY',
 policy_function   => 'NUM_GT_500',
 sec_relevant_cols => 'NUM');
 END;
 /
6.) Just rows with NUM >= 500
SQL> SELECT COUNT(*) FROM cmagno.teste_vpd;
COUNT(*)
 ----------
 501
7.) Droping Policy:
 BEGIN
 DBMS_RLS.DROP_POLICY (object_schema     => 'CMAGNO',
 object_name       => 'TESTE_VPD',
 policy_name       => 'NUM_GT_500_PLCY');
 END;
 /
8.)
 SQL> SELECT COUNT(*) FROM cmagno.teste_vpd;
COUNT(*)
 ----------
 1000