DBA_ROLES
📄字数 1.3K
👁️阅读量 加载中...
功能描述
DBA_ROLES系统视图用于展示当前库下所有角色信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | USER_ID | INTEGER | 用户ID | √ | √ |
| 2 | USER_NAME | VARCHAR | 用户名 | √ | √ |
| 3 | IS_ROLE | BOOLEAN | 是否为角色(该视图该字段为true) | √ | √ |
| 4 | PASSWORD | BINARY | 口令的加密后内容 | √ | √ |
| 5 | START_TIME | DATETIME | 有效期开始时间 | √ | √ |
| 6 | UNTIL_TIME | DATETIME | 有效期结束时间 | √ | √ |
| 7 | LOCKED | BOOLEAN | 是否被锁定（在解锁前该用户不能登录系统） | √ | √ |
| 8 | EXPIRED | BOOLEAN | 是否过期（过期后该用户可登录系统但只能进行口令重设操作） | √ | √ |
| 9 | PASS_SET_TIME | DATETIME | 最近一次设置口令的时间 | √ | √ |
| 10 | PASS_SET_PERIOD | INTEGER | 口令设置周期 | √ | √ |
| 11 | ALIAS | VARCHAR | 用户别名 | √ | √ |
| 12 | IS_SYS | BOOLEAN | 是否系统内建 | √ | √ |
| 13 | TRUST_IP | VARCHAR | 可信任IP范围 | √ | √ |
| 14 | XLS_PID | INTEGER | 安全策略ID | √ | √ |
| 15 | XLS_LID | INTEGER | 安全级别ID | √ | √ |
| 16 | XLS_CIDS | BIGINT | 安全范畴ID | √ | √ |
| 17 | PRIORITY | INTEGER | 执行优先级 | √ | √ |
| 18 | TEMP_SPACE_QUOTA | INTEGER | 临时表空间配额（单位：M） | √ | √ |
| 19 | CURSOR_QUOTA | INTEGER | cursor配额 | √ | √ |
| 20 | SESSION_QUOTA | INTEGER | SESSION配额 | √ | √ |
| 21 | IO_QUOTA | INTEGER | IO配额（单次命令最大IO次数） | √ | √ |
| 22 | CREATE_TIME | DATETIME | 创建时间 | √ | √ |
| 23 | LAST_MODI_TIME | DATETIME | 最后修改时间 | √ | √ |
| 24 | ENCRY_ID | INTEGER | 加密机ID | × | √ |
| 25 | STO_ZONE | INTEGER | 存储域 | x | √ |
| 26 | RESERVED3 | VARCHAR | 保留字段 | √ | √ |
应用举例
- 查询库下内置角色信息
sql
SQL> SELECT user_name,is_role,locked FROM DBA_ROLES;
+-----------------+---------+--------+
| USER_NAME | IS_ROLE | LOCKED |
+-----------------+---------+--------+
| DB_ADMIN | T | <NULL> |
| DB_POLICY_ADMIN | T | <NULL> |
| DB_POLICY_OPER | T | <NULL> |
| DB_AUDIT_ADMIN | T | <NULL> |
| DB_AUDIT_OPER | T | <NULL> |
| PUBLIC | T | <NULL> |
+-----------------+---------+--------+- 查询自定义用户具有的角色信息
sql
-- 创建用户账户
SQL> CREATE USER new_user IDENTIFIED BY 'pass_123';
-- 用户授予权限
SQL> CREATE ROLE role_new INIT USER new_user;
SQL> SELECT * FROM DBA_ROLES WHERE USER_NAME='ROLE_NEW';
+-------+---------+-----------+---------+----------+------------+------------+--------+---------+---------------+-----------------+--------+--------+----------+---------+---------+----------+----------+------------------+--------------+---------------+----------+-------------+----------------+----------+----------+-----------+
| DB_ID | USER_ID | USER_NAME | IS_ROLE | PASSWORD | START_TIME | UNTIL_TIME | LOCKED | EXPIRED | PASS_SET_TIME | PASS_SET_PERIOD | ALIAS | IS_SYS | TRUST_IP | XLS_PID | XLS_LID | XLS_CIDS | PRIORITY | TEMP_SPACE_QUOTA | CURSOR_QUOTA | SESSION_QUOTA | IO_QUOTA | CREATE_TIME | LAST_MODI_TIME | ENCRY_ID | STO_ZONE | RESERVED3 |
+-------+---------+-----------+---------+----------+------------+------------+--------+---------+---------------+-----------------+--------+--------+----------+---------+---------+----------+----------+------------------+--------------+---------------+----------+-------------+----------------+----------+----------+-----------+
| 1 | 105 | ROLE_NEW | T | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | F | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> |
+-------+---------+-----------+---------+----------+------------+------------+--------+---------+---------------+-----------------+--------+--------+----------+---------+---------+----------+----------+------------------+--------------+---------------+----------+-------------+----------------+----------+----------+-----------+