Oracle教程 2023-02-24

Oracle 视图 DBA_ROLES 官方解释，作用，如何使用详细说明

Oracle视图DBA_ROLES存储数据库中所创建的角色的详细信息，可以用来查询特定数据库用户（DBA）角色的详细信息。它由系统用户角色、用户定义角色和系统内部角色组成，包括角色类型、角色名称、角色虚拟模式标志等常用数据。

使用Oracle视图DBA_ROLES的具体步骤如下：

Step 1：连接到 Oracle 数据库，使用用户SYSTEM账号密码登录（用户SYSTEM需要设置为DBA类型）。

Step 2：打开 SQLPlus，输入以下SQL查询语句：

SELECT * FROM sys.dba_roles;

Step 3：结果集中返回所有数据库中的角色信息，包括角色类型、角色名称、角色虚拟模式标志等常用数据。

DBA_ROLES describes all roles in the database.

Column	Datatype	NULL	Description
`ROLE`	`VARCHAR2(128)`	`NOT NULL`	Name of the role
`ROLE_ID`	`NUMBER`	`NOT NULL`	ID number of the role
`PASSWORD_REQUIRED`	`VARCHAR2(8)`		This column is deprecated in favor of the `AUTHENTICATION_TYPE` column
`AUTHENTICATION_TYPE`	`VARCHAR2(11)`		Indicates the authentication mechanism for the role: `NONE` – `CREATE ROLE` `role1;` `EXTERNAL` – `CREATE ROLE` `role2` `IDENTIFIED EXTERNALLY;` `GLOBAL` – `CREATE ROLE` `role3` `IDENTIFIED GLOBALLY;` `APPLICATION` – `CREATE ROLE` `role4` `IDENTIFIED USING` `schema.package;` `PASSWORD` – `CREATE ROLE` `role5` `IDENTIFIED BY` `role5;`
`COMMON`	`VARCHAR2(3)`		Indicates whether a given role is common. Possible values: `YES` if the role is common `NO` if the role is local (not common)
`ORACLE_MAINTAINED`	`VARCHAR2(1)`		Denotes whether the role was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql). A role for which this column has the value `Y` must not be changed in any way except by running an Oracle-supplied script.
`INHERITED`	`VARCHAR2(3)`		Indicates whether the role was inherited from another container (`YES`) or not (`NO`)
`IMPLICIT`	`VARCHAR2(3)`		Indicates whether the role is a common role created by an implicit application (`YES`) or not (`NO`)
`EXTERNAL_NAME`	`VARCHAR2(4000)`		For a global role, the external name refers to the DN of a group from a directory service that is mapped to the global role. This is not applicable to a local role.

分享到：