ALL_USERS

📄字数 1.4K

👁️阅读量 加载中...

功能描述

ALL_USERS系统视图用于保存用户的相关信息。

重要

从安全角度考虑，ALL_USERS系统视图自V12.8.5起只允许访问当前用户信息。

字段说明

序号	字段名	类型	说明	V11	V12
0	DB_ID	INTEGER	库ID	√	√
1	USER_ID	INTEGER	用户ID	√	√
2	USER_NAME	VARCHAR	用户名	√	√
3	IS_ROLE	BOOLEAN	是否为角色(该视图该字段为false)	√	√
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	存储域	×	√
26	RESERVED3	VARCHAR	保留字段	√	√

应用举例

• 查询数据库当前库下当前用户信息。

SQL> SELECT * FROM ALL_USERS;
+-------+---------+-----------+---------+----------+--------------------------+--------------------------+--------+---------+---------------+-----------------+--------+--------+----------+---------+---------+----------+----------+------------------+--------------+---------------+----------+--------------------------+--------------------------+----------+----------+-----------+
| 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     | 1       | SYSDBA    | F       | <BINARY> | 2025-06-26 14:56:07.313  | 2125-06-02 14:56:07.313  | F      | F       | <NULL>        | <NULL>          | SYSDBA | T      | <NULL>   | <NULL>  | <NULL>  | <NULL>   | 1        | -1               | -1           | -1            | -1       | 2025-06-26 14:56:07.313  | 2025-06-26 14:56:07.313  | 0        | 0        | <NULL>    |
+-------+---------+-----------+---------+----------+--------------------------+--------------------------+--------+---------+---------------+-----------------+--------+--------+----------+---------+---------+----------+----------+------------------+--------------+---------------+----------+--------------------------+--------------------------+----------+----------+-----------+

• 查询用户名、是否为角色、是否被锁定等信息

SQL> CREATE USER user_info IDENTIFIED BY '1234@abcd' ACCOUNT LOCK;

SQL> SELECT user_name,is_role,locked FROM ALL_USERS WHERE user_name='USER_INFO';
+-----------+---------+--------+
| USER_NAME | IS_ROLE | LOCKED |
+-----------+---------+--------+
| USER_INFO | F       | T      |
+-----------+---------+--------+

• 查看用户是否过期

SQL> SELECT user_name,expired FROM ALL_USERS WHERE user_name='USER_INFO';
+-----------+---------+
| USER_NAME | EXPIRED |
+-----------+---------+
| USER_INFO | F       |
+-----------+---------+

相关系统表

• SYS_USERS