§
Local
users
o
Users
who exist only within a single pluggable database (PDB) - i.e. the one where they are created
§
Common
users
o
Users
who exist in all containers current and future and can navigate across them.
The table below compares
the most important features of the common user against the local user side by
side, and discusses plugging operations as applicable to common users.
Common user
|
Local user
|
| The scope of this user
is all current and future containers. |
The scope of the local
user is limited to the container in which it was created. |
| The common user’s scope
is administrative and system duties across multiple containers. |
The local user does not
have an existence or meaning outside of his container. |
| Once created, the common
user will be visible across all containers. However, such a user needs
explicit grants to be able to connect to and do things a pdb. And his grants can
differ from pdb to pdb. The common user can switch containers - if granted the required privileges. e.g. ALTER SESSION SET CONTAINER=hrpdb; |
The local user cannot
connect to the root. The local user will also need explicit grants like
create session, etc to do things within his container. The local user cannot connect to a container other than the one in which the user was created. |
| A number of common users
like SYS and SYSTEM are Oracle-supplied. Non-Oracle supplied common users can be user-created but their names must begin with C##. |
Local users can be
Oracle-supplied (e.g. sample schemas like SCOTT,HR) or user-created. Local user’s names cannot begin with C##, because that would make them a common user. |
In the remaining
sections, the creation and administration of local and common users is briefly
discussed.
Also covered briefly are local and common grants.
Creating users in a 12c multitenant container database
Creating a local user
Local
users can be created by CDB administrative users (CDB or PDB administrator)
after connecting to the specific PDB.
The steps to create a local user are shown below.
- Connect
to the specific PDB as an administrative user. To connect to the specific PDB (PDB1) as the PDB dba,
you can use easy-connect syntax as shown below. But, it is very important
to connect to the correct container.
- Issuer
the CREATE USER command. And issue the CREATE SESSION
and CREATE TABLE GRANTS.
- Test
the connectivity. In the screen below HR is able
to connect to PDB1 but not to the root CDB1. That’s because HR is a local
user – local to the PDB1 container only.
Creating a common user
The common
user is a new kind of user in 12c, found in 12c multitenant databases. Once
created, this user will be known and exist in all containers current and
future.
Such a
user can receive a special kind of GRANT, known as a common grant. A common grant is respected in all containers current
and future, and is a powerful privilege. It should be issued to administrative users
only.
The common
user’s purpose is to administer multiple pluggable databases. Such a dba may be
responsible to start/stop, restore/recover multiple PDBs – but only the ones to
which he has been granted the necessary access.
Whereas, administration
is something a PDB administrator can do within his pluggable database only.
The steps
to create a common user are shown below.
Note:
This is a created common user and not a
Oracle-supplied one. Therefore, the username must begin with C##.
- Connect to the ROOT container
as a privileged user like SYS.
- Issue the CREATE USER command,
and GRANT the CREATE SESSION privilege only.
Below, the GRANT was specified with
CONTAINER=ALL, therefore this is a COMMON grant, valid across all containers.
- Test the connectivity.
In another shell, start sqlplus and connect using the
credentials of the new user, to the root and to the PDB1 container. Then try to
switch containers with ALTER SESSION SET CONTAINER.
C##DBA1 can directly connect to the
container CDB2 and PDB1.
But SWITCHing containers is not
possible yet. That needs a GRANT SET CONTAINER.
- Still connected to the root as
SYSDBA, do a local GRANT, GRANTING the SET CONTAINER privilege to C##DBA1.
- Test the switching ability. Now,
the c##dba1 user can only switch from any container to the root, not vice
versa. Because of the local nature of the grant, it is only respected in
the root.
- From the root, as SYSDBA, now
do a common GRANT to allow the C##DBA1 to switch containers freely.
- Test the connectivity. Now the
C##DBA1 user is able to switch containers.
- From the PDB1 container, as SYSDBA, GRANT the SYSOPER privilege to the
user C##DBA1.
- Note that the grant is a local grant. It allows C##DBA1 to
start and stop the only PDB1 pluggable database. Check the ability of
C##DBA1 to alter the availability of PDB1.
- Check the access to other
containers – he cannot connect as SYSOPER to other containers.
Conclusion:
Due to
consolidation and large number of databases inside a multitenant container
database, role separation and access control become important. Hence Oracle
Database 12c introduced different classes of administrative users with
different job scopes and different levels of access to the database.
Common
users can be created and granted privileges to do administrative tasks across
multiple PDBs or specific sets of PDBs.
GRANTS are
local to the current container unless CONTAINER=ALL is specified.
