Loading....

In many databases we find that over the course of time certain users particularly application owner schemas and developer user accounts have been granted excessive privileges – more than what they need to do their job as developers or required for the application to perform normally.

Excessive privileges violate the basic security principle of least privilege.

In Oracle 12c now we have a package called DBMS_PRIVLEGE_CAPTURE through which we can identify unnecessary object and system privileges which have been granted and revoke privileges which have been granted but which have not yet been used.

The privilege analysis can be at the entire database level, or based on a particular role or context-specific – like a particular user in the database.

These are the main steps involved:

1) Create the Database, Role or Context privilege analysis policy via DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
2) Start the analysis of used privileges via DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE
3)Stop the analysis when required via DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE
4) Generate the report via DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT
5) Examine the views like DBA_USED_SYSPRIVS, DBA_USED_OBJPRIVS,DBA_USED_PRIVS, DBA_UNUSED_PRIVS etc

In this example below we do context-based analysis – the role ‘DBA’ and the user ‘SH’.

SQL> alter session set container=sales;

Session altered.

SQL>  grant dba to sh;

Grant succeeded.


SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(-
> name => 'AUDIT_DBA_SH',-
>  type => dbms_privilege_capture.g_role_and_context,-
> roles => role_name_list ('DBA'),-
> condition => 'SYS_CONTEXT (''USERENV'',''SESSION_USER'')=''SH''');

PL/SQL procedure successfully completed.


SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(-
> name => 'AUDIT_DBA_SH');

PL/SQL procedure successfully completed.

SQL> conn sh/sh@sales
Connected.


SQL> alter user hr identified by hr;

User altered.

SQL> create table myobjects as select * from all_objects;
create table myobjects as select * from all_objects
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> drop table myobjects;

Table dropped.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter tablespace users online;

Tablespace altered.



SQL> conn / as sysdba
Connected.


SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(-
>  name => 'AUDIT_DBA_SH');

PL/SQL procedure successfully completed.

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(-
>  name => 'AUDIT_DBA_SH');

PL/SQL procedure successfully completed.


SQL> select name,type,enabled,roles,context
  2  from dba_priv_captures;

NAME           TYPE             E ROLES           CONTEXT
-------------- ---------------- - --------------- ------------------------------------------------------------
AUDIT_DBA_SH   ROLE_AND_CONTEXT N ROLE_ID_LIST(4) SYS_CONTEXT ('USERENV','SESSION_USER')='SH'


SQL> select username,sys_priv from dba_used_sysprivs;


USERNAME             SYS_PRIV
-------------------- ----------------------------------------
SH                   CREATE SESSION
SH                   ALTER USER
SH                   CREATE TABLE
SH                   ALTER TABLESPACE
Please follow and like us:

Last Update: June 23, 2020  

February 26, 2015 222 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?