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.
Blogs:
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.
Capture Privilege Usage (DBMS_PRIVILEGE_CAPTURE) in Oracle Database 12c Release 1 (12.1)
Role and Privileges Analysis Oracle Database 12C release 1 (12.1)
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