Privilege Analysis hands-on for least privileges principle

Preamble

If there is one subject raising quickly where you work it is, with no doubt, security ! The subject is complex in essence since there is obviously not a clear procedure to follow to be secure, would be too simple. When it comes to Oracle databases privileges there is clearly a principle that you MUST apply everywhere called least privileges principle.

Where I work it is always a challenge to apply this principle because in many cases for legacy (bad) reasons many applicative accounts have been granted many xxx ANY yyy privileges to avoid granting real required objects one by one. Not to say that some applicative accounts have even been granted with DBA role… Then it becomes really difficult to guess what the users really need and use to remove the high privileges to grant many other smaller ones. You also often do not get any support from users in such a scenario…

With Oracle 12cR1 (12.1.0.1) has been released a very interesting feature to achieve this goal of least privileges principle call Privilege Analysis (PA). The process is very simple and Oracle official documentation state it very well:

Privilege analysis enables customers to create a profile for a database user and capture the list of system and object privileges that are being used by this user. The customer can then compare the user’s list of used privileges with the list of granted privileges and reduce the list of granted privileges to match the used privileges.

Unfortunately this feature has been made available only within the Database Vault paid option. The excellent news that Oracle published on December 7th, 2018 is that they made this feature free in all Oracle Enterprise Edition version !

My test database is running on a RedHat 7.8 server and is release 19c with October 2020 Release Update (RU) 31771877 so exact database Release is 19.9.0.0.201020. In fact a pluggable database inside this container one.

Privilege analysis test case

When creating my test database I have selected the sample schemas and so have a bit of figures already created inside the database:

SQL> SET lines 200
SQL> SELECT * FROM hr.employees FETCH next 5 ROWS only;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES         24000                                      90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP           17000                       100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-01 AD_VP           17000                       100            90
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         03-JAN-06 IT_PROG          9000                       102            60
        104 Bruce                Ernst                     BERNST                    590.423.4568         21-MAY-07 IT_PROG          6000                       103            60

The EMPLOYEES table is part of the well known HR schema.

Now let’s imagine I have a HRUSER account used inside an application and by laziness I grant SELECT ANY TABLE and UPDATE ANY TABLE privileges to this account to be able to (simply) access and update HR schema tables.

This also raise some questions when you have multiple schema owner inside the same database or pluggable database as the powerful SELECT ANY TABLE privilege will allow the account to select figures inside objects of all database schemas and NOT ONLY the desired one linked to the application this account is supporting… No even mentioning the extra threat with UPDATE ANY TABLE, DELETE ANY TABLE and so on…

Simple creation:

SQL> CREATE USER hruser IDENTIFIED BY "secure_password";
 
USER created.
 
SQL> GRANT CONNECT, SELECT ANY TABLE, UPDATE ANY TABLE TO hruser;
 
GRANT succeeded.

Privilege analysis testing

Now that our fictive application is running using HRUSER account we would like to analyze really what the HRUSER account is using as privileges and see how we can reduce its rights to apply the least privileges principle.

All is done through the DBMS_PRIVILEGE_CAPTURE PL/SQL supplied package and you need the CAPTURE_ADMIN role to use it, for convenience I will use SYS account.

I start by creating a privilege analysis policy for my HRUSER account. I have chosen to create a G_CONTEXT analysis policy to focus on my HRUSER account as explained in official documentation:

G_DATABASE: Captures all privilege use in the database, except privileges used by the SYS user.
G_ROLE: Captures the use of a privilege if the privilege is part of a specified role or list of roles.
G_CONTEXT: Captures the use of a privilege if the context specified by the condition parameter evaluates to true.
G_ROLE_AND_CONTEXT: Captures the use of a privilege if the privilege is part of the specified list of roles and when the condition specified by the condition parameter is true.

SQL> EXEC dbms_privilege_capture.create_capture(name=>'hruser_prileges_analysis', description=>'Analyze HRUSER privileges usage',-
> TYPE=>DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,-
> condition=>'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''HRUSER''');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col description FOR a40
SQL> col CONTEXT FOR a50
SQL> SELECT description,TYPE,CONTEXT FROM dba_priv_captures WHERE name='hruser_prileges_analysis';
 
DESCRIPTION                              TYPE             CONTEXT
---------------------------------------- ---------------- --------------------------------------------------
ANALYZE HRUSER PRIVILEGES usage          CONTEXT          SYS_CONTEXT('USERENV', 'SESSION_USER')='HRUSER'

You must then enable the privilege analysis policy with below command. Specifying a run_name is interesting to generate report and compare privilege analysis policy result over multiple capture periods:

SQL> EXEC dbms_privilege_capture.enable_capture(name=>'hruser_prileges_analysis', run_name=>'hruser_18_dec_2020');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col run_name FOR a20
SQL> SELECT description,TYPE,CONTEXT,run_name FROM dba_priv_captures WHERE name='hruser_prileges_analysis';
 
DESCRIPTION                              TYPE             CONTEXT                                            RUN_NAME
---------------------------------------- ---------------- -------------------------------------------------- --------------------
ANALYZE HRUSER PRIVILEGES usage          CONTEXT          SYS_CONTEXT('USERENV', 'SESSION_USER')='HRUSER'    HRUSER_18_DEC_2020

Then with my previously created HRUSER I simulate what would be done through a classical application. I select salary and commission percentage of a sales employee and because he has performed very well over the past year I increase his commission:

SQL> SELECT first_name,last_name,salary,commission_pct FROM hr.employees WHERE employee_id=165;
 
FIRST_NAME           LAST_NAME                     SALARY COMMISSION_PCT
-------------------- ------------------------- ---------- --------------
David                Lee                             6800             .1
 
SQL> UPDATE hr.employees SET commission_pct=0.2 WHERE employee_id=165;
 
1 ROW updated.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT first_name,last_name,salary,commission_pct FROM hr.employees WHERE employee_id=165;
 
FIRST_NAME           LAST_NAME                     SALARY COMMISSION_PCT
-------------------- ------------------------- ---------- --------------
David                Lee                             6800             .2

The duration of the capture activity might be quite complex to determine. What if you have weekly jobs, monthly jobs or even yearly jobs. I tend to say that it must run for at least a week but the good duration is totally up to you and your environment…

Privilege analysis reports and conclusion

Before generating a privilege anaylsys policy report you must disable it:

SQL> EXEC dbms_privilege_capture.disable_capture(name=> 'hruser_prileges_analysis');
 
PL/SQL PROCEDURE successfully completed.

I generate a privilege analysis report using the run name I have specified when enabling it:

SQL> EXEC dbms_privilege_capture.generate_result(name=>'hruser_prileges_analysis', run_name=>'HRUSER_18_DEC_2020', dependency=>TRUE);
 
PL/SQL PROCEDURE successfully completed.

To be honest I am a little bit disappointed here as I expected the procedure like AWR one to generate an html nice and usable result. But in fact the generate result procedure simply fill the DBA_USED_* data dictionary privilege analysis views and you have then to fetch them to get your result. The complete list of this views is available in official documentation.

SQL> col username format a10
SQL> col sys_priv format a16
SQL> col object_owner format a13
SQL> col object_name format a23
SQL> SELECT username,sys_priv, object_owner, object_name FROM dba_used_privs WHERE capture='hruser_prileges_analysis' AND run_name='HRUSER_18_DEC_2020';
 
USERNAME   SYS_PRIV         OBJECT_OWNER  OBJECT_NAME
---------- ---------------- ------------- -----------------------
HRUSER                      SYS           DBMS_APPLICATION_INFO
HRUSER     UPDATE ANY TABLE HR            EMPLOYEES
HRUSER     CREATE SESSION
HRUSER     SELECT ANY TABLE HR            EMPLOYEES
HRUSER                      SYS           DUAL

In below output we see that HRUSER has used UPDATE ANY TABLE and SELECT ANY TABLE to respectively update and read HR.EMPLOYEES table. So a direct grant and update on this table would replace the two high ANY privileges.

Let’s now generate system privileges HRUSER has used:

SQL> SELECT username, sys_priv FROM dba_used_sysprivs WHERE capture='hruser_prileges_analysis' AND run_name='HRUSER_18_DEC_2020';
 
USERNAME   SYS_PRIV
---------- ----------------
HRUSER     CREATE SESSION
HRUSER     SELECT ANY TABLE
HRUSER     UPDATE ANY TABLE

In my example I have nothing related to objects privileges but if you need you can use DBA_USED_OBJPRIVS(_PATH) and DBA_UNUSED_OBJPRIVS(_PATH) views.

In this final example we can also see that from CONNECT role HRUSER has not used the SET CONTAINER privilege so we could also replace CONNECT role by CREATE SESSION privilege.

SQL> col PATH FOR a40
SQL> SELECT sys_priv, PATH FROM dba_used_sysprivs_path WHERE capture='hruser_prileges_analysis'  AND run_name='HRUSER_18_DEC_2020';
 
SYS_PRIV         PATH
---------------- ----------------------------------------
UPDATE ANY TABLE GRANT_PATH('HRUSER')
CREATE SESSION   GRANT_PATH('HRUSER', 'CONNECT')
SELECT ANY TABLE GRANT_PATH('HRUSER')
 
SQL> SELECT sys_priv, PATH FROM dba_unused_sysprivs_path WHERE capture='hruser_prileges_analysis' AND run_name='HRUSER_18_DEC_2020' AND username='HRUSER';
 
SYS_PRIV         PATH
---------------- ----------------------------------------
SET CONTAINER    GRANT_PATH('HRUSER', 'CONNECT')

All those SQL*Plus queries are cool but at few years light from what you can get with Cloud Control (Security / Privilege Analysis menu). The version I have is Cloud Control 13c release 2 (13.2):

Global view (you even have start and end time capture that do not appear easily in PA views):