Da mesma forma que temos os local e common users na arquitetura Multitenant, também temos o mesmo conceito para a questão das roles. Neste artigo vamos explorar um pouco sobre sua criação e gerenciamento.
Com a consulta seguinte, listamos todas as common e local roles tanto do container root quanto de seus PDBs:
[oracle@quiasma admin]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Apr 13 04:16:33 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> col role format a30
SQL> SELECT ROLE, COMMON, CON_ID FROM CDB_ROLES ORDER BY ROLE, CON_ID;
ROLE COM CON_ID
------------------------------ --- ----------
ADM_PARALLEL_EXECUTE_TASK YES 1
ADM_PARALLEL_EXECUTE_TASK YES 3
APPLICATION_TRACE_VIEWER YES 1
APPLICATION_TRACE_VIEWER YES 3
AQ_ADMINISTRATOR_ROLE YES 1
AQ_ADMINISTRATOR_ROLE YES 3
...
XS_NAMESPACE_ADMIN YES 1
XS_NAMESPACE_ADMIN YES 3
XS_SESSION_ADMIN YES 1
XS_SESSION_ADMIN YES 3
172 rows selected.
Listando todas as roles do root container. Lembrando que nele, só podemos criar common roles, pois a local roles só podem ser criadas nos PDBs:
SQL> SELECT ROLE, COMMON FROM DBA_ROLES ORDER BY ROLE;
ROLE COM
------------------------------ ---
ADM_PARALLEL_EXECUTE_TASK YES
APPLICATION_TRACE_VIEWER YES
AQ_ADMINISTRATOR_ROLE YES
AQ_USER_ROLE YES
AUDIT_ADMIN YES
AUDIT_VIEWER YES
...
XS_CACHE_ADMIN YES
XS_CONNECT YES
XS_NAMESPACE_ADMIN YES
XS_SESSION_ADMIN YES
86 rows selected.
Criando uma common role:
SQL> CREATE ROLE C##ROLE1 CONTAINER=ALL;
Role created.
Tentando criar uma Local Role dentro do root container:
SQL> SHOW CON_NAME CON_ID
CON_NAME
------------------------------
CDB$ROOT
CON_ID
------------------------------
1
SQL> CREATE ROLE AQUINAO CONTAINER=CURRENT;
CREATE ROLE AQUINAO CONTAINER=CURRENT
*
ERROR at line 1:
ORA-65049: Creation of local user or role is not allowed in this container.
Dentro do PDB, listando suas roles:
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
Session altered.
SQL> col role format a30
SQL> SELECT ROLE, COMMON, CON_ID FROM CDB_ROLES ORDER BY 1;
ROLE COM CON_ID
------------------------------ --- ----------
ADM_PARALLEL_EXECUTE_TASK YES 3
APPLICATION_TRACE_VIEWER YES 3
AQ_ADMINISTRATOR_ROLE YES 3
...
XS_CACHE_ADMIN YES 3
XS_CONNECT YES 3
XS_NAMESPACE_ADMIN YES 3
XS_SESSION_ADMIN YES 3
87 rows selected.
SQL> SELECT ROLE, COMMON FROM DBA_ROLES ORDER BY ROLE;
ROLE COM
------------------------------ ---
ADM_PARALLEL_EXECUTE_TASK YES
APPLICATION_TRACE_VIEWER YES
AQ_ADMINISTRATOR_ROLE YES
AQ_USER_ROLE YES
...
XS_CACHE_ADMIN YES
XS_CONNECT YES
XS_NAMESPACE_ADMIN YES
XS_SESSION_ADMIN YES
87 rows selected.
Criando uma local role dentro do PDB:
SQL> CREATE ROLE LOCALROLE CONTAINER=CURRENT;
Role created.
SQL> SELECT ROLE FROM DBA_ROLES WHERE COMMON ='NO' ORDER BY ROLE;
ROLE
------------------------------
LOCALROLE
Na situação abaixo, vamos conceder a common role para o common user dentro do root container. Este privilégio será aplicado apenas no root:
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
Session altered.
SQL> GRANT C##ROLE1 TO C##BRUNO;
Grant succeeded.
Checando através da consulta abaixo que fizemos o grant localmente no root container:
SQL> col grantee format A16
SQL> col granted_role format A16
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##BRUNO';
GRANTEE GRANTED_ROLE COM CON_ID
---------------- ---------------- --- ----------
C##BRUNO C##ROLE1 NO 1
SQL> CONN C##BRUNO/oracle
Connected.
SQL> SHOW USER;
USER is "C##BRUNO"
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
C##ROLE1
Isso aconteceu pois o parâmetro CONTAINER ficou interpretado como CURRENT. Caso queiramos conceder este mesmo privilégio para todos os PDBs, basta usarmos o exemplo seguinte:
SQL> conn / as sysdba
Connected.
SQL> GRANT C##ROLE1 TO C##BRUNO CONTAINER=ALL;
Grant succeeded.
SQL> col grantee format A16
SQL> col granted_role format A16
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##BRUNO';
GRANTEE GRANTED_ROLE COM CON_ID
---------------- ---------------- --- ----------
C##BRUNO C##ROLE1 NO 1
C##BRUNO C##ROLE1 YES 1
C##BRUNO C##ROLE1 YES 3
SQL> conn C##BRUNO/oracle
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
C##ROLE1
Conectando com o common user em um PDB e checando que a common role foi concedida:
SQL> CONN C##BRUNO/oracle@HIPOFISE1
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
C##ROLE1
Realizando o revoke da common role do common user:
SQL> conn / as sysdba
Connected.
SQL> REVOKE C##ROLE1 FROM C##BRUNO CONTAINER=ALL;
Revoke succeeded.
Podemos perceber que apenas o privilégio common foi revogado. O local ainda permanece:
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##BRUNO';
GRANTEE GRANTED_ROLE COM CON_ID
---------------- ---------------- --- ----------
C##BRUNO C##ROLE1 NO 1
Realizando o local revoke:
SQL> REVOKE C##ROLE1 FROM C##BRUNO;
Revoke succeeded.
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##BRUNO';
no rows selected
Validando:
SQL> conn C##BRUNO/oracle
Connected.
SQL> SELECT * FROM SESSION_ROLES;
no rows selected
SQL> conn C##BRUNO/oracle@HIPOFISE1
Connected.
SQL> SELECT * FROM SESSION_ROLES;
no rows selected
Agora vamos conceder uma common role para um local user (apenas para aprendizado, pois o ideal é evitarmos essa prática, pois pode trazer muita confusão em um ambiente normal):
SQL> conn system/oracle@HIPOFISE1
Connected.
SQL> GRANT C##ROLE1 TO LOCALUSER;
Grant succeeded.
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='LOCALUSER';
GRANTEE GRANTED_ROLE COM CON_ID
---------------- ---------------- --- ----------
LOCALUSER C##ROLE1 NO 3
SQL> CONN LOCALUSER/oracle@HIPOFISE1
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
C##ROLE1
Por último, vamos conceder uma local role para um local user:
SQL> CONNECT SYSTEM/oracle@HIPOFISE1
Connected.
SQL> GRANT LOCALROLE TO LOCALUSER;
Grant succeeded.
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='LOCALUSER';
GRANTEE GRANTED_ROLE COM CON_ID
---------------- ---------------- --- ----------
LOCALUSER C##ROLE1 NO 3
LOCALUSER LOCALROLE NO 3
SQL> CONNECT LOCALUSER/oracle@HIPOFISE1
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
C##ROLE1
LOCALROLE
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish