View the information of a user

After you create a user, you can query the ALL_USERS, DBA_USERS, or USER_USERS view for the information of the user.

Procedure

1. Log on to an Oracle tenant of the database.

2. Execute the following statements to view user information.

   • View the details of the current user

     obclient [TEST2]> SELECT * FROM USER_USERS;
     

     The query result is as follows:

     +----------+--------+----------------+-----------+-------------+--------------------+----------------------+-----------------------+-----------+-----------------------------+---------------+--------------------+--------+-------------------+-----------+-------------------+----------+-----------+----------------------+
     | USERNAME | USERID | ACCOUNT_STATUS | LOCK_DATE | EXPIRY_DATE | DEFAULT_TABLESPACE | TEMPORARY_TABLESPACE | LOCAL_TEMP_TABLESPACE | CREATED   | INITIAL_RSRC_CONSUMER_GROUP | EXTERNAL_NAME | PROXY_ONLY_CONNECT | COMMON | ORACLE_MAINTAINED | INHERITED | DEFAULT_COLLATION | IMPLICIT | ALL_SHARD | PASSWORD_CHANGE_DATE |
     +----------+--------+----------------+-----------+-------------+--------------------+----------------------+-----------------------+-----------+-----------------------------+---------------+--------------------+--------+-------------------+-----------+-------------------+----------+-----------+----------------------+
     | TEST2    | 500036 | OPEN           | NULL      | NULL        | NULL               | NULL                 | NULL                  | 21-DEC-23 | NULL                        | NULL          | N                  | NO     | N                 | NO        | USING_NLS_COMP    | NO       | NO        | 21-DEC-23            |
     +----------+--------+----------------+-----------+-------------+--------------------+----------------------+-----------------------+-----------+-----------------------------+---------------+--------------------+--------+-------------------+-----------+-------------------+----------+-----------+----------------------+
     1 row in set
     

     For more information about the USER_USERS view, see USER_USERS.

   • View information about all users visible to the current user

     obclient [TEST2]> SELECT * FROM ALL_USERS;
     

     The query result is as follows:

     +------------+--------+-----------+--------+-------------------+-----------+-------------------+----------+-----------+
     | USERNAME   | USERID | CREATED   | COMMON | ORACLE_MAINTAINED | INHERITED | DEFAULT_COLLATION | IMPLICIT | ALL_SHARD |
     +------------+--------+-----------+--------+-------------------+-----------+-------------------+----------+-----------+
     | SYS        | 200003 | 18-DEC-23 | NO     | N                 | NO        | USING_NLS_COMP    | NO       | NO        |
     | LBACSYS    | 200004 | 18-DEC-23 | NO     | N                 | NO        | USING_NLS_COMP    | NO       | NO        |
     | ORAAUDITOR | 200005 | 18-DEC-23 | NO     | N                 | NO        | USING_NLS_COMP    | NO       | NO        |
     | TEST1      | 500028 | 20-DEC-23 | NO     | N                 | NO        | USING_NLS_COMP    | NO       | NO        |
     | TEST2      | 500036 | 21-DEC-23 | NO     | N                 | NO        | USING_NLS_COMP    | NO       | NO        |
     +------------+--------+-----------+--------+-------------------+-----------+-------------------+----------+-----------+
     5 rows in set
     

     Fields in the query result are described as follows:

     • SYS: the built-in database administrator in the system.
     • LBACSYS: the built-in database security administrator in the system.
     • ORAAUDITOR: the built-in database auditor in the system.
     • TEST1 and TEST2: custom users created.

     For more information about the ALL_USERS view, see ALL_USERS.

   • View the details of all users in the current tenant as an administrator

     obclient [TEST2]> SELECT * FROM DBA_USERS;
     

     The query result is as follows:

     +------------+--------+-------------------------------------------+----------------+-----------+-------------+--------------------+----------------------+-----------------------+-----------+---------+-----------------------------+---------------+-------------------+------------------+---------------------+--------------------+--------+------------+-------------------+-----------+-------------------+----------+-----------+----------------------+
     | USERNAME   | USERID | PASSWORD                                  | ACCOUNT_STATUS | LOCK_DATE | EXPIRY_DATE | DEFAULT_TABLESPACE | TEMPORARY_TABLESPACE | LOCAL_TEMP_TABLESPACE | CREATED   | PROFILE | INITIAL_RSRC_CONSUMER_GROUP | EXTERNAL_NAME | PASSWORD_VERSIONS | EDITIONS_ENABLED | AUTHENTICATION_TYPE | PROXY_ONLY_CONNECT | COMMON | LAST_LOGIN | ORACLE_MAINTAINED | INHERITED | DEFAULT_COLLATION | IMPLICIT | ALL_SHARD | PASSWORD_CHANGE_DATE |
     +------------+--------+-------------------------------------------+----------------+-----------+-------------+--------------------+----------------------+-----------------------+-----------+---------+-----------------------------+---------------+-------------------+------------------+---------------------+--------------------+--------+------------+-------------------+-----------+-------------------+----------+-----------+----------------------+
     | SYS        | 200003 | ***************************************** | OPEN           | NULL      | NULL        | NULL               | NULL                 | NULL                  | 18-DEC-23 | DEFAULT | NULL                        | NULL          | NULL              | N                | PASSWORD            | N                  | NO     | NULL       | N                 | NO        | USING_NLS_COMP    | NO       | NO        | 18-DEC-23            |
     | LBACSYS    | 200004 | ***************************************** | LOCKED         | NULL      | NULL        | NULL               | NULL                 | NULL                  | 18-DEC-23 | DEFAULT | NULL                        | NULL          | NULL              | N                | PASSWORD            | N                  | NO     | NULL       | N                 | NO        | USING_NLS_COMP    | NO       | NO        | 18-DEC-23            |
     | ORAAUDITOR | 200005 | ***************************************** | LOCKED         | NULL      | NULL        | NULL               | NULL                 | NULL                  | 18-DEC-23 | DEFAULT | NULL                        | NULL          | NULL              | N                | PASSWORD            | N                  | NO     | NULL       | N                 | NO        | USING_NLS_COMP    | NO       | NO        | 18-DEC-23            |
     | TEST1      | 500028 | ***************************************** | OPEN           | NULL      | NULL        | NULL               | NULL                 | NULL                  | 20-DEC-23 | DEFAULT | NULL                        | NULL          | NULL              | N                | PASSWORD            | N                  | NO     | NULL       | N                 | NO        | USING_NLS_COMP    | NO       | NO        | 20-DEC-23            |
     | TEST2      | 500036 | ***************************************** | OPEN           | NULL      | NULL        | NULL               | NULL                 | NULL                  | 21-DEC-23 | DEFAULT | NULL                        | NULL          | NULL              | N                | PASSWORD            | N                  | NO     | NULL       | N                 | NO        | USING_NLS_COMP    | NO       | NO        | 21-DEC-23            |
     +------------+--------+-------------------------------------------+----------------+-----------+-------------+--------------------+----------------------+-----------------------+-----------+---------+-----------------------------+---------------+-------------------+------------------+---------------------+--------------------+--------+------------+-------------------+-----------+-------------------+----------+-----------+----------------------+
     5 rows in set
     

     Fields in the query result are described as follows:

     • SYS: the built-in database administrator in the system.

     • LBACSYS: the built-in database security administrator in the system, which is locked by default. To use this administrator, you must unlock it first.

     • ORAAUDITOR: the built-in database auditor in the system, which is locked by default. To use this administrator, you must unlock it first.

     For more information about the DBA_USERS view, see DBA_USERS.

References

Users and permissions