Table of contents
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):
Used privileges:
Unused privileges (I had to filter on my HRUSER account in Cloud Control):
References
- Privilege Analysis – Included with Oracle Database Enterprise Edition
- Privilege Analysis
- Performing Privilege Analysis to Find Privilege Use
- DBMS_PRIVILEGE_CAPTURE
