ORACLE12C Multi-tenant Manage users, roles, permissions

In oracle12c, the management of user rights is slightly different from the traditional Oracle single database environment. There are two types of users in a multitenant environment.

①: Common users (Common user): The user exists in all containers (root and all Pdb).

②: Local User: The user exists only in a specific PDB. The same user name can exist in multiple PDB creation, but there is no relationship between them.

Similarly, there are two types of roles:

①: Common role (Common role): This role is in all containers (root and all Pdb).

②: Local role: The role exists only in a specific PDB. You can create the same role names in multiple PDB, but there is no relationship between them.

Some DDL statements are expanded so that they can be directed to the current container or to the container clause of all containers. Its use will be demonstrated in the following sections.

Attention:

When creating a public user in CDB, the same user is also created in PDBs. If the GRANT command in CDB is empowered, if the Container=all is not specified, the empowerment is only effective in the CDB and does not take effect in the PDB, and the user needs to be able to access the PDB and need to switch to the PDB to re-assign the power. If the Container=all is assigned, the assignment takes effect in the CDB and also in the PDB.

1. In CDB, Container=all is not specified when assigning to User:

Sql> show Con_name;

Con_name

------------------------------

Cdb$root

sql> Create User C # #zhang identified by Zhang;

Sql> Grant Create session to C # #zhang; --Empower the user, this time to open another window when using the user login PDB is not authorized, as follows:

[Email protected] ~]$ sqlplus C # #zhang/[email protected]/testpdb

Sql*plus:release 12.2.0.1.0 Production on Tue Jul 18 15:15:51 2017

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

ERROR:

Ora-01045:user C # #ZHANG lacks CREATE SESSION privilege; Logon denied

----switch to the PDB and empower the user to log in:

Sql> alter session set CONTAINER=TESTPDB;

Sql> Grant Create session to C # #zhang;

2, in the CDB, the user is assigned the right to specify Container=all:

sql> Create User C # #zhang1 identified by Zhang;

Sql> Grant Create session to C # #zhang1 Container=all;

3. Create a public role:

Sql> Show Con_name

Con_name

------------------------------

Cdb$root

sql> Create role C # #role; ---create a role

Sql> Grant Select on Dba_objects to C # #role Container=all; --Add permissions to the role

Sql> Grant C # #role to C # #zhang1 Container=all; --Assigning a role to a public user

Sql> alter session set CONTAINER=TESTPDB; ---switch to PDB

Sql> Grant C # #role to admin; ---can also give this role to local users in the PDB

4. Local role

Local roles are created in a similar manner to the pre-12 C database. Each PDB can have a matching name because the local scope is limited to the role of the current PDB.

The following conditions must be met.

Conditions:

①: You must be connected to a user who has the Create role permission.

②: If you connect to a public user, the container must be set to local PDB.

③: The role name for the local role does not have to be prefixed with "c##" or "c##".

④: The role name must be unique within the PDB.

⑤: Local roles can be assigned to public users (scoped to the PDB operation, without affecting CDB permissions) or Local users. Such as:

Sql> show Con_name;

Con_name

------------------------------

Testpdb

sql> Create role Pdb_role; ---create a role

Sql> Grant Select on Dba_tables to Pdb_role; --Add permissions to Roles

Sql> Grant Pdb_role to C # #zhang; --Assigning roles to public users

Sql> Grant Pdb_role to admin; ---Assigning roles to Local Users

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1949774

ORACLE12C Multi-tenant Manage users, roles, permissions