12c New features changed from licensing option to free to use!
When Oracle 12c was released one of the new features was privilege analysis, this is a great features, but initially required additional licensing. But recently (November 2018) this license is not longer required. Which is very nice from Oracle.
What is privilege analysis?
Privilege analysis performs a dynamic (not static) analysis of privileges and roles that a user account or database uses over time.
Because it is a dynamic analysis, it captures real privileges and roles that were actually used.
Privilege analysis captures privileges used by database users and applications at runtime. You then can revoke unused grants and other changes to better reflect the access a user requires. This simplifies the work required to implement least privilege practice.
Running inside the Oracle Database kernel, privilege analysis helps reduce the attack surface of applications and increase operational security by identifying used and unused privileges. Privilege analysis can be used after you install Oracle Database Release 12c without any additional configuration steps.
The above is coming from the Oracle manual.
Their are several good blog posts explaining how to use privilege analysis, so I don’t want to spend explaining what others already did very well.
Keep in-mind that you can also use cloud control to perform this action:
So take advances of this license free security feature.
Below a scripts which I use to demo this feature, simple but explains what is done.
col inst_id for 9999 col pdb_name for a20 col file_name for a50 col owner for a15 col object_name for a30 accept host prompt "Enter hostname :" connect sys@"&&host/&servicename" as sysdba set linesize 80 set echo on echo "Privilege analysis performs a dynamic analysis of privileges and roles that a user account or database uses over time." echo "This demo only works in a PDB or NON-CDB BEGIN DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( name => 'dba_role' , description => 'Check the DBA role' , type=> 2 , roles=> SYS.ROLE_NAME_LIST('DBA')); END; / BEGIN DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( name => 'check_user' , description => 'Check privs for this user', type => dbms_privilege_capture.g_context, condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''BCB'''); END; / -- also possible to use module ect: 'SYS_CONTEXT(''USERENV'', ''MODULE'')=''sqlplus''' pause Display the create capture policy and check if enabled col name for a20 col enabled for a10 select name,type,enabled from dba_priv_captures; prompt Enable the capture policy, notice the error, database and 1 other policy can be enabled at one time pause Notice able capture is the same as start capture in Cloud control execute DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE( name => 'check_user',run_name =>'myrun1') select name,type,enabled from dba_priv_captures; pause enable also the other policy execute DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE( name => 'dba_role',run_name =>'myrun2'); create user bcb identified by bcb; grant create session,dba to bcb; prompt Now do something in the container as bcb user like create table myemp (a varchar2(10)) -- (user sys will not be analyzed) pause create a table, drop a table for example, perform a query pause Disable the privilege capture run execute DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE( name => 'check_user'); select name,type,enabled from dba_priv_captures; pause Now push the result to the data dictionary views execute DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT( name => 'check_user'); pause Which views are available to check? col object_name for a30 col object_type for a12 select object_name,object_type from dba_objects where object_name like 'DBA_USED_%' or object_name like 'DBA_UNUSED%'; pause Now we can start to analyse the result, in Cloud control the report functionality is nice. pause Which system privileges where used during the capture periode? COLUMN username FORMAT A20 COLUMN sys_priv FORMAT A20 SELECT username, sys_priv FROM dba_used_sysprivs WHERE capture='check_user' ORDER BY username, sys_priv; pause How where the privileges granted? COLUMN username FORMAT A20 COLUMN used_role FORMAT A30 COLUMN sys_priv FORMAT A20 COLUMN path FORMAT A50 SET LINESIZE 200 SELECT username, sys_priv, used_role, path FROM dba_used_sysprivs_path WHERE capture='check_user' ORDER BY username, sys_priv; pause Which system privileges where NOT used during the capture periode? COLUMN username FORMAT A20 COLUMN sys_priv FORMAT A20 SELECT sys_priv FROM dba_unused_sysprivs WHERE capture='check_user' and username='BCB' ORDER BY sys_priv; pause Create the statements to revoke all the privileges which are not required --Generate statements to revoke permissions select 'revoke '||OBJ_PRIV||' on '||OBJECT_OWNER||'.'|| OBJECT_NAME||' from ' ||USERNAME||';' from dba_unused_privs where username='BCB'; pause Cleanup/remove the user and privilege capture policy drop user bcb; execute DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE( name => 'dba_role'); execute DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE( name => 'check_user'); select name,type,enabled from dba_priv_captures; set echo off -- end of demo