In this chapter, we will cover the following tasks:
Creating a common user
Creating a local user
Creating a common role
Creating a local role
Granting privileges commonly
Granting privileges locally
Granting common and local roles
The effects of plugging/unplugging operations on users, roles, and privileges
The Oracle multitenant environment is a new architecture of Oracle Database, introduced in version 12c (12.1.0.1). It brings major changes to the way Oracle Database administrators think about the concept of databases and how they work (in a multitenant environment). One of the most significant changes is that many databases (up to 252) can share one database instance.
This chapter is focused on some of the security considerations concerning common and local users, roles, and privileges. The prerequisite for understanding recipes in this chapter is to have at least basic knowledge of fundamental multitenant concepts, such as what is a container database (CDB), pluggable database (PDB), root container, and seed.
Figure 1 shows the traditional architecture of Oracle Database.
Figure 1 - A traditional architecture
Figure 2 shows the separation of the data dictionary in a multitenant architecture:
Figure 2 - Data Dictionary separation
Figure 3 shows a multitenant architecture. To learn...
A common user is a user created in the root container, which has the same identity across all containers. The main purpose of a common user is to perform "infrastructure" administrative tasks, such as starting up a CDB, plugging and unplugging PDBs, and opening PDBs. There are two types of common users: Oracle-supplied (for example, SYS and SYSTEM) and user-created common users.
To complete this recipe, you'll need an existing common user who has create user privilege granted commonly.
Connect to the root container as a common user who has create user privilege granted commonly (for example, c##zoran or system user):
SQL> connect c##zoran@cdb1
Create a common user (for example, c##maja):
c##zoran@CDB1> create user c##maja identified by oracle1
container=all;
A local user is a user that is created and that exists in only one PDB. A local user can't be created in the root container.
A pluggable database (in our case, pdb1) should be open. You'll need an existing user (either common or local) who has create user privilege in that pluggable database.
Connect to PDB (for example, pdb1) as a common user or local user who has create user privilege in that PDB (for example, c##zoran or system user):
SQL> connect c##zoran@pdb1
Create a local user (for example, mike):
c##zoran@PDB1> create user mike identified by pa3t5brii
container=current;
Figure 9
There are a few rules you should be aware of:
The name of a local user must be unique within its pluggable database and it must not begin with c## or C##
A local user cannot be created in the root
A local user exists in one and only one PDB and owns a schema...
Common roles are roles created in the root container and they exist in all containers. These roles can have a different set of privileges in different containers and they can be granted to either common or local users or roles.
To complete this recipe, you'll need an existing common user who has create role privilege granted commonly.
Connect to the root container as a common user who has create role privilege granted commonly (for example, c##zoran or system user):
SQL> connect c##zoran@cdb1
Create a common role (for example, c##role1):
SQL> create role c##role1 container=all;
When you create a common role, that role exists in all containers in that database (including a root container and existing and future pluggable databases).
Figure 12
c##zoran@CDB1> select * from dba_roles where role='C##ROLE1'; ROLE PASSWORD AUTHENTICAT COM O ---------------- -------- ----------- --- - C##ROLE1...
Local roles are roles created in PDB and they exist only in that PDB. These roles can be granted only locally to either common or local users or roles.
For this recipe, a pluggable database (in our case, pdb1) should be open. You'll need an existing user (either common or local) who has create role privilege in that pluggable database.
Connect to PDB (for example, pdb1) as a common or local user who has create role privilege in that PDB (for example, c##maja):
SQL> connect c##maja@pdb1
Create a local role (for example, local_role1):
c##maja@PDB1> create role local_role1 container=current;
When you create a local role, that role exists only in the pluggable database in which it is created. Local roles cannot be created in the root container. These roles are traditional roles.
Figure 15
c##maja@CDB1> select * from dba_roles where role='LOCAL_ROLE1'; no rows selected c##maja...
The common privilege is a privilege that can be exercised across all containers in a container database. Depending only on the way it is granted, a privilege becomes common or local. When you grant a privilege commonly (across all containers) it becomes a common privilege. Only common users or roles can have common privileges. Only common role can be granted commonly.
For this recipe, you will need to connect to the root container as an existing common user who is able to grant a specific privilege or existing role (in our case, create session, select any table, c##role1, c##role2) to another existing common user (c##john). If you want to try out examples in the How it works section, you should open pdb1 and pdb2.
You will use the following:
Common users c##maja and c##zoran with the dba role granted commonly
Common user c##john
Common roles c##role1 and c##role2
A local privilege is a privilege than can be exercised only in a container in which it is granted. Depending only on the way it is granted, a privilege becomes common or local. When you grant privilege locally (in the current container), it becomes a local privilege. Both common and local users or roles can have local privileges.
For this recipe, you'll need an existing user (c##maja) who can grant some privileges (for example, create procedure, create table, create view, and create synonym) and roles (c##role1, c##role2, c##role3, c##role4, and local_role1) in a specific container (root or PDB; in our case, pdb1) to existing users and roles (c##john, mike, local_role1, c##role1, c##role3, and c##role4).
The purpose of this recipe is to show what is going to happen to users, roles, and privileges when you unplug a pluggable database from one container database (cdb1) and plug it into some other container database (cdb2).
To complete this recipe, you will need the following:
Two container databases (cdb1 and cdb2)
One pluggable database (pdb1) in the container database cdb1
Local user mike in the pluggable database pdb1 with the local create session privilege
The common user c##john with the create session common privilege and create synonym local privilege on the pluggable database pdb1
Connect to the root container of cdb1 as user sys:
SQL> connect sys@cdb1 as sysdba
Unplug pdb1 by creating an XML metadata file:
SQL> alter pluggable database pdb1 unplug into
'/u02/oradata/pdb1.xml';
Drop pdb1 and keep the datafiles:
SQL> drop pluggable database pdb1 keep datafiles;
Connect...
© 2016 Packt Publishing Limited All Rights Reserved
