Oracle Multitenant – SYS*-privileges on PDB level

2018-06-30 Off By Markus Flechtner

In an Oracle Container Database, SYSDBA, SYSDG, SYSBACKUP etc. privileges can
be granted on PDB level. This enables PDB administrators with their local
users e.g. to open and to close a PDB. How does this work and where is
this privilege information stored?

Deutsche Übersetzung dieses Beitrages auf www.markusdba.de

Granting SYS-privileges on PDB level

SQL> show user
USER is "SYS"

SQL> alter session set container=PDB01;
Session altered.

SQL> create user localadmin identified by manager;
User created.

SQL> grant sysdba to localadmin;
Grant succeeded.

That’s it.

Let us now close the PDB as SYS and re-open the PDB as the user LOCALADMIN:

Opening and closing a PDB as local SYSDBA

Even when the PDB is closed, the default service of the PDB is still registered with
the listener:

SQL> host lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 27-JUN-2018 19:15:39
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 27-JUN-2018 19:09:37
[..]
Service "pdb01.markusdba.local" has 1 instance(s).
Instance "TVDCDB1", status READY, has 1 handler(s) for this service...
[..]

This enables the SYSDBA of the PDB to connect to the PDB.
Applications (i.e “Non-SYSDBA”-connections) which try to connect to the
default service of the PDB will fail:

SQL> connect localadmin/manager@markusdba.local:1521/pdb01.markusdba.local
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

Warning: You are no longer connected to ORACLE.

But SYSDBA connects work:

SQL> connect localadmin/manager@omt.markusdba.local:1521/pdb01.markusdba.local as sysdba
Connected.

SQL> alter database open;
Database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
7 PDB01 READ WRITE NO

Where is the SYSDBA information stored?

SQL> connect / as sysdba
Connected.

SQL> select con_id,username,sysdba from v$pwfile_users;

CON_ID USERNAME SYSDB
---------- ------------------------- -----
0 SYS TRUE
1 SYSDG FALSE
1 SYSBACKUP FALSE
1 SYSKM FALSE
7 LOCALADMIN TRUE

So it seems that it is stored in the password file. Let’s take a closer look at the password file:

SQL> host ls -ltr $ORACLE_HOME/dbs/orapw*
lrwxrwxrwx. 1 oracle oinstall 48 Mar 10 2017 /u00/app/oracle/product/12.2.0.1/dbs/orapwTVDCDB1 -> /u00/app/oracle/admin/TVDCDB1/pfile/orapwTVDCDB1

SQL> host ls -ltr /u00/app/oracle/admin/TVDCDB1/pfile/orapwTVDCDB1
-rw-r-----. 1 oracle oinstall 5632 Apr 1 18:42 /u00/app/oracle/admin/TVDCDB1/pfile/orapwTVDCDB1

SQL> host strings /u00/app/oracle/admin/TVDCDB1/pfile/orapwTVDCDB1
]\[Z
ORACLE Remote Password file
( yr
{#Z
SYSDG
9pVQ\
SYSBACKUP
nk{^
SYSKM
EuU[
DE\o

So the user LOCALADMIN appears in V$PWFILE_USERS but is obviously not stored in the password file.

But there’s a data dictionary view which contains the information we are looking for:

SQL> desc CDB_LOCAL_ADMIN_PRIVS
Name Null? Type
----------------------------------------- -------- ----------------------------
CON_ID NOT NULL NUMBER
CON_NAME NOT NULL VARCHAR2(128)
GRANTEE NOT NULL VARCHAR2(128)
SYSDBA VARCHAR2(5)
SYSOPER VARCHAR2(5)
SYSASM VARCHAR2(5)
SYSBACKUP VARCHAR2(5)
SYSDG VARCHAR2(5)
SYSKM VARCHAR2(5)

SQL> column con_name format a30
SQL> column grantee format a30
SQL> select con_id,con_name,grantee,sysdba from CDB_LOCAL_ADMIN_PRIVS
2 order by con_id;

CON_ID CON_NAME GRANTEE SYSDB
---------- ------------------------------ ------------------------------ -----
7 PDB01 LOCALADMIN TRUE

This configuration makes sense: The password file is required to authenticate a user when the database is not open. But when you want to connect to a PDB as SYSDBA in order to open it, at least the CDB$ROOT must be open. And so the data dictionary of the CDB$ROOT is accessible. Storing the information outside the database (in the password file) is not required.

Note: CDB_LOCAL_ADMIN_PRIVS is mentioned in the 12.1 Security Guide (https://docs.oracle.com/database/121/DBSEG/authorization.htm#BEGIN) but is not documented in the Oracle 12.2 reference

When you check the definition of CDB_LOCAL_ADMIN_PRIVS you’ll find that it is based on the table CDB_LOCAL_ADMINAUTH$:

SQL> desc cdb_local_adminauth$
Name Null? Type
—————————————– ——– —————————-
CON_UID NOT NULL NUMBER
GRANTEE$ NOT NULL VARCHAR2(128)
PRIVILEGES NOT NULL NUMBER
PASSWD NOT NULL VARCHAR2(4000)
FLAGS NOT NULL NUMBER
SPARE1 NUMBER
SPARE2 VARCHAR2(128)
LCOUNT NUMBER
ASTATUS NUMBER
EXPTIME DATE
LTIME DATE
LSLTIME DATE
PASSWD_PROFILE VARCHAR2(128)
PASSWD_LIMIT VARCHAR2(4000)
FED_PRIVILEGES NOT NULL NUMBER
EXT_USERNAME VARCHAR2(4000)

And our local admin is there:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> column grantee$ format a30
SQL> select con_uid,grantee$ from CDB_LOCAL_ADMINAUTH$;

CON_UID GRANTEE$
---------- ------------------------------
1343596567 LOCALADMIN

How is CDB_LOCAL_ADMINAUTH$ stored internally?

SQL> select con_id,owner,object_type,sharing from cdb_objects
2 where object_name='CDB_LOCAL_ADMINAUTH

It’s a metadata-linked table, which means that data is stored locally in each container.

But where does this table really exist?

SQL> select con_id from cdb_segments where segment_name='CDB_LOCAL_ADMINAUTH

So it’s only stored in the root container CDB$ROOT. As expected.

What happens, when the unplug the PDB and plug it into another CDB?

SQL> alter pluggable database pdb01 close;

Pluggable database altered.

SQL> alter pluggable database PDB01 unplug into '/home/oracle/pdb01.xml';

Pluggable database altered.

SQL> select con_uid,grantee$ from CDB_LOCAL_ADMINAUTH$;

CON_UID GRANTEE$
---------- ------------------------------
1343596567 LOCALADMIN

SQL> drop pluggable database pdb01 keep datafiles;

Pluggable database dropped.

SQL> select con_uid,grantee$ from CDB_LOCAL_ADMINAUTH$;

no rows selected

When dropping the PDB the row in CDB_LOCAL_ADMINAUTH$ is deleted.

Let’s take a look at the xml-manifest file

SQL> host grep -i LOCALADMIN /home/oracle/pdb01.xml

SQL> host grep -i SYSDBA /home/oracle/pdb01.xml

So there is no information on the user LOCALADMIN and its SYSDBA privileges in the xml file.

Let’s plug the database into another CDB:

SQL> select con_uid,grantee$ from CDB_LOCAL_ADMINAUTH$;
no rows selected

SQL> create pluggable database PDB01 using '/home/oracle/pdb01.xml';
Pluggable database created.

SQL> select con_uid,grantee$ from CDB_LOCAL_ADMINAUTH$;
no rows selected

SQL> alter pluggable database PDB01 open;
Pluggable database altered.

SQL> column grantee$ format a30
SQL> select con_uid,grantee$ from CDB_LOCAL_ADMINAUTH$;

CON_UID GRANTEE$
---------- ------------------------------
245011485 LOCALADMIN

So the user is back again.

It seems that the information on local (PDB level)  SYSDBA is written into the PDB when the PDB is unplugged and copied back to CDB$ROOT in the target CDB when the PDB is plugged in again.

 

Links:

; CON_ID OWNER OBJECT_TYPE SHARING ---------- ---------- ----------------------- ------------------ 1 SYS TABLE METADATA LINK 5 SYS TABLE METADATA LINK 3 SYS TABLE METADATA LINK 4 SYS TABLE METADATA LINK

It’s a metadata-linked table, which means that data is stored locally in each container.

But where does this table really exist?



So it’s only stored in the root container CDB$ROOT. As expected.


What happens, when the unplug the PDB and plug it into another CDB?




When dropping the PDB the row in CDB_LOCAL_ADMINAUTH$ is deleted.


Let’s take a look at the xml-manifest file




So there is no information on the user LOCALADMIN and its SYSDBA privileges in the xml file.


Let’s plug the database into another CDB:




So the user is back again.


It seems that the information on local (PDB level)  SYSDBA is written into the PDB when the PDB is unplugged and copied back to CDB$ROOT in the target CDB when the PDB is plugged in again.


 


Links:



; CON_ID ---------- 1


So it’s only stored in the root container CDB$ROOT. As expected.


What happens, when the unplug the PDB and plug it into another CDB?




When dropping the PDB the row in CDB_LOCAL_ADMINAUTH$ is deleted.


Let’s take a look at the xml-manifest file




So there is no information on the user LOCALADMIN and its SYSDBA privileges in the xml file.


Let’s plug the database into another CDB:




So the user is back again.


It seems that the information on local (PDB level)  SYSDBA is written into the PDB when the PDB is unplugged and copied back to CDB$ROOT in the target CDB when the PDB is plugged in again.


 


Links:



; CON_ID OWNER OBJECT_TYPE SHARING ———- ———- ———————– —————— 1 SYS TABLE METADATA LINK 5 SYS TABLE METADATA LINK 3 SYS TABLE METADATA LINK 4 SYS TABLE METADATA LINK


It’s a metadata-linked table, which means that data is stored locally in each container.


But where does this table really exist?




So it’s only stored in the root container CDB$ROOT. As expected.


What happens, when the unplug the PDB and plug it into another CDB?




When dropping the PDB the row in CDB_LOCAL_ADMINAUTH$ is deleted.


Let’s take a look at the xml-manifest file




So there is no information on the user LOCALADMIN and its SYSDBA privileges in the xml file.


Let’s plug the database into another CDB:




So the user is back again.


It seems that the information on local (PDB level)  SYSDBA is written into the PDB when the PDB is unplugged and copied back to CDB$ROOT in the target CDB when the PDB is plugged in again.


 


Links:





Ad (Amazon Link):



    															

																
CategoryMultitenant Oracle 12c