Oracle 12C Study--12c新特性-权限分析

来源：互联网发布：linux循环新建文件命令编辑：程序博客网时间：2024/05/17 10:27

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.

4、测试用户登录并访问数据库

Tom 用户登录数据库：

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.

Rose用户登录数据库：

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,object_name,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','PRODUCT_PRIVS','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,obj_priv,object_name,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.

---通过以上视图，可以获取用户从未使用过的权限的情况，可以根据管理需要，回收用户从未使用过的权限。

8、删除捕获策略，删除前需要禁用策略

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,sys_priv,obj_priv,object_name,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,obj_priv,object_name,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','PRODUCT_PRIVS','DUAL') ORDER BY USERNAME;

no rows selected

0 0