Da mesma forma que temos os local e common users na arquitetura Multitenant, tamb\u00e9m temos o mesmo conceito para a quest\u00e3o das roles. Neste artigo vamos explorar um pouco sobre sua cria\u00e7\u00e3o e gerenciamento.<\/p>\n\n\n\n
Com a consulta seguinte, listamos todas as common e local roles tanto do container root quanto de seus PDBs:<\/p>\n\n\n
\n[oracle@quiasma admin]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Tue Apr 13 04:16:33 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> col role format a30\nSQL> SELECT ROLE, COMMON, CON_ID FROM CDB_ROLES ORDER BY ROLE, CON_ID;\n \nROLE COM CON_ID\n------------------------------ --- ----------\nADM_PARALLEL_EXECUTE_TASK YES 1\nADM_PARALLEL_EXECUTE_TASK YES 3\nAPPLICATION_TRACE_VIEWER YES 1\nAPPLICATION_TRACE_VIEWER YES 3\nAQ_ADMINISTRATOR_ROLE YES 1\nAQ_ADMINISTRATOR_ROLE YES 3\n \n...\n \nXS_NAMESPACE_ADMIN YES 1\nXS_NAMESPACE_ADMIN YES 3\nXS_SESSION_ADMIN YES 1\nXS_SESSION_ADMIN YES 3\n \n172 rows selected.\n<\/pre><\/div>\n\n\nListando todas as roles do root container. Lembrando que nele, s\u00f3 podemos criar common roles, pois a local roles s\u00f3 podem ser criadas nos PDBs:<\/p>\n\n\n
\nSQL> SELECT ROLE, COMMON FROM DBA_ROLES ORDER BY ROLE;\n \nROLE COM\n------------------------------ ---\nADM_PARALLEL_EXECUTE_TASK YES\nAPPLICATION_TRACE_VIEWER YES\nAQ_ADMINISTRATOR_ROLE YES\nAQ_USER_ROLE YES\nAUDIT_ADMIN YES\nAUDIT_VIEWER YES\n \n...\n \nXS_CACHE_ADMIN YES\nXS_CONNECT YES\nXS_NAMESPACE_ADMIN YES\nXS_SESSION_ADMIN YES\n \n86 rows selected.\n<\/pre><\/div>\n\n\nCriando uma common role:<\/p>\n\n\n
\nSQL> CREATE ROLE C##ROLE1 CONTAINER=ALL;\n \nRole created.\n<\/pre><\/div>\n\n\nTentando criar uma Local Role dentro do root container:<\/p>\n\n\n
\nSQL> SHOW CON_NAME CON_ID\n \nCON_NAME\n------------------------------\nCDB$ROOT\n \nCON_ID\n------------------------------\n1\nSQL> CREATE ROLE AQUINAO CONTAINER=CURRENT;\nCREATE ROLE AQUINAO CONTAINER=CURRENT\n*\nERROR at line 1:\nORA-65049: Creation of local user or role is not allowed in this container.\n<\/pre><\/div>\n\n\nDentro do PDB, listando suas roles:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> col role format a30\nSQL> SELECT ROLE, COMMON, CON_ID FROM CDB_ROLES ORDER BY 1;\n \nROLE COM CON_ID\n------------------------------ --- ----------\nADM_PARALLEL_EXECUTE_TASK YES 3\nAPPLICATION_TRACE_VIEWER YES 3\nAQ_ADMINISTRATOR_ROLE YES 3\n \n...\n \nXS_CACHE_ADMIN YES 3\nXS_CONNECT YES 3\nXS_NAMESPACE_ADMIN YES 3\nXS_SESSION_ADMIN YES 3\n \n87 rows selected.\n \nSQL> SELECT ROLE, COMMON FROM DBA_ROLES ORDER BY ROLE;\n \nROLE COM\n------------------------------ ---\nADM_PARALLEL_EXECUTE_TASK YES\nAPPLICATION_TRACE_VIEWER YES\nAQ_ADMINISTRATOR_ROLE YES\nAQ_USER_ROLE YES\n \n...\n \nXS_CACHE_ADMIN YES\nXS_CONNECT YES\nXS_NAMESPACE_ADMIN YES\nXS_SESSION_ADMIN YES\n \n87 rows selected.\n<\/pre><\/div>\n\n\nCriando uma local role dentro do PDB:<\/p>\n\n\n
\nSQL> CREATE ROLE LOCALROLE CONTAINER=CURRENT;\n \nRole created.\n \nSQL> SELECT ROLE FROM DBA_ROLES WHERE COMMON ='NO' ORDER BY ROLE;\n \nROLE\n------------------------------\nLOCALROLE\n<\/pre><\/div>\n\n\nNa situa\u00e7\u00e3o abaixo, vamos conceder a common role para o common user dentro do root container. Este privil\u00e9gio ser\u00e1 aplicado apenas no root:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=CDB$ROOT;\n \nSession altered.\n \nSQL> GRANT C##ROLE1 TO C##BRUNO;\n \nGrant succeeded.\n<\/pre><\/div>\n\n\nChecando atrav\u00e9s da consulta abaixo que fizemos o grant localmente no root container:<\/p>\n\n\n
\nSQL> col grantee format A16\nSQL> col granted_role format A16\nSQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##BRUNO';\n \nGRANTEE GRANTED_ROLE COM CON_ID\n---------------- ---------------- --- ----------\nC##BRUNO C##ROLE1 NO 1\n \nSQL> CONN C##BRUNO\/oracle\nConnected.\nSQL> SHOW USER;\nUSER is "C##BRUNO"\nSQL> SELECT * FROM SESSION_ROLES;\n \nROLE\n------------------------------\nC##ROLE1\n<\/pre><\/div>\n\n\nIsso aconteceu pois o par\u00e2metro CONTAINER ficou interpretado como CURRENT. Caso queiramos conceder este mesmo privil\u00e9gio para todos os PDBs, basta usarmos o exemplo seguinte:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> GRANT C##ROLE1 TO C##BRUNO CONTAINER=ALL;\n \nGrant succeeded.\n \nSQL> col grantee format A16\nSQL> col granted_role format A16\nSQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##BRUNO';\n \nGRANTEE GRANTED_ROLE COM CON_ID\n---------------- ---------------- --- ----------\nC##BRUNO C##ROLE1 NO 1\nC##BRUNO C##ROLE1 YES 1\nC##BRUNO C##ROLE1 YES 3\n \nSQL> conn C##BRUNO\/oracle\nConnected.\nSQL> SELECT * FROM SESSION_ROLES;\n \nROLE\n------------------------------\nC##ROLE1\n<\/pre><\/div>\n\n\nConectando com o common user em um PDB e checando que a common role foi concedida:<\/p>\n\n\n
\nSQL> CONN C##BRUNO\/oracle@HIPOFISE1\nConnected.\nSQL> SELECT * FROM SESSION_ROLES;\n \nROLE\n------------------------------\nC##ROLE1\n<\/pre><\/div>\n\n\nRealizando o revoke da common role do common user:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> REVOKE C##ROLE1 FROM C##BRUNO CONTAINER=ALL;\n \nRevoke succeeded.\n<\/pre><\/div>\n\n\nPodemos perceber que apenas o privil\u00e9gio common foi revogado. O local ainda permanece:<\/p>\n\n\n
\nSQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##BRUNO';\n \nGRANTEE GRANTED_ROLE COM CON_ID\n---------------- ---------------- --- ----------\nC##BRUNO C##ROLE1 NO 1\n<\/pre><\/div>\n\n\nRealizando o local revoke:<\/p>\n\n\n
\nSQL> REVOKE C##ROLE1 FROM C##BRUNO;\n \nRevoke succeeded.\n \nSQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##BRUNO';\n \nno rows selected\n<\/pre><\/div>\n\n\nValidando:<\/p>\n\n\n
\nSQL> conn C##BRUNO\/oracle\nConnected.\nSQL> SELECT * FROM SESSION_ROLES;\n \nno rows selected\n \nSQL> conn C##BRUNO\/oracle@HIPOFISE1\nConnected.\nSQL> SELECT * FROM SESSION_ROLES;\n \nno rows selected\n<\/pre><\/div>\n\n\nAgora vamos conceder uma common role para um local user (apenas para aprendizado, pois o ideal \u00e9 evitarmos essa pr\u00e1tica, pois pode trazer muita confus\u00e3o em um ambiente normal):<\/p>\n\n\n
\nSQL> conn system\/oracle@HIPOFISE1\nConnected.\nSQL> GRANT C##ROLE1 TO LOCALUSER;\n \nGrant succeeded.\n \nSQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='LOCALUSER';\n \nGRANTEE GRANTED_ROLE COM CON_ID\n---------------- ---------------- --- ----------\nLOCALUSER C##ROLE1 NO 3\n \nSQL> CONN LOCALUSER\/oracle@HIPOFISE1\nConnected.\nSQL> SELECT * FROM SESSION_ROLES;\n \nROLE\n------------------------------\nC##ROLE1\n<\/pre><\/div>\n\n\nPor \u00faltimo, vamos conceder uma local role para um local user:<\/p>\n\n\n
\nSQL> CONNECT SYSTEM\/oracle@HIPOFISE1\nConnected.\nSQL> GRANT LOCALROLE TO LOCALUSER;\n \nGrant succeeded.\n \nSQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='LOCALUSER';\n \nGRANTEE GRANTED_ROLE COM CON_ID\n---------------- ---------------- --- ----------\nLOCALUSER C##ROLE1 NO 3\nLOCALUSER LOCALROLE NO 3\n \nSQL> CONNECT LOCALUSER\/oracle@HIPOFISE1\nConnected.\nSQL> SELECT * FROM SESSION_ROLES;\n \nROLE\n------------------------------\nC##ROLE1\nLOCALROLE\n<\/pre><\/div>\n\n\nObs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
Da mesma forma que temos os local e common users na arquitetura Multitenant, tamb\u00e9m temos o mesmo conceito para a quest\u00e3o das roles. Neste artigo vamos explorar um pouco sobre sua cria\u00e7\u00e3o e gerenciamento. Com a consulta seguinte, listamos todas as common e local roles tanto do container root quanto de seus PDBs: Listando todas […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-3749","post","type-post","status-publish","format-standard","hentry","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3749","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=3749"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3749\/revisions"}],"predecessor-version":[{"id":9174,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3749\/revisions\/9174"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3749"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3749"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3749"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}