Na arquitetura Multitenant, tivemos a cria\u00e7\u00e3o do conceito de common users e local users. O primeiro nos possibilita criar um usu\u00e1rio no CDB$ROOT e em todos os PDBs do container, al\u00e9m de sua cria\u00e7\u00e3o autom\u00e1tica nos PDBs gerados posteriormente. Sua identifica\u00e7\u00e3o padr\u00e3o possui o prefixo “C##”, que o distingue do local user que n\u00e3o possui nenhum prefixo. Este valor \u00e9 definido no par\u00e2metro “common_user_prefix”, conforme abaixo:<\/p>\n\n\n
\n[oracle@quiasma ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 12 05:29:08 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> SHO PARAMETER common_user_prefix\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\ncommon_user_prefix string C##\nSQL>\n<\/pre><\/div>\n\n\nApenas 2 coment\u00e1rios sofre este prefixo: o melhor dos mundos \u00e9 mantermos este valor, para n\u00e3o criarmos confus\u00e3o ao administrar o ambiente. E caso o recurso de autentica\u00e7\u00e3o externa seja utilizada, devemos equalizar o valor do prefixo com o par\u00e2metro “OS_AUTHENT_PREFIX” (que tem valor padr\u00e3o como OPS$):<\/p>\n\n\n
\nSQL> SHO PARAMETER OS_AUTHENT_PREFIX\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\nos_authent_prefix string ops$\n<\/pre><\/div>\n\n\nO comando abaixo nos ajuda a ver os common users do container root:<\/p>\n\n\n
\nSQL> SHOW CON_NAME CON_ID;\n \nCON_NAME\n------------------------------\nCDB$ROOT\n \nCON_ID\n------------------------------\n1\nSQL> set pagesize 80\nSQL> col username format a30\nSQL> SELECT DISTINCT USERNAME, ORACLE_MAINTAINED FROM CDB_USERS WHERE COMMON='YES' ORDER BY 1;\n \nUSERNAME O\n------------------------------ -\nANONYMOUS Y\nAPPQOSSYS Y\nAUDSYS Y\nCTXSYS Y\nDBSFWUSER Y\nDBSNMP Y\nDIP Y\nDVF Y\nDVSYS Y\nGGSYS Y\nGSMADMIN_INTERNAL Y\nGSMCATUSER Y\nGSMUSER Y\nLBACSYS Y\nMDDATA Y\nMDSYS Y\nOJVMSYS Y\nOLAPSYS Y\nORACLE_OCM Y\nORDDATA Y\nORDPLUGINS Y\nORDSYS Y\nOUTLN Y\nREMOTE_SCHEDULER_AGENT Y\nSI_INFORMTN_SCHEMA Y\nSYS Y\nSYS$UMF Y\nSYSBACKUP Y\nSYSDG Y\nSYSKM Y\nSYSRAC Y\nSYSTEM Y\nWMSYS Y\nXDB Y\nXS$NULL Y\n \n35 rows selected.\n<\/pre><\/div>\n\n\nCriando um common user (utilizando o par\u00e2metro CONTAINER=ALL) e concedendo privil\u00e9gio de create session:<\/p>\n\n\n
\nSQL> CREATE USER C##BRUNO IDENTIFIED BY oracle CONTAINER=ALL;\n \nUser created.\n \nSQL> GRANT CREATE SESSION TO C##BRUNO CONTAINER=ALL;\n \nGrant succeeded.\n<\/pre><\/div>\n\n\nPodemos ver que este container root n\u00e3o possui nenhum PDB, al\u00e9m do seed:<\/p>\n\n\n
\nSQL> select name,open_mode from v$pdbs;\n \nNAME\n--------------------------------------------------------------------------------\nOPEN_MODE\n----------\nPDB$SEED\nREAD ONLY\n<\/pre><\/div>\n\n\nCriando um novo PDB a partir do seed:<\/p>\n\n\n
\nSQL> CREATE PLUGGABLE DATABASE HIPOFISE1\nADMIN USER BSS IDENTIFIED BY oracle\nDEFAULT TABLESPACE USERS\nSTORAGE (MAXSIZE 2G); 2 3 4\n \nPluggable database created.\n \nSQL> ALTER PLUGGABLE DATABASE HIPOFISE1 OPEN;\n \nPluggable database altered.\n \nSQL> ALTER PLUGGABLE DATABASE HIPOFISE1 SAVE STATE;\n \nPluggable database altered.\n<\/pre><\/div>\n\n\nConectando no container root com o common user criado:<\/p>\n\n\n
\n[oracle@quiasma admin]$ sqlplus C##BRUNO\/oracle@TALAMO\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 12 05:43:58 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> SHOW USER;\nUSER is "C##BRUNO"\n<\/pre><\/div>\n\n\nTestando a conex\u00e3o com o PDB HIPOFISE1, que foi criado posteriormente ao common user:<\/p>\n\n\n
\n[oracle@quiasma admin]$ sqlplus C##BRUNO\/oracle@HIPOFISE1\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 12 05:45:46 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \nLast Successful login time: Mon Apr 12 2021 05:43:58 -03:00\n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> SHOW USER;\nUSER is "C##BRUNO"\n<\/pre><\/div>\n\n\nConectado no CDB$ROOT, podemos identificar que n\u00e3o \u00e9 poss\u00edvel criar um LOCAL USER nele:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> SHO CON_ID CON_nAME\n \nCON_ID\n------------------------------\n1\n \nCON_NAME\n------------------------------\nCDB$ROOT\nSQL> CREATE USER SPFC IDENTIFIED BY oracle CONTAINER=CURRENT;\nCREATE USER SPFC IDENTIFIED BY oracle 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\nCriando um novo Common user:<\/p>\n\n\n
\nSQL> CREATE USER C##BRUNO2 IDENTIFIED BY oracle CONTAINER=ALL;\n \nUser created.\n<\/pre><\/div>\n\n\nNo momento de conceder privil\u00e9gios, caso n\u00e3o especifiquemos o CONTAINER=ALL, o Oracle considerar\u00e1 o valor CURRENT, ou seja, o privil\u00e9gio ser\u00e1 a n\u00edvel local:<\/p>\n\n\n
\nSQL> GRANT CREATE SESSION TO C##BRUNO2;\n \nGrant succeeded.\n<\/pre><\/div>\n\n\nConectando no CDB$ROOT com o novo usu\u00e1rio:<\/p>\n\n\n
\n[oracle@quiasma admin]$ sqlplus C##BRUNO2\/oracle@TALAMO\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 12 05:50:46 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> SHOW USER;\nUSER is "C##BRUNO2"\nSQL>\n<\/pre><\/div>\n\n\nPor\u00e9m ao tentar conectar no PDB com o mesmo usu\u00e1rio, n\u00e3o nos \u00e9 permitido pois o privil\u00e9gio de criar sess\u00e3o foi dado localmente no CDB$ROOT:<\/p>\n\n\n
\n[oracle@quiasma admin]$ sqlplus C##BRUNO2\/oracle@HIPOFISE1\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 12 05:51:30 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \nERROR:\nORA-01045: user C##BRUNO2 lacks CREATE SESSION privilege; logon denied\n \n \nEnter user-name:\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":"
Na arquitetura Multitenant, tivemos a cria\u00e7\u00e3o do conceito de common users e local users. O primeiro nos possibilita criar um usu\u00e1rio no CDB$ROOT e em todos os PDBs do container, al\u00e9m de sua cria\u00e7\u00e3o autom\u00e1tica nos PDBs gerados posteriormente. Sua identifica\u00e7\u00e3o padr\u00e3o possui o prefixo “C##”, que o distingue do local user que n\u00e3o possui […]<\/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-3719","post","type-post","status-publish","format-standard","hentry","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3719","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=3719"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3719\/revisions"}],"predecessor-version":[{"id":9176,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3719\/revisions\/9176"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3719"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3719"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3719"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}