Oracle Database 12c: Privilege Analysis in Oracle Database 12c

Oracle security is based on the principle of granting only the minimum privileges required in order to perform a function. However access to a database can be gained by way of grant or by way of a role, and the chain by which access was gained can become complex to analyze. Third party database applications may grant more privileges than are necessary to the accounts which access the database, causing security vulnerabilities.

For these needs, Oracle Database 12c enables analysis of privilege and role usage, and reporting on the analysis, in order to see if more privileges have been granted than is required, so that they may be revoked, without affecting the ability of the applications to continue functioning.

Privilege analysis consists of these steps

As a user who has privilege analysis rights i.e. the CAPTURE_ADMIN role, create a privilege capture policy and enable it.

The database users continue accessing the database, while the privilege capture policy records usage of the privileges in database dictionary views for a representative observation period.

The capture administrator can query the views to determine whether the granted privileges are being used.

If there are any privileges and roles which are not being used, they may be revoked without impact to the applications.

The benefit of privilege capture is that it helps the dba to track which privileges are not required and revoke them, thus securing the database.

For example, if there is a role called HR_ADMIN which has been granted 2 privileges.

CDB name	PDB name	Role name	Grants
CDB1	HRPDB	HR_ADMIN	Create any table Select on hr.employees

This role will be granted to a new user named SCOTT, whose usage will be monitored by creating a privilege analysis policy.

First creating the role HR_ADMIN in the HRPDB database.

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 12 11:55:44 2014

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> create role hr_mgr ;

Role created.

SQL> grant select on hr.employees to hr_mgr;

Grant succeeded.

SQL> grant create any table to hr_mgr;

Grant succeeded.

Next we will create the user named SCOTT.

SQL> create user scott identified by tiger default tablespace example;

User created.

SQL> grant create session to scott;

Grant succeeded.

SQL> alter user scott quota 1m on example;

User altered.

SQL> grant create table to scott ;

User altered.

SQL>

Granting the role HR_MGR to the user SCOTT.

SQL> grant hr_mgr to scott;

Grant succeeded.

SQL>

We have set up the user and his role at this point. Now to set up the capture.

1. The CAPTURE_ADMIN privilege is necessary to administer privilege capture. This role is available to the user SYS. The new package DBMS_PRIVILEGE_CAPTURE enables privilege capture administration. It can create privilege captures for the entire database, for a specific role or a combination of role and session context. We want to create a role capture. The following pl/sql block creates the capture named CAPTURE1, which will capture usage of the role named HR_MGR.

SQL> -- create role capture policy

begin

dbms_privilege_capture.create_capture(name => 'CAPTURE1',

description => 'capture demo',

type => dbms_privilege_capture.g_role,

roles => role_name_list('HR_MGR')

);

end;

SQL> 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

2. Enable the capture, to start the monitoring.

SQL> -- enable the capture

begin

dbms_privilege_capture.enable_capture(name => 'CAPTURE1');

end;

SQL> 2 3 4

PL/SQL procedure successfully completed.

3. Disconnect and exit from this SQlPlus session. Otherwise the privilege usage views are not properly updated.

4. Connect as the SCOTT user and exercise the privilege which is being monitored for usage. For example use the SELECT ANY TABLE to select data from hr.employees. And create a table in the local schema of scott.

[oracle@laboms ~]$ sqlplus scott/tiger@localhost:1521/hrpdb

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 13 08:26:40 2014

Last Successful login time: Sat Jul 12 2014 12:24:36 +08:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> select * from hr.employees fetch first 10 rows only;

ID NAME START_DAT EMPNO

---------- -------------------- --------- ----------

1 EMP_ 00000001 06-OCT-03 14211

2 EMP_ 00000002 05-APR-04 14003

3 EMP_ 00000003 17-NOV-02 15669

4 EMP_ 00000004 16-JUN-00 8135

5 EMP_ 00000005 02-OCT-03 5783

6 EMP_ 00000006 20-MAR-05 8154

7 EMP_ 00000007 06-MAY-02 1676

8 EMP_ 00000008 11-DEC-01 15087

9 EMP_ 00000009 16-MAY-04 7462

10 EMP_ 00000010 25-MAR-00 11849

10 rows selected.

SQL> create table test as select * from hr.employees;

Table created.

SQL>

5. Disconnect and exit from this SQlPlus session. Otherwise the privilege usage views are not properly updated.

6. Connect as SYSBA and execute these procedures to disable and DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE and DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedures.

[oracle@laboms ~]$ sqlplus sys/oracle@localhost:1521/hrpdb as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 13 08:33:10 2014

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

SQL> begin

dbms_privilege_capture.disable_capture(name => 'CAPTURE1');

dbms_privilege_capture.generate_result(name => 'CAPTURE1');

end;

/ 2 3 4 5

PL/SQL procedure successfully completed.

7. After this the privilege usage profile of the role HR_MGR can be queried from the data dictionary views like these.

View	Description
DBA_USED_OBJPRIVS	Details of object privileges used during the capture period.
DBA_UNUSED_SYSPRIVS	Details of system privileges not used during the captured.

8. Below are the query results from the DBA_USED_OBJPRIVS view.

This views lists the object privileges used during the capture period.

SQL> column capture format a10

column username format a10

column used_role format a10

column obj_priv format a10

column object_owner format a15

column object_name format a15

SQL> select capture,username,used_role,obj_priv,object_owner,object_name from dba_used_objprivs;

CAPTURE USERNAME USED_ROLE OBJ_PRIV OBJECT_OWNER OBJECT_NAME

---------- ---------- ---------- ---------- --------------- ---------------

CAPTURE1 SCOTT HR_MGR SELECT HR EMPLOYEES

9. But more relevant to security administration are the granted access to the databases but NOT used during the representative capture period. That information is available from the dba_unused_*privs view.

SQL> column capture format a10

column username format a10

column used_role format a10

column obj_priv format a10

column object_owner format a15

column object_name format a15

column rolename format a20

column sys_priv format a20

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL>

SQL> select capture,rolename,sys_priv from dba_unused_sysprivs

2 /

CAPTURE ROLENAME SYS_PRIV

---------- -------------------- --------------------

CAPTURE1 HR_MGR CREATE ANY TABLE

The user SCOTT in this example did NOT use the CREATE ANY TABLE privilege, and this is exposed in the dba_unused_sysprivs view.

The privilege analysis feature enables the administrator to determine the CREATE ANY TABLE privilege is unused and can be revoked from the HR_MGR role.

Oracle Database 12c

Sunday, 13 July 2014

Privilege Analysis in Oracle Database 12c

No comments:

Post a Comment