0

While being logged in as SYS, I created a new user. I ran

 select username from dba_users order by username;

and I was able to see the user that i just created;

Then, I logged as SYSTEM, I ran the same query, but the user was missing this time. This leads me to the question: don't all users have the same instance of the table DBA_USERS? I thought that this was a system related table, and all users get the same instance (as long as they have the right privileges to access this table).

2
  • Let me guess. You have a container database and you are unaware of it. You created a user in a container, then did not find it in another. Can you see the user when querying CDB_USERS in the root container? Mar 21, 2019 at 15:20
  • yes. I did the following: while being logged as SYS, I created USER_CREATED_BY_SYS; while being logged as SYSTEM, I created USER_CREATED_BY_SYSTEM; when I query cdb_users, I see both users; when I query dba_users, I see only one user Mar 21, 2019 at 15:38

1 Answer 1

1

Local users created in a pluggable database are not visible in other containers through DBA_USERS. You can query all users from all open pluggable databases in the root container through CDB_USERS.

Overview of Commonality in a CDB

SQL> show con_name pdbs

CON_NAME
------------------------------
CDB$ROOT

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SAMPLE                         READ WRITE NO
         5 BENCHMARK                      READ WRITE NO

SQL>  conn sys/Oracle123@o7ca1-vip/SAMPLE as sysdba
Connected.
SQL> create user user_created_by_sys identified by 1;

User created.

SQL> conn system/Oracle123@o7ca1-vip/BENCHMARK
Connected.
SQL> create user user_created_by_system identified by 1;

User created.

SQL> select username from dba_users where username like 'USER_CREATED%';

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
USER_CREATED_BY_SYSTEM

SQL> conn sys/Oracle123@o7ca1-vip/SAMPLE as sysdba
Connected.
SQL> select username from dba_users where username like 'USER_CREATED%';

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
USER_CREATED_BY_SYS

SQL> conn / as sysdba
Connected.
SQL> select username from dba_users where username like 'USER_CREATED%';

no rows selected

SQL> select con_id, username from cdb_users where username like 'USER_CREATED%';

    CON_ID USERNAME
---------- --------------------------------------------------------------------------------------------------------------------------------
         3 USER_CREATED_BY_SYS
         5 USER_CREATED_BY_SYSTEM

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.