Posts Tagged Multitenant

Common Roles get copied upon plug-in with #Oracle Multitenant

What happens when you unplug a pluggable database that has local users who have been granted common roles? They get copied upon plug-in of the PDB to the target container database!

Before Unplug of the PDBThe picture above shows the situation before the unplug command. It has been implemented with these commands:

 

SQL> connect / as sysdba
Connected.
SQL> create role c##role container=all;

Role created.

SQL> grant select any table to c##role container=all;

Grant succeeded.

SQL> connect sys/oracle_4U@pdb1 as sysdba
Connected.
SQL> grant c##role to app;

Grant succeeded.



SQL> grant create session to app;

Grant succeeded.

The local user app has now been granted the common role c##role. Let’s assume that the application depends on the privileges inside the common role. Now the pdb1 is unplugged and plugged in to cdb2:

SQL> shutdown immediate
Pluggable Database closed.
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.xml';

Pluggable database altered.

SQL> drop pluggable database pdb1;

Pluggable database dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@EDE5R2P0 ~]$ . oraenv
ORACLE_SID = [cdb1] ? cdb2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle
[oracle@EDE5R2P0 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 29 12:52:19 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create pluggable database pdb1 using '/home/oracle/pdb1.xml' nocopy;

Pluggable database created.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> connect app/app@pdb1
Connected.
SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE

SQL> connect / as sysdba
Connected.

SQL> select role,common from cdb_roles where role='C##ROLE';

ROLE
--------------------------------------------------------------------------------
COM
---
C##ROLE
YES

As seen above, the common role has been copied upon the plug-in like the picture illustrates:
After plug-in of the PDBNot surprisingly the local user app together with the local privilege CREATE SESSION was moved to the target container database. But it is not so obvious that the common role is copied then to the target CDB. This is something I found out during delivery of a recent Oracle University LVC about 12c New Features, thanks to a question of one attendee. My guess was it will lead to an error upon unplug, but this test-case proves it doesn’t. I thought that behavior may be of interest to the Oracle Community. As always: Don’t believe it, test it! :-)

About these ads

,

Leave a comment

Common Users & SYSDBA with #Oracle 12c Multitenancy

A 12c multitenant database introduces the new concept of local users and common users. 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 in the picture below.

Or suppose we have many pluggable databases (PDBs) and different superusers responsible for different PDBs like C##_ADMIN1 and C##_ADMIN2:

Common Users in Oracle 12cLet’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.

The whole example is from my present 12c New Features in Düsseldorf, by the way. Hope you find it useful :-)
As always: Don’t believe it, test it!

,

Leave a comment

Follow

Get every new post delivered to your Inbox.

Join 2,747 other followers

%d bloggers like this: