User, Role and Privilege management in Oracle 12c

User creation, Roles and privileges in 12c.

User creation and role & privilege management changes slightly on Oracle 12c.

Now we have two types of user and roles & privileges such as Local and Common.

* Local User
* Common User
* Local roles and privileges
* Common roles and privileges.

Below are some points about this topic.

A CDB can contain only common User. By default any user created in a CBD is a common user.

Let’s create a Common User.

SQL>
SQL> create user c##testuser1 identified by oracle;

User created.

SQL>

SQL> L
1* select username,user_id,account_status,profile,common,oracle_maintained from dba_users where oracle_maintained='N'
SQL> /

USERNAME	USER_ID ACCOUNT_STATUS	PROFILE COM O
--------------- ---------- -------------------- ---------- --- -
C##TESTUSER1	105 OPEN DEFAULT YES N
C##CDB_USER	102 OPEN DEFAULT YES N
C##TEST1	104 OPEN DEFAULT YES N

SQL>

– A common user is visible and accessible on the root container and all pdb container.

Let’s check whether the common user is visible at PDB level.

Login to any PDB or switch to any PDB and check the dba_users dictionary view.

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

SQL> sho con_name
CON_NAME
------------------------------
PDB20
SQL> set lines 200 pages 2000
SQL> col username for a20

SQL> select username,account_status,common from dba_users where oracle_maintained='N';
USERNAME             ACCOUNT_STATUS                   COM
-------------------- -------------------------------- ---
TEST3                OPEN                             NO
TEST2                OPEN                             NO
TEST1                OPEN                             NO
PDB_ADM              OPEN                             NO
C##TESTUSER1         OPEN                             YES
C##DEEPAK            OPEN                             YES
C##SREE              OPEN                             YES
C##DEEPAK2           OPEN                             YES

8 rows selected.

SQL> select username,account_status,common from dba_users where username='C##TESTUSER1';
USERNAME             ACCOUNT_STATUS                   COM
-------------------- -------------------------------- ---
C##TESTUSER1         OPEN                             YES

SQL>

– A common user can perform administrative action on root container and PDB containers only if provided with necessary roles and privileges.

Let’s try to login to a containter with the newly created common user.

SQL>
SQL> conn c##testuser1/deepak@localhost:1521/pdb20
ERROR:
ORA-01045: user C##TESTUSER1 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
SQL>

As per the above snippet we are not able to even login to the PDB without necessary privilege.

– A common user name always start with the string “c##”, for example “c##tester”
– By default it is not possible to create without string “C##”, it is possible to create user with regular name only by setting the undocumented parameter “_ORACLE_SCRIPT”. (Ofcourse it is not recommended to set underscore parameter without consent from Oracle Support. Your database may be desupported if you set undocumented parameter without directive from Oracle support.)

Now let’s try to create common user without the C## string.

SQL> show parameter _Oracle_script
SQL>
SQL>
SQL> create user testuser2 identified by deepak;
create user testuser2 identified by deepak
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL>

The undocumented parameter “_ORACLE_SCRIPT” is not set and we tried to create a cmmon user without the string, we encountered an error.

SQL>
SQL> alter session set "_ORACLE_SCRIPT"=TRUE;

Session altered.

SQL> create user testuser1 identified by oracle;

User created.

SQL>

After setting the parameter we are able to create user without c## string.

Let’s check the user is whether common user and it is accessible from PDB level.

CDB level.

SQL> SET LINES 200 PAGES 2000
SQL> COL USERNAME FOR A20
SQL> select username,account_status,common from dba_users where username='TESTUSER1';

USERNAME             ACCOUNT_STATUS                   COM
-------------------- -------------------------------- ---
TESTUSER1            OPEN                             YES

SQL> SHO CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL>

At PDB level

SQL> alter session set container=PDB20;

Session altered.

SQL> SHO CON_NAME

CON_NAME
------------------------------
PDB20
SQL> SET LINES 200 PAGES 2000
SQL> COL USERNAME FOR A20
SQL> select username,account_status,common from dba_users where username='TESTUSER1';

no rows selected

SQL>

Common users created without c## is not visible at PDB level.

When trying to login with this common user, we get invalid user instead of missing Create session privilege

SQL> conn testuser1/oracle@localhost:1521/pdb20
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL>

– After creating a common user we must provide necessary privilege to the common user.
– It is possible to restrict the common role and privilege for a common user to only a subset of PDB, and it is also possible to provide Common role and privilege to all PDBs at once.
– It is possible to provide common users different privilege in different containers.

Now let’s provide create session privilege to only the PDB20 container and check the login behaviour at various containers.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO
         4 DEEPAK1                        READ WRITE NO
         5 PDB20                          READ WRITE NO
         6 PDB21                          READ WRITE NO
SQL>

SQL> grant create session to c##testuser1;

Grant succeeded.

SQL> select grantee,privilege,common,con_id from cdb_sys_privs  where grantee='C##TESTUSER1';

GRANTEE         PRIVILEGE                                COM     CON_ID
--------------- ---------------------------------------- --- ----------
C##TESTUSER1    CREATE SESSION                           NO           1

SQL>

As checked in the cdb_sys_privs the privilege is granted only in the container 1 which is root container.

Let’s try logging into some PDBs.

We have tried to login to the containers PDB20 and PDB21, but we are not able to login as the role provided was local to CDB$ROOT container.

SQL>
SQL> conn c##testuser1/deepak@localhost:1521/pdb20;
ERROR:
ORA-01045: user C##TESTUSER1 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn c##testuser1/deepak@localhost:1521/pdb21;
ERROR:
ORA-01045: user C##TESTUSER1 lacks CREATE SESSION privilege; logon denied

SQL>

We can provide common role to common user with either the option CONTAINER=ALL or CONTAINER=CURRENT.

Below examples shows how to set privilege at specific container level, for example PDB20

SQL> CONN / AS SYSDBA
Connected.
SQL>
SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL> ALTER SESSION SET CONTAINER=PDB20;

Session altered.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB20
SQL> GRANT CREATE SESSION TO C##TESTUSER1 CONTAINER=CURRENT;

Grant succeeded.

SQL> select grantee,privilege,common,con_id from cdb_sys_privs  where grantee='C##TESTUSER1';

GRANTEE         PRIVILEGE                                COM     CON_ID
--------------- ---------------------------------------- --- ----------
C##TESTUSER1    CREATE SESSION                           NO           5

SQL> alter session set container=cdb$root;

Session altered.

SQL> select grantee,privilege,common,con_id from cdb_sys_privs  where grantee='C##TESTUSER1';

GRANTEE         PRIVILEGE                                COM     CON_ID
--------------- ---------------------------------------- --- ----------
C##TESTUSER1    CREATE SESSION                           NO           1
C##TESTUSER1    CREATE SESSION                           NO           5

NOTE: Please not that the above privilege is not a Common privilege which is shown by the column COMMON.

Now create session has been granted to cdb$root and PDB20 containers. Let’s try to login to few PDBs.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> conn c##testuser1/deepak@localhost:1521/pdb20
Connected.
SQL>
SQL> show con_name

CON_NAME
------------------------------
PDB20
SQL>
SQL> disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL>
SQL> conn c##testuser1/deepak@localhost:1521/pdb21
ERROR:
ORA-01045: user C##TESTUSER1 lacks CREATE SESSION privilege; logon denied

SQL>

We are not able to login to the container PDB21 since we have not granted create session privilege to c##testuser1 for the container PDB21.

Now let’s give create session privilege to c##testuser1 with CONTAINER=ALL option and try the same.

SQL> conn / as sysdba
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> grant create session to c##testuser1 container=all;

Grant succeeded.

SQL> select grantee,privilege,common,con_id from cdb_sys_privs  where grantee='C##TESTUSER1';

GRANTEE         PRIVILEGE                                COM     CON_ID
--------------- ---------------------------------------- --- ----------
C##TESTUSER1    CREATE SESSION                           NO           1
C##TESTUSER1    CREATE SESSION                           YES          1
C##TESTUSER1    CREATE SESSION                           YES          4
C##TESTUSER1    CREATE SESSION                           YES          3
C##TESTUSER1    CREATE SESSION                           YES          6
C##TESTUSER1    CREATE SESSION                           NO           5
C##TESTUSER1    CREATE SESSION                           YES          5

7 rows selected.

SQL>

Note that the privilege we grant with container=all option created a record in the above view for all the available containers (except PDB$SEED).

Try logging some PDBs.

SQL> conn c##testuser1/deepak@localhost:1521/pdb20
Connected.
SQL> disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> conn c##testuser1/deepak@localhost:1521/pdb21
Connected.
SQL> disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> conn c##testuser1/deepak@localhost:1521/pdborcl
Connected.
SQL> disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>

We are able to login to all PDBs without problem.

Also note that if you grant a privilege with option CONTAINER=ALL to an username without preceeding c## string, it will NOT obtain privilege for all the containers. It will obtain the privilege only for the Root container (CDB).

Let’s create a common user without preceeding c## string at the root container. After creation we will grant create session with CONTAINER=ALL option. After granting the privilege check the dcitionary view cdb_sys_privs to confirm on which containers privilege are granted.

SQL> 
SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;

Session altered.

SQL> CREATE USER TESTER IDENTIFIED BY deepak;

User created.

SQL> grant create session to tester container=all;

Grant succeeded.

SQL> desc cdb_sys_privs
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE					    VARCHAR2(128)
 PRIVILEGE					    VARCHAR2(40)
 ADMIN_OPTION					    VARCHAR2(3)
 COMMON 					    VARCHAR2(3)
 CON_ID 					    NUMBER

SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee='TESTER';

GRANTEE		PRIVILEGE				 COM	 CON_ID
---------------------------------------- --- ----------
TESTER		CREATE SESSION				 YES	      1


SQL> 

– A user which is created in a PDB is a local user.
– It is not possible to create a common user at PDB level.
– There is no restriction like the string “C##” while creating a local user. We can create users with regular name as usual.
– Its not possible to create a common user at a PDB, try doing so will result in error.
– The role and privlege that is provided at a PDB is local role and local privilege.
– It is not possible to provide common role and common privilege at PDB level. Trying do so will result in error.

DB Cache Size, Oracle Blocks, Tablespace with non standard block size.

The basic unit of disk I/O performed by Oracle is an Oracle Block. Each Oracle block is a combination of several OS blocks and the number of OS block per Oracle block is OS dependent.

The default size of oracle block is specified by the parameter DB_BLOCK_SIZE which is non modifiable during the life time of the database.

Critical tablespaces such as SYSTEM and SYSAUX will be created with the block size specified by the parameter DB_BLOCK_SIZE. Possible value for this parameter such as 2K, 4K, 8K, 16K and 32K, for 32 bit servers the maximum value for this parameter is 16K.

OLTP system typically benefit better by using a 4K or 8K blocks, but need to be decided after studying the data model of the database. DSS databases would benefit better with the block sizes of 16K or 32K.

SQL> show parameter db_block_size
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_block_size			     integer	 8192
SQL>

This parameter cannot be changed after creation of the database.

SQL>
SQL> alter system set db_block_size=16384 scope=spfile;
alter system set db_block_size=16384 scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-32016: parameter "db_block_size" cannot be updated in SPFILE

SQL> alter system set db_block_size=16384 scope=memory;
alter system set db_block_size=16384 scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL>

If there is a need to create table with non-default block size, it is possible to create it in the same database. Oracle database allows upto 4 non standard block size tablespaces.

Before creating a tablespace with non standard block size be sure to allocate new buffer cache space that can accommodate blocks with this size by setting DB_nK_CACHE_SIZE.

For example if the default block size is 8K and you want to create a tablespace with 16K blocks then you need to allocate buffer cache to store 16K blocks using the parameter db_16K_cache_size.
Let’s try to create a new tablespace with non-default block size without allocating buffer cache with this block size.

SQL> create tablespace tbs_16k datafile '/oem/oradata/tbs_16k_datafile01.dbf' size 1g blocksize 16k;
create tablespace tbs_16k datafile '/oem/oradata/tbs_16k_datafile01.dbf' size 1g blocksize 16k
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes

Let’s set the 16K buffer cache as 50M and try to create the tablespace again

SQL> alter system set db_16k_cache_size=50M scope=both;
System altered.

SQL> create tablespace tbs_16k datafile '/oem/oradata/tbs_16k_datafile01.dbf' size 1g blocksize 16k;
Tablespace created.

SQL>

Tablespace has been succesfully created,

If you want to change the block size of a certain table you need to create a new tablespace with new required block size and move that table to this newly created tablespace.