Common Users & SYSDBA with #Oracle 12c Multitenancy
Nice Explanation by - uhesse (URL)
In 12c multitenant database introduces the new This article shows simple use cases why DBAs may want to create common users – in contrast to the common users that are created automatically, like SYS, SYSTEM, MDSYS etc.
A typical requirement is to have a superuser other than SYS, but with the same power. Like the common user C##_SYS
Or suppose we have many pluggable databases (PDBs) and different superusers responsible for different PDBs like C##_ADMIN1 and C##_ADMIN2:
Let’s implement it as above. Initially, my demo environment looks like this:
SQL> select name,open_mode,con_id from v$pdbs;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB1 READ WRITE 3
PDB2 READ WRITE 4
PDB3 READ WRITE 5
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
At first, I create C##_SYS and grant SYSDBA as a common privilege to that new user:
SQL> create user c##_sys identified by oracle container=all;
User created.
SQL> grant sysdba to c##_sys container=all;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0
C##_SYS can now do anything to any PDB:
SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> shutdown immediate
Pluggable Database closed.
SQL> connect / as sysdba
Connected.
SQL> select name,open_mode,con_id from v$pdbs;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB1 MOUNTED 3
PDB2 READ WRITE 4
PDB3 READ WRITE 5
SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> startup
Pluggable Database opened.
SQL> connect / as sysdba
Connected.
SQL> select name,open_mode,con_id from v$pdbs;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB1 READ WRITE 3
PDB2 READ WRITE 4
PDB3 READ WRITE 5
Notice that there is a subtle difference in granting SYSDBA of the container database (CDB) as local or common privilege:
SQL> revoke sysdba from c##_sys container=all;
Revoke succeeded.
SQL> grant sysdba to c##_sys;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 1
SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
C##_SYS has SYSDBA of the CDB “only”, therefore the error. Although:
SQL> connect c##_sys/oracle as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb1;
Session altered.
SQL> shutdown immediate
Pluggable Database closed.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> startup
Pluggable Database opened.
SQL> connect c##_sys/oracle as sysdba
Connected.
SQL> select name,open_mode,con_id from v$pdbs;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB1 READ WRITE 3
PDB2 READ WRITE 4
PDB3 READ WRITE 5
However, the proper way is probably granting it as a common privilege:
SQL> revoke sysdba from c##_sys;
Revoke succeeded.
SQL> grant sysdba to c##_sys container=all;
Grant succeeded.
Now to the implementation of C##_ADMIN1 and C##_ADMIN2. The point is here, that SYSDBA can be granted as a local privilege for certain PDBs only, to the effect that different superusers can be responsible for different groups of PDBs:
SQL> create user c##_admin1 identified by oracle container=all;
User created.
SQL> alter session set container=pdb1;
Session altered.
SQL> grant sysdba to c##_admin1 container=current;
Grant succeeded.
SQL> connect / as sysdba
Connected.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0
C##_ADMIN1 TRUE FALSE FALSE FALSE FALSE FALSE 3
6 rows selected.
For now, C##_ADMIN1 can only connect to PDB1:
SQL> connect c##_admin1/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> select count(*) from session_privs;
COUNT(*)
----------
233
SQL> connect c##_admin1/oracle@edd2r6p0/pdb2 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
The completed implementation of the picture above:
SQL> connect / as sysdba
Connected.
SQL> alter session set container=pdb2;
Session altered.
SQL> grant sysdba to c##_admin1 container=current;
Grant succeeded.
SQL> connect / as sysdba
Connected.
SQL> create user c##_admin2 identified by oracle;
User created.
SQL> alter session set container=pdb3;
Session altered.
SQL> grant sysdba to c##_admin2 container=current;
Grant succeeded.
SQL> connect / as sysdba
Connected.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0
C##_ADMIN1 TRUE FALSE FALSE FALSE FALSE FALSE 3
C##_ADMIN1 TRUE FALSE FALSE FALSE FALSE FALSE 4
C##_ADMIN2 TRUE FALSE FALSE FALSE FALSE FALSE 5
8 rows selected.
No comments:
Post a Comment