Oracle 12c gives the ability to perform a user privilege analysis to bounce against granted privileges. This allows you to reduce granted privilege down to what the individual user actually needs and no more. This article gives a procedure to perform User privilege analysis.
- Logon oracle server as the oracle software owner than logon to sqlplus with sysdba privilegs. If this is done by none privileged user account, the user must have the CAPTURE_ADMIN role granted.
[root@linux2 ~]# su – oracle
Last login: Thu Dec 15 07:50:00 EST 2016 on pts/1
Enter database to use:
1 – ORCL
2 – CDB1
Option >
1
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:05:59 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
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
SQL>
- Define the privilege analysis policy with the CREATE_CAPTURE procedure of DBMS_PRIVILEGE_CAPTURE package. This example below will capture all privileges used.
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => ‘user_priv_capture’,
description => ‘Capture the privileges by user test’,
type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
END;
/
- Enable the privilege analysis policy.
SQL> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (‘user_priv_capture’);
PL/SQL procedure successfully completed.
SQL>
- Logon as the user test and perform DML processing.
SQL> connect test/test
Connected.
SQL> insert into test.test_times(pers_id, first_name) values(99,’test’);
1 row created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from 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
[oracle@linux2 ~]$
- Logon as sysdba and disable the privilege analysis policy’s recording of privilege use.
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:25:29 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
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
SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (‘user_priv_capture’);
PL/SQL procedure successfully completed.
SQL>
- Generate privilege analysis results.
SQL> EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (‘user_priv_capture’);
PL/SQL procedure successfully completed.
SQL>
- Query the table dba_used_privs to see privileges used during the capture period.
SQL> column username format a12
column sys_priv format a15
column object_owner format a10
column object_name format a25
set pagesize 500
SELECT username, sys_priv, object_owner, object_name FROM dba_used_privs WHERE capture = ‘user_priv_capture’;
SQL> SQL> SQL> SQL> SQL>
USERNAME SYS_PRIV OBJECT_OWN OBJECT_NAME
———— ————— ———- ————————-
TEST CREATE SESSION
TEST SYS USER_TABLES
APEX_040200 SYS DBMS_OUTPUT
TEST CREATE SESSION
TEST CREATE SESSION
TEST CREATE SESSION
TEST CREATE SESSION
TEST SYS DUAL
APEX_040200 SYS DBMS_OUTPUT
TEST CREATE SESSION
TEST CREATE SESSION
TEST CREATE SESSION
TEST SYS DUAL
APEX_040200 SYS DEFAULT_JOB_CLASS
TEST CREATE SESSION
TEST SYSTEM PRODUCT_PRIVS
TEST SYS DBMS_APPLICATION_INFO
17 rows selected.
SQL>
- Optionally, drop the privilege analysis policy.
EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (‘user_priv_capture’);
Larry Catt
OCP