Oracle Database Commands and Practical Examples: Multitenant - Granting the Privileges as Common or Local on CDB and PDB

Multitenant - Granting the Privileges as Common or Local

Connect and check common user privilages

Check user privileges on connecting PDB database.

SQL> CONNECT system/system123@PLM2
Connected.
SQL> SELECT GRANTEE, PRIVILEGE, COMMON
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN ('C##USER1', 'LUSER2'); 2 3

GRANTEE PRIVILEGE COM
------------------ -------------- ---
LUSER2 CREATE SESSION NO
C##USER1 CREATE SESSION YES

Check privileges and Grant create table to common C##USER1 to all container user.

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 14 12:11:22 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> col grantee format a18
col privilege format a14
SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID FROM CDB_SYS_PRIVS WHERE GRANTEE IN ('C##USER1', 'LUSER2');SQL> SQL>

GRANTEE PRIVILEGE COM CON_ID
------------------ -------------- --- ----------
C##USER1 CREATE SESSION YES 1
LUSER2 CREATE SESSION NO 5
C##USER1 CREATE SESSION YES 5
C##USER1 CREATE SESSION YES 6
C##USER1 CREATE SESSION YES 8

SQL> CONNECT system/system123@PLM2
Connected.
SQL> SELECT GRANTEE, PRIVILEGE, COMMON
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN ('C##USER1', 'LUSER2'); 2 3

GRANTEE PRIVILEGE COM
------------------ -------------- ---
LUSER2 CREATE SESSION NO
C##USER1 CREATE SESSION YES

SQL> CONNECT system/system123
Connected.
SQL> GRANT CREATE TABLE, UNLIMITED TABLESPACE TO C##USER1 CONTAINER=ALL;

Grant succeeded.

SQL> col grantee format a12
col privilege format a30
SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID
FROM CDB_SYS_PRIVS
WHERE GRANTEE = 'C##USER1'
ORDER BY 1,2;SQL> SQL> 2 3 4

GRANTEE PRIVILEGE COM CON_ID
------------ ------------------------------ --- ----------
C##USER1 CREATE SESSION YES 8
C##USER1 CREATE SESSION YES 5
C##USER1 CREATE SESSION YES 1
C##USER1 CREATE SESSION YES 6
C##USER1 CREATE TABLE YES 8
C##USER1 CREATE TABLE YES 1
C##USER1 CREATE TABLE YES 5
C##USER1 CREATE TABLE YES 6
C##USER1 UNLIMITED TABLESPACE YES 6
C##USER1 UNLIMITED TABLESPACE YES 8
C##USER1 UNLIMITED TABLESPACE YES 1

GRANTEE PRIVILEGE COM CON_ID
------------ ------------------------------ --- ----------
C##USER1 UNLIMITED TABLESPACE YES 5

12 rows selected.

Check and grant sequence privileges to current container

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 14 12:38:25 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> GRANT CREATE SEQUENCE TO C##USER1 CONTAINER=CURRENT;

Grant succeeded.

SQL> SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID
FROM CDB_SYS_PRIVS
WHERE GRANTEE = 'C##USER1' AND PRIVILEGE ='CREATE SEQUENCE'; 2 3

GRANTEE
--------------------------------------------------------------------------------
PRIVILEGE COM CON_ID
---------------------------------------- --- ----------
C##USER1
CREATE SEQUENCE NO 1

Grant create synonym to PLM2 pluggable database

SQL> CONNECT system/system123@PLM2
Connected.
SQL> GRANT CREATE SYNONYM TO C##USER1 CONTAINER=CURRENT;

Grant succeeded.

SQL> col grantee format a18
SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID
FROM CDB_SYS_PRIVS
WHERE GRANTEE = 'C##USER1' AND PRIVILEGE ='CREATE SYNONYM';SQL> 2 3

GRANTEE PRIVILEGE COM CON_ID
------------------ -------------- --- ----------
C##USER1 CREATE SYNONYM NO 5

Grant unlimited tablespace to local user

SQL> CONNECT system/system123@PLM2
Connected.
SQL> GRANT UNLIMITED TABLESPACE TO LUSER2;

Grant succeeded.

SQL> col grantee format a18
SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID
FROM CDB_SYS_PRIVS
WHERE GRANTEE = 'LUSER2';SQL> 2 3

GRANTEE PRIVILEGE COM CON_ID
------------------ ---------------------------------------- --- ----------
LUSER2 UNLIMITED TABLESPACE NO 5
LUSER2 CREATE SESSION NO 5

SQL> CONNECT / as sysdba
Connected.
SQL> column username format a10
SQL> column default_attr format a7
column owner format a6
column object_name format a11
column all_containers format a3
column container_name format a10
column con_id format 999
set pages 100
set line 200
SELECT USERNAME, DEFAULT_ATTR, OWNER, OBJECT_NAME,
ALL_CONTAINERS, CONTAINER_NAME, CON_ID
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 FROM CDB_CONTAINER_DATA
4 WHERE username NOT IN ('GSMADMIN_INTERNAL', 'APPQOSSYS', 'DBSNMP')
ORDER BY USERNAME; 5

USERNAME DEFAULT OWNER OBJECT_NAME ALL CONTAINER_ CON_ID
---------- ------- ------ ----------- --- ---------- ------
DBSFWUSER Y Y 6
DBSFWUSER Y Y 1
DBSFWUSER Y Y 8
SYS Y Y 1
SYSBACKUP Y Y 1
SYSDG Y Y 1
SYSRAC Y Y 1
SYSTEM Y Y 6
SYSTEM Y Y 1
SYSTEM Y Y 8

10 rows selected.

SQL> CREATE USER C##USER2 IDENTIFIED BY oracle CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION, SET CONTAINER TO C##USER2 CONTAINER=ALL;

Grant succeeded.

SQL> GRANT SELECT ON sys.V_$SESSION TO C##USER2 CONTAINER=ALL;

Grant succeeded.

SQL>
sqlplus sys/system123@plm2 as sysdba

SQL> SELECT USERNAME, CON_ID FROM V_$SESSION
WHERE USERNAME IS NOT NULL AND USERNAME <> 'DBSNMP'
AND CON_ID = ( SELECT CON_ID FROM V$PDBS WHERE NAME='PLM2'); 2 3

USERNAME
--------------------------------------------------------------------------------
CON_ID
----------
SYS
5

SQL>CONNECT C##USER2/oracle

SQL> SELECT USERNAME, CON_ID FROM V$SESSION
WHERE USERNAME IS NOT NULL AND USERNAME <> 'DBSNMP'; 2

USERNAME
--------------------------------------------------------------------------------
CON_ID
----------
SYS
0

C##USER2
1

Oracle Database Commands and Practical Examples

HOME

Monday 24 August 2020

Multitenant - Granting the Privileges as Common or Local on CDB and PDB

No comments:

Post a Comment