The Oracle
Database 12c privilege analysis enables the security administrator to analyze
the path by which access was gained to a table or object of interest. This is
called the GRANT PATH, which can be difficult to analyze in complex cases
without privilege analysis.
Consider a
case where a user hr has indirectly gained access to the SCOTT.EMPLOYEES table.
This user gained access when he received a grant to a role named HR_USER. And
the HR_USER in turn role has received a grant of the role named HR_ADMIN. The
HR_ADMIN has directly received a grant of SELECT on SCOTT.EMPLOYEES.
The 'grant
path' is HR (user) --> HR_USER (role) --> HR_ADMIN (role)
username :
hr
Role granted to hr : hr_user
Role granted to hr_user : hr_admin
Role granted to hr_admin : select
on scott.employees
The
information about this chain of grants is available in a capture, once
generated, via the DBA_USED_OBJPRIVS_PATH/DBA_USED_SYSPRIVS_PATH view. The
example below shows an example of retrieving the GRANT PATH .
Setting up
the roles, creating the HR user and GRANTing the user the roles.
SQL> create role hr_admin;
Role created.
SQL> grant select on scott.employees to hr_admin;
Grant succeeded.
SQL> create role hr_user;
Role created.
SQL> grant hr_admin to hr_user;
Grant succeeded.
SQL> create user hr identified by oracle
profile default
default tablespace users quota unlimited on users;
2
User created.
SQL> grant create
session to hr;
Grant succeeded.
SQL> grant hr_user
to hr;
Grant succeeded.
Setting up the privilege capture…
The capture will be a role
capture type and capture the usage of the role HR_USER.
SQL> begin
dbms_privilege_capture.create_capture(name
=> 'CAPTURE_TRIAL',
description => 'TRIAL',
type =>
dbms_privilege_capture.g_role,
roles =>
role_name_list('HR_USER'));
end;
2 3
4 5 6
7 /
PL/SQL procedure successfully completed.
Enabling the
capture…
SQL> begin
dbms_privilege_capture.enable_capture(name => 'CAPTURE_TRIAL');
end;
2 3
4 /
PL/SQL procedure successfully completed.
Exercising the privilege… as the new
HR user…
SQL> conn hr/oracle@localhost:1521/noncdb1
Connected.
SQL> select * from scott.employees
2 fetch first 5 rows only;
EMP_ID NAME JOIN_DATE MGR
DEPT_ID
---------- -------------------- --------- ---------- ----------
1 emp0000001 28-NOV-14 123 100
2 emp0000002 28-NOV-14 123 100
3 emp0000003 28-NOV-14 123 100
4 emp0000004 28-NOV-14 123 100
5 emp0000005 28-NOV-14 123 100
SQL>
Stopping the privilege capture and
collecting its results…
SQL> conn / as sysdba
Connected.
SQL>
SQL> begin
2 dbms_privilege_capture.disable_capture('CAPTURE_TRIAL');
3
dbms_privilege_capture.generate_result('CAPTURE_TRIAL');
4
end;
5
/
PL/SQL procedure successfully completed.
Querying the capture results…
The dba_used_objprivs_path will have
the grant path details for object privilege usage.
SQL> ed
Wrote file afiedt.buf
1* select capture,
used_role, obj_priv, object_owner, object_NAME,
path from dba_used_objprivs_path
SQL> /
CAPTURE
USED_ROLE OBJ_PRIV OBJ_OWNER
OBJECT_NAME PATH
--------------- ---------- ---------- ----------
--------------- ----------------------------------------
CAPTURE_TRIAL
HR_ADMIN SELECT SCOTT
EMPLOYEES GRANT_PATH('HR',
'HR_USER', 'HR_ADMIN')
The PATH
column is actually a VARRAY collection and can be extracted using pl/sql script
as shown
SQL> ed
Wrote file afiedt.buf
1 declare
2 v_grant_path GRANT_PATH;
3 v_index varchar(128);
4 begin
5 select path into v_grant_path from
dba_used_objprivs_path where sequence=1;
6 v_index :=v_grant_path.first();
7 while v_index is not null
8 loop
9 dbms_output.put_line(v_grant_path(v_index));
10 v_index :=v_grant_path.next(v_index);
11 end loop;
12* end;
SQL> /
HR
HR_USER
HR_ADMIN
The grant
path information allows the administrator to trace the trail of grants by which
a user received access to a table of interest. In more complex, real-world cases, this would have been
difficult to analyze without the aid of the privilege capture.
No comments:
Post a Comment