Don’t the users SYS and SYSTEM share the table dba_users

oracleoracle-12c

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).

Best Answer

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

Related Solutions

Sql-server – How to connect multiple users/schemas in Oracle 11g

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.

In Oracle, will certain users only be able to see certain data in the privileges tables

The table_privileges view is only preserved for compatibility with ancient versions of Oracle.

You should instead be using the user_tab_privs view, all_tab_privs view, or dba_tab_privs view (documentation links for each when you click).

Best Answer

Related Solutions

Sql-server – How to connect multiple users/schemas in Oracle 11g

In Oracle, will certain users only be able to see certain data in the privileges tables

Related Question