Privilege Usage Feature

Or theory of “minimum permission”. Basically it is about giving the minimum level of privileges to a user to develop their work, giving more than what they actually use does not contribute anything in their day to day and from the point of view of security is a high risk. From a simple error by the user (to delete a table) until the user is compromised by an attack and used for other purposes than the original.

In version 12 Oracle has introduced a new feature to audit the permissions that are being used “Privilege Usage”. This trace can be in the following types of level;

 
At the database level
At the role level
At the user level

Let’s see the procedures that this package has (DBMS_PRIVILEGE_CAPTURE)

 
SQL> desc DBMS_PRIVILEGE_CAPTURE
PROCEDURE CREATE_CAPTURE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 TYPE                           NUMBER                  IN     DEFAULT
 ROLES                          ROLE_NAME_LIST          IN     DEFAULT
 CONDITION                      VARCHAR2                IN     DEFAULT
PROCEDURE DISABLE_CAPTURE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
PROCEDURE DROP_CAPTURE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
PROCEDURE ENABLE_CAPTURE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
PROCEDURE GENERATE_RESULT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN

The basic steps are the following;

  • Create a trace
  • Activate trace
  • user work as usual
  • Stop trace
  • Generate report

Before to start create a user for this sample;

 
SQL>
SQL> -- Create a sample user and grant dba role
SQL> create user test identified by test1234567890;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL>

Create the trace. Capture for user TEST;

 
SQL>
SQL> -- Create capture for user test  --------------------------
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.create_capture(
  3  name        => 'capture_for_user_test',
  4  type        => DBMS_PRIVILEGE_CAPTURE.g_context,
  5  condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST'''
  6  );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>

Now activate the trace

 
SQL>
SQL> -- Activate the trace ------
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.enable_capture(
  3  name        => 'capture_for_user_test'
  4  );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>

For other session log on like test user and execute a pair of commands;

 
$ 
$ sqlplus test/test1234567890

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 27 14:31:59 2018

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show user
USER is "TEST"
SQL> create table trial as select * from dba_objects;

Table created.

SQL> select count (*) from dba_users;

  COUNT(*)
----------
      1329

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
$ 

Stop the trace;

 
SQL> -- Stop the trace -----
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.disable_capture(
  3  name        => 'capture_for_user_test'
  4  );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>

Generate a new report;

 
SQL> -- Generate the report by insert data into the DBA views associated -----
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.generate_result(
  3  name        => 'capture_for_user_test'
  4  );
  5  END;
  6  /

PL/SQL procedure successfully completed.

Select the data from DBA views

 
SQL> COLUMN name FORMAT a25
SQL> COLUMN roles FORMAT a20
SQL> COLUMN context FORMAT a30
SQL> select name,type,roles,context FROM dba_priv_captures;

NAME                      TYPE             ROLES                CONTEXT
------------------------- ---------------- -------------------- ------------------------------
capture_for_user_test     CONTEXT                               SYS_CONTEXT('USERENV', 'SESSIO
                                                                N_USER') = 'TEST'

SQL> select username, sys_priv from dba_used_sysprivs where capture = 'capture_for_user_test';

USERNAME   SYS_PRIV
---------- ----------------------------------------
TEST       CREATE TABLE
TEST       UNLIMITED TABLESPACE
TEST       CREATE SESSION

SQL>

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s