How to control the access of common users to pdb information in cdb

How to control the access of common users to pdb information in cdb

Purpose:
Study the permissions required for common users to switch between different PDBs;
Study how to restrict a common user to access only information corresponding to a certain number of PDBs when querying container_data_objects on cdb or to access information corresponding to a specific container_data_object., common users generally have relatively large permissions, but sometimes we do not want common users to access some pdb information through the views in cdb.

First, explain the concept of container_data_object: Table or view corresponding to container_data = 'y' in Dba_table and dba_views. Because these views contain information from different PDBs, they are called container_data_object.

1. Only the common user can use alter session set container = PDBX to switch between PDBs, provided that the common user must have at least the create session and set container permissions in these PDBs:

--- Create a common user in cdb and grant the create session permission

SQL> show con_name

 

CON_NAME

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

CDB $ ROOT

SQL> create user c # guser2 identified by 773946 container = all;

 

User created.

SQL> grant create session to c # guser2;

 

Grant succeeded.

 

 

--- Log on to cdb as the newly created c # guser2 user, set container to orapdba, and prompt that the permission is insufficient.

Oracle @ ora12c1:/home/oracle> sqlplus c # guser2/773946

 

SQL * Plus: Release 12.1.0.1.0 Production on Thu Dec 12 21:22:44 2013

 

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

 

 

Connected:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

 

SQL> alter session set container = orapdba;

ERROR:

ORA-01031: insufficient privileges

 

-- [C1]-grant set iner permission to c # guser2 in orapdba

SQL> show con_name

 

CON_NAME

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

ORAPDBA

 

SQL> grant set container, create session to c # guser2;

 

Grant succeeded.

 

--- Set container to orapdba successfully

SQL> show con_name

 

CON_NAME

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

CDB $ ROOT

 

SQL> alter session set container = orapdba;

 

Session altered.

 

Conclusion: To switch between different PDBs, the common user must have the create session and set iner permissions on these PDBs, the simplest way is to execute grant create session on cdb, set container to c # guser2 container = all for global authorization.

 

2. Use container_data_clause in alter user to control the container_data_objects access permissions:

--- Global authorization c # guser2 Query System View Permissions

SQL> grant select_catalog_role to c # guser2 container = all;

 

Grant succeeded.

 

--- [C2] log on to cdb $ root with c # guser2 to query the content of cdb_data_files and v $ session.

SQL> show user

USER is "C # GUSER2"

 

SQL> show con_name

 

CON_NAME

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

CDB $ ROOT

SQL> select distinct con_id from v $ session;

 

CON_ID

----------

1

0

 

SQL> select distinct con_id from cdb_data_files;

 

CON_ID

----------

1

 

--- Open to c # guser2 users to query information on a specific pdb

SQL> show user

USER is "SYS"

SQL> show con_name

 

CON_NAME

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

CDB $ ROOT

 

SQL> alter user c # guser2 set container_data = (CDB $ root [C3], orapdba, orapdbb) container = current;

 

User altered.

 

--- Query cdb_container_data. You can see that c # guser2 has the permission to query container_data_object on some pdb instances.

Select * from CDB_container_data where username = 'C # guser2 ';

Grant succeeded.

--- Log on to cdb $ root again with c # guser2 to query the content of cdb_data_files and v $ session. You can find information about other pdb instances except 0 and 1.
 

 

SQL> select distinct con_id from v $ session;

 

CON_ID

----------

1

4

3

0

 

SQL> select distinct con_id from cdb_data_files;

 

CON_ID

----------

4

3

1

 

--- For the v $ session view, c # guser2 can only access orapdba-related information.

SQL> alter user c # guser2 set container_data = (CDB $ root, orapdba) for v $ session container = current;

 

--- Cdb_container_data adds two more lines of control information based on specific objects.

Select * from CDB_container_data where username = 'C # guser2'

--- Log on to the Root user using c # guser2 to query the content of the cdb_data_files and v $ session Views. You can still find the con_id = 4, that is, the record of orapdbb.

SQL> select distinct con_id from v $ session;

 

CON_ID

----------

1

4

3

0

SQL> select distinct con_id from cdb_data_files;

 

CON_ID

----------

4

3

1

 

--- The reason is that the red part still takes effect.

--- Remove the access permission for orapdbb

SQL> alter user c # guser2 remove container_data = (orapdbb) container = current;

 

User altered.

 

--- Log on to the Root user using c # guser2 to query the content of the cdb_data_files and v $ session Views.

Con_id = 4 records

SQL> select distinct con_id from v $ session;

 

CON_ID

----------

1

3

0

 

SQL> select distinct con_id from cdb_data_files;

 

CON_ID

----------

3

1

 

--- Relax the v $ session view restrictions so that c # guser2 can also access orapdbb-related information

SQL> alter user c # guser2 add container_data = (orapdbb) for v $ session

Container = current;

 

User altered.

 

--- Log on to the Root user using c # guser2 to query the v $ session view and view the records with con_id = 4,

There is still no con_id = 4 record in cdb_data_files

User altered.

 
[C1] If the create session is not authorized here, the set iner can be set successfully. It is better to attach the create session together in actual operations.

[C2] Because c # guser2 does not set container_data, that is, it is equivalent to setting set container_data = default. Therefore, only records with con_id = 0 and con_id = 1 can be seen, 0 represents the entire CDB, and 1 represents CDB $ ROOT

[C3] must contain Root, otherwise a ORA-65057 error will be reported

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian