Oracle中与权限相关的数据字典

最新推荐文章于 2024-07-29 12:32:44 发布

CodingSuen

最新推荐文章于 2024-07-29 12:32:44 发布

阅读量1.9k

点赞数

分类专栏： Oracle 文章标签： Oracle

本文链接：https://blog.csdn.net/XavierSuen/article/details/82945334

版权

Oracle 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

在平时的运维工作中，与权限有关的视图会经常用到。为不同的用户分配相应的权限，既能提高工作效率又能保护数据库的安全性。下面我来总结下经常用的几个权限视图。

1.DBA_XXX_PRIVS

DBA_XXX_PRIVS包括所有的系统权限、角色和对象。可以使用sys用户来查询我们想要查询的目标。
DBA_SYS_PRIVS

DBA_SYS_PRIVS describes system privileges granted to users and roles.

Column	Datatype	NULL	Description
`GRANTEE`	`VARCHAR2(30)`	`NOT NULL`	Grantee name, user, or role receiving the grant
`PRIVILEGE`	`VARCHAR2(40)`	`NOT NULL`	System privilege
`ADMIN_OPTION`	`VARCHAR2(3)`		Indicates whether the grant was with the `ADMIN` option (`YES`) or not (`NO`)

该视图包括了赋予所有用户和角色的系统权限。

ADMIN_OPTION为YES表示被赋予权限的用户可以将该权限赋予其他用户；NO则表示不可以赋予其他用户，也就是不能够传递权限。

例如，SCOTT用户的系统权限。

SQL> select * from dba_sys_privs where grantee = 'SCOTT';

GRANTEE 	     PRIVILEGE		  ADMIN_OPTION
-------------------- -------------------- --------------------
SCOTT		     UNLIMITED TABLESPACE NO

常用的CONNECT角色拥有的系统权限。

SQL> select * from dba_sys_privs where grantee = 'CONNECT';

GRANTEE 	     PRIVILEGE		  ADMIN_OPTION
-------------------- -------------------- --------------------
CONNECT 	     CREATE SESSION	  NO

DBA_ROLE_PRIVS

DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.

Column	Datatype	NULL	Description
`GRANTEE`	`VARCHAR2(30)`		Name of the user or role receiving the grant
`GRANTED_ROLE`	`VARCHAR2(30)`	`NOT NULL`	Granted role name
`ADMIN_OPTION`	`VARCHAR2(3)`		Indicates whether the grant was with the `ADMIN OPTION` (`YES`) or not (`NO`)
`DEFAULT_ROLE`	`VARCHAR2(3)`		Indicates whether the role is designated as a `DEFAULT ROLE` for the user (`YES`) or not (`NO`)

该视图包含了赋予所有用户和角色的角色。

例如，SCOTT用户拥有的角色。

SQL> select * from dba_role_privs where grantee = 'SCOTT';

GRANTEE 	     GRANTED_ROLE	  ADMIN_OPTION	       DEFAULT_ROLE
-------------------- -------------------- -------------------- --------------------
SCOTT		     RESOURCE		  NO		       YES
SCOTT		     CONNECT		  NO		       YES

DBA_TAB_PRIVS

DBA_TAB_PRIVS describes all object grants in the database.

Column	Datatype	NULL	Description
`GRANTEE`	`VARCHAR2(30)`	`NOT NULL`	Name of the user or role to whom access was granted
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the object
`TABLE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the object. The object can be any object, including tables, packages, indexes, sequences, and so on.
`GRANTOR`	`VARCHAR2(30)`	`NOT NULL`	Name of the user who performed the grant
`PRIVILEGE`	`VARCHAR2(40)`	`NOT NULL`	Privilege on the object
`GRANTABLE`	`VARCHAR2(3)`		Indicates whether the privilege was granted with the `GRANT OPTION` (`YES`) or not (`NO`)
`HIERARCHY`	`VARCHAR2(3)`		Indicates whether the privilege was granted with the `HIERARCHY OPTION` (`YES`) or not (`NO`)

包含了所有赋予的对象权限。

GRANTABLE和ADMIN_OPTION的作用一样，表示该用户可以将自己拥有的权限授权给别人。

以下就是对象权限的内容。

例如，赋予SCOTT用户的对象权限。

SQL> select * from dba_tab_privs where grantee = 'SCOTT';

GRANTEE 	     OWNER		  TABLE_NAME	       GRANTOR		    PRIVILEGE		 GRANTABLE	      HIERARCHY
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
SCOTT		     SCOTT		  EMP		       SCOTT		    UPDATE		 NO	      NO

2.USER_XXX_PRIVS

USER_XXX_PRIVS包含当前用户的系统权限、对象权限和角色。以下用Oracle默认的普通用户举例子。
USER_SYS_PRIVS

USER_SYS_PRIVS describes system privileges granted to the current user.

Column	Datatype	NULL	Description
`USERNAME`	`VARCHAR2(30)`		Name of the user, or `PUBLIC`
`PRIVILEGE`	`VARCHAR2(40)`	`NOT NULL`	System privilege
`ADMIN_OPTION`	`VARCHAR2(3)`		Indicates whether the grant was with the `ADMIN` option (`YES`) or not (`NO`)

该视图包含了当前用户被赋予的系统权限。

SQL> select * from user_sys_privs;

USERNAME	     PRIVILEGE		  ADMIN_OPT
-------------------- -------------------- ---------
SCOTT		     UNLIMITED TABLESPACE NO

USER_ROLE_PRIVS

USER_ROLE_PRIVS describes the roles granted to the current user.

Column	Datatype	Description
`USERNAME`	`VARCHAR2(30)`	Name of the user, or `PUBLIC`
`GRANTED_ROLE`	`VARCHAR2(30)`	Name of the role granted to the user
`ADMIN_OPTION`	`VARCHAR2(3)`	Indicates whether the grant was with the `ADMIN OPTION` (`YES`) or not (`NO`)
`DEFAULT_ROLE`	`VARCHAR2(3)`	Indicates whether the role is designated as a `DEFAULT ROLE` for the user (`YES`) or not (`NO`)
`OS_GRANTED`	`VARCHAR2(3)`	Indicates whether the role was granted by the operating system (`YES`) or not (`NO`); occurs if the `OS_ROLES` initialization parameter is `true`

包含了当前用户被赋予的角色。

SQL> select * from user_role_privs;

USERNAME	     GRANTED_ROLE										ADMIN_OPT DEFAULT_R OS_GRANTE
-------------------- ------------------------------------------------------------------------------------------ --------- --------- ---------
SCOTT		     CONNECT											NO	  YES	    NO
SCOTT		     RESOURCE											NO	  YES	    NO

USER_TAB_PRIVS

USER_TAB_PRIVS describes the object grants for which the current user is the object owner, grantor, or grantee. Its columns are the same as those in DBA_TAB_PRIVS.

Column	Datatype	NULL	Description
`GRANTEE`	`VARCHAR2(30)`	`NOT NULL`	Name of the user or role to whom access was granted
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the object
`TABLE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the object. The object can be any object, including tables, packages, indexes, sequences, and so on.
`GRANTOR`	`VARCHAR2(30)`	`NOT NULL`	Name of the user who performed the grant
`PRIVILEGE`	`VARCHAR2(40)`	`NOT NULL`	Privilege on the object
`GRANTABLE`	`VARCHAR2(3)`		Indicates whether the privilege was granted with the `GRANT OPTION` (`YES`) or not (`NO`)
`HIERARCHY`	`VARCHAR2(3)`		Indicates whether the privilege was granted with the `HIERARCHY OPTION` (`YES`) or not (`NO`)

包含了用户被赋予的对象权限。

SQL> select * from user_tab_privs;

GRANTEE    OWNER		TABLE_NAME	     GRANTOR		  PRIVILEGE	       GRANTABLE	    HIERARCHY
---------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
SCOTT	   SCOTT		EMP		     SCOTT		  UPDATE	       NO		    NO

3.SESSION_PRIVS

SESSION_PRIVS describes the privileges that are currently available to the user.

Column	Datatype	NULL	Description
`PRIVILEGE`	`VARCHAR2(40)`	`NOT NULL`	Name of the privilege

该视图包含了当前用户的所有权限。

SQL> select * from session_privs;

PRIVILEGE
--------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

4.ROLE_XXX_PRIVS

ROLE_XXX_PRIVS包含角色用有的系统权限、对象权限和角色。
ROLE_SYS_PRIVS

ROLE_SYS_PRIVS describes system privileges granted to roles. Information is provided only about roles to which the user has access.

Column	Datatype	NULL	Description
`ROLE`	`VARCHAR2(30)`	`NOT NULL`	Name of the role
`PRIVILEGE`	`VARCHAR2(40)`	`NOT NULL`	System privilege granted to the role
`ADMIN_OPTION`	`VARCHAR2(3)`		Indicates whether the grant was with the `ADMIN` option (`YES`) or not (`NO`)

包含了角色拥有的系统权限。

例如，角色CONNECT就拥有CREATE SESSION的系统权限。

SQL> select * from role_sys_privs where role = 'CONNECT';

ROLE	   PRIVILEGE		ADMIN_OPTION
---------- -------------------- --------------------
CONNECT    CREATE SESSION	NO

ROLE_ROLE_PRIVS

ROLE_ROLE_PRIVS describes the roles granted to other roles. Information is provided only about roles to which the user has access.

Column	Datatype	NULL	Description
`ROLE`	`VARCHAR2(30)`	`NOT NULL`	Name of the role
`GRANTED_ROLE`	`VARCHAR2(30)`	`NOT NULL`	Role that was granted
`ADMIN_OPTION`	`VARCHAR2(3)`		Signifies that the role was granted with `ADMIN` option

包含了角色被赋予的角色。

例如，角色DBA被赋予的有哪些角色。

SQL> select * from role_role_privs where role = 'DBA';

ROLE	   GRANTED_ROLE 	ADMIN_OPTION
---------- -------------------- --------------------
DBA	   OLAP_DBA		NO
DBA	   SCHEDULER_ADMIN	YES
DBA	   DATAPUMP_IMP_FULL_DA NO
	   TABASE

DBA	   OLAP_XS_ADMIN	NO
DBA	   DELETE_CATALOG_ROLE	YES
DBA	   EXECUTE_CATALOG_ROLE YES
DBA	   WM_ADMIN_ROLE	NO
DBA	   EXP_FULL_DATABASE	NO
DBA	   SELECT_CATALOG_ROLE	YES
DBA	   JAVA_DEPLOY		NO
DBA	   GATHER_SYSTEM_STATIS NO
	   TICS

DBA	   XDB_SET_INVOKER	NO
DBA	   JAVA_ADMIN		NO
DBA	   DATAPUMP_EXP_FULL_DA NO
	   TABASE

DBA	   XDBADMIN		NO
DBA	   IMP_FULL_DATABASE	NO

ROLE_TAB_PRIVS

ROLE_TAB_PRIVS describes table privileges granted to roles. Information is provided only about roles to which the user has access.

Column	Datatype	NULL	Description
`ROLE`	`VARCHAR2(30)`	`NOT NULL`	Name of the role
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the object
`TABLE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the object
`COLUMN_NAME`	`VARCHAR2(30)`		Name of the column, if applicable
`PRIVILEGE`	`VARCHAR2(40)`	`NOT NULL`	Object privilege granted to the role
`GRANTABLE`	`VARCHAR2(3)`		`YES` if the role was granted with `ADMIN OPTION`; otherwise `NO`

包含了角色被赋予的对象权限。

例如，角色ORDADMIN被赋予的对象权限。

SQL> select * from role_tab_privs where role = 'ORDADMIN';

ROLE	   OWNER		TABLE_NAME	     COLUMN_NAME										PRIVILEGE	     GRANTABLE
---------- -------------------- -------------------- ------------------------------------------------------------------------------------------ -------------------- --------------------
ORDADMIN   ORDSYS		ORD_DICOM_ADMIN 												EXECUTE 	     NO
ORDADMIN   ORDDATA		ORDDCM_DOCUMENT_REFS												SELECT		     NO

4.总结

以DBA开头的视图包含了数据库内所有的系统权限、对象权限和角色。在登陆sys用户后，可以查看。如果想要查看某个用户或某个角色，那么需要GRANTEE=？条件。

以USER开头的视图包含了当前用户的系统权限、对象权限和角色。

以ROLE开头的视图包含了该角色拥有的系统权限、对象权限和角色。