Oracle 12C Study--12c新特性-权限分析
Oracle 12c致力于云计算服务，安全是云计算方案中最重要的一环。而对数据库访问权限的管理，也涉及到安全的各个方面。如果能对用户访问数据库的权限做到精细化管理，无论是对数据库的管理和应用都是一个很好的福音。
Oracle 12c提供了一个有力的工具DBMS_PRIVILEGE_CAPTURE，可以通过建立分析策略，对分配给用户的权限跟踪、分析、生成使用报告，从而对用户在应用中所有使用的权限和未使用的权限有一个清晰的掌控。根据管理需求，可对用户从未使用的权限进行回收，达到权限的精细化管理。
一、权限分析流程
二、案例：分析用户权限的使用情况
11:53:16 SYS@ orcl>select * from v$version;
BANNER CON_ID
--------------------------------------------------------------------Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/sql Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
Elapsed: 00:00:00.01
1、建立权限捕获策略
10:26:51 SYS@ orcl>exec sys.dbms_privilege_capture.create_capture(-
10:27:46 > name=> 'All_privs',-
10:27:51 > description=>'All privs used',-
10:27:58 > type => dbms_privilege_capture.g_database);
PL/sql procedure successfully completed.
2、执行捕获策略
10:28:07 SYS@ orcl>exec sys.dbms_privilege_capture.enable_capture( name=>'All_privs');
PL/sql procedure successfully completed.
3、建立测试用户并分配权限
10:34:50 SYS@ orcl>create user tom identified by tom;
User created.
10:35:10 SYS@ orcl>create user rose identified by rose;
User created.
10:35:26 SYS@ orcl>grant create session to tom,rose;
Grant succeeded.
10:35:41 SYS@ orcl>create role r1;
Role created.
10:39:59 SYS@ orcl>grant all on scott.emp to r1;
Grant succeeded.
10:40:12 SYS@ orcl>grant r1 to tom,rose;
Grant succeeded.
10:40:33 SYS@ orcl>alter user tom quota 10m on users;
User altered.
10:41:00 SYS@ orcl>alter user rose quota 10m on users;
User altered.
10:41:49 SYS@ orcl>grant create table,select any table to tom,rose;
Grant succeeded.
10:43:16 TOM@ orcl>r
1* create table t1 (id int)
Table created.
10:44:01 TOM@ orcl>select * from scott.emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 2250 500 30
7566 JONES MANAGER 7839 02-APR-81 3975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 2250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3850 30
10:44:14 TOM@ orcl>delete from scott.emp;
14 rows deleted.
10:44:37 TOM@ orcl>rollback;
Rollback complete.
10:45:59 SYS@ orcl>conn rose/rose
Connected.
10:46:02 ROSE@ orcl>create table t1 (id int);
Table created.
10:46:15 ROSE@ orcl>select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 1800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 2250 500 30
7566 JONES MANAGER 7839 02-APR-81 3975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 2250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3850 30
10:46:21 ROSE@ orcl>update scott.emp set sal=sal+3000;
14 rows updated.
11:00:23 ROSE@ orcl>commit;
Commit complete.
5、权限捕获完成
10:43:48 SYS@ orcl>exec sys.dbms_privilege_capture.disable_capture(name=>'All_privs');
PL/sql procedure successfully completed.
6、生成权限捕获结果
11:02:09 SYS@ orcl>exec sys.dbms_privilege_capture.generate_result(name=>'All_privs');
PL/sql procedure successfully completed.
7、查看权限捕获结果
查看用户已使用的权限：（对象权限）
11:07:19 SYS@ orcl>select capture,username,object_owner,object_name,OBJ_PRIV
2 from dba_used_objprivs
3 where username in ('TOM','ROSE')
4 and object_name not in
5* ('DBMS_APPLICATION_INFO','PRODUCT_PRIVS','DUAL') ORDER BY USERNAME
CAPTURE USERNAME OBJECT_OWN OBJECT_NAME OBJ_PRIV
-------------- ---------- ---------- ---------------
All_privs ROSE SCOTT EMP UPDATE
All_privs ROSE SCOTT EMP READ
All_privs TOM SYS TAB SELECT
All_privs TOM SCOTT EMP DELETE
系统权限：
11:08:33 SYS@ orcl>SELECT USERNAME,SYS_PRIV from dba_used_sysprivs
11:08:42 2 where username in ('TOM','ROSE');
USERNAME SYS_PRIV
---------- ----------------------------------------
ROSE CREATE SESSION
TOM SELECT ANY TABLE
TOM CREATE SESSION
ROSE CREATE TABLE
TOM CREATE TABLE
查看通过role获取的权限：
11:08:50 SYS@ orcl>col path for a32
11:09:25 SYS@ orcl>select username,obj_priv,path
11:09:37 2 from dba_used_objprivs_path
11:09:48 3 where username in ('TOM','ROSE')
11:10:00 4 and object_name not in('DBMS_APPLICATION_INFO','DUAL') ORDER BY USERNAME;
USERNAME OBJ_PRIV OBJECT_NAME PATH
---------- ---------------------------------------- ---------------
ROSE READ EMP GRANT_PATH('ROSE','R1')
ROSE UPDATE EMP GRANT_PATH('ROSE','R1')
TOM SELECT TAB GRANT_PATH('PUBLIC')
TOM DELETE EMP GRANT_PATH('TOM','R1')
12:21:25 SYS@ orcl>col role for a10
12:21:36 SYS@ orcl>col owner for a10
12:21:44 SYS@ orcl>col table_name for a10
12:21:51 SYS@ orcl>r
1 select role,owner,TABLE_NAME,PRIVILEGE from role_tab_privs
2* where role='R1'
ROLE OWNER TABLE_NAME PRIVILEGE
---------- ---------- ---------- ----------------------------------------
R1 SCOTT EMP QUERY REWRITE
R1 SCOTT EMP SELECT
R1 SCOTT EMP DEBUG
R1 SCOTT EMP DELETE
R1 SCOTT EMP FLASHBACK
R1 SCOTT EMP READ
R1 SCOTT EMP ON COMMIT REFRESH
R1 SCOTT EMP ALTER
R1 SCOTT EMP INSERT
R1 SCOTT EMP UPDATE
11:12:50 SYS@ orcl>col obj_priv for a30
11:13:08 SYS@ orcl> select username,sys_priv,path
2 from dba_unused_privs
3* where username in ('TOM','ROSE')
USERNAME SYS_PRIV OBJ_PRIV OBJECT_NAM PATH
---------- ---------- ------------------------------ ----------
ROSE FLASHBACK EMP GRANT_PATH('ROSE','R1')
ROSE DEBUG EMP GRANT_PATH('ROSE','R1')
ROSE QUERY REWRITE EMP GRANT_PATH('ROSE','R1')
ROSE ON COMMIT REFRESH EMP GRANT_PATH('ROSE','R1')
ROSE SELECT EMP GRANT_PATH('ROSE','R1')
ROSE INSERT EMP GRANT_PATH('ROSE','R1')
ROSE DELETE EMP GRANT_PATH('ROSE','R1')
ROSE ALTER EMP GRANT_PATH('ROSE','R1')
ROSE SELECT ANY GRANT_PATH('ROSE')
TABLE
TOM FLASHBACK EMP GRANT_PATH('TOM','R1')
TOM DEBUG EMP GRANT_PATH('TOM','R1')
TOM QUERY REWRITE EMP GRANT_PATH('TOM','R1')
TOM ON COMMIT REFRESH EMP GRANT_PATH('TOM','R1')
TOM READ EMP GRANT_PATH('TOM','R1')
TOM UPDATE EMP GRANT_PATH('TOM','R1')
TOM SELECT EMP GRANT_PATH('TOM','R1')
TOM INSERT EMP GRANT_PATH('TOM','R1')
TOM ALTER EMP GRANT_PATH('TOM','R1')
18 rows selected.
---通过以上视图，可以获取用户从未使用过的权限的情况，可以根据管理需要，回收用户从未使用过的权限。
11:14:58 SYS@ orcl>select name,type,enabled,roles,context from dba_priv_captures;
NAME TYPE ENABLED ROLES CONTEXT
--------------- ---------- ---------- -------------------- --------------------
All_privs DATABASE N
ORA$DEPENDENCY DATABASE N
11:15:10 SYS@ orcl>exec sys.dbms_privilege_capture.drop_capture(name=>'All_privs');
PL/sql procedure successfully completed.
11:16:22 SYS@ orcl>select username,path
11:17:16 2 from dba_unused_privs
11:17:25 3 where username in ('TOM','ROSE');
no rows selected
12:29:50 SYS@ orcl>select username,path
12:30:11 2 from dba_used_objprivs_path
12:30:11 3 where username in ('TOM','ROSE')
12:30:11 4 and object_name not in('DBMS_APPLICATION_INFO','DUAL') ORDER BY USERNAME;
no rows selected
