Shulou(Shulou.com)06/01 Report--

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 multi-tenant environment.

①: common user (Common User): this user exists in all containers (root and all Pdb).

②: local user (Local User): the user exists only in a specific PDB. There can be multiple Pdb creation in the same user name, but there is no relationship between them.

Similarly, there are two types of roles: such as

①: common role (Common Role): this role is in all containers (root and all Pdb).

②: local role (Local Role): this role exists only in a specific PDB. You can create the same role name in multiple Pdb, but there is no relationship between them.

Some DDL statements are extended 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.

Note:

When you create a public user in cdb, the same user is created in pdbs. If the GRANT command is authorized under CDB, and if container=all is not specified, the authorization will only take effect in CDB, not in PDB. To be able to access PDB, this user needs to switch to pdb to re-authorize. If container=all is specified when authoring, the authorization takes effect in CDB as well as in PDB.

1. In CDB, container=all is not specified when assigning rights to users:

SQL > show con_name

CON_NAME

-

CDB$ROOT

SQL > create user c##zhang identified by zhang

SQL > grant create session to clockzhang;-- Grant the right to the user. When you open another window, you do not have permission to log in to pdb with this user, as shown below:

[oracle@localhost ~] $sqlplus c##zhang/zhang@192.168.2.100/testpdb

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 18 15:15:51 2017

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

ERROR:

ORA-01045: user C##ZHANG lacks CREATE SESSION privilege; logon denied

-switch to pdb, empower the user and log in:

SQL > alter session set container=testpdb

SQL > grant create session to c##zhang

2. In CDB, specify container=all when assigning rights to the user:

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 caterpillar;-create a role

SQL > grant select on dba_objects to c##role container=all;-- add permissions to this role

SQL > grant c##role to c##zhang1 container=all;-- assign roles to public users

SQL > alter session set container=testpdb;-switch to pdb

SQL > grant c##role to admin;-you can also assign this role to local users in pdb

4. Local role

Local roles are created in a similar manner to the pre-12 c database Each PDB can have a name that matches, because the local scope is limited to the current PDB role.

The following conditions must be met.

Conditions:

①: you must connect to a user with CREATE ROLE privileges.

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

③: the role name is a local role and does not have to be prefixed with "roles #" or "roles #".

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

⑤: local roles can be granted to public users (limited to operations within pdb and do not affect CDB permissions) or local users. Such as:

SQL > show con_name

CON_NAME

-

TESTPDB

SQL > create role pdb_role;-create roles

SQL > grant select on dba_tables to pdb_role;-- add permissions to roles

SQL > grant pdb_role to caterpillar Zhang;-- assign roles to public users

SQL > grant pdb_role to admin;-assign roles to local users

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.