Tudo o que você precisa saber sobre o Common e o Local no Oracle Database 12c

Por OraWorld,
Postado em Junho 2014

Sejam bem vindos mais uma vez caros colegas da Comunidade Oracle América Latina, é com muito gosto que trazemos até vocês mais um artigo de qualidade, com o qual podemos aprender o que a versão 12c nos oferece de mais eficaz e eficiente, com exemplos claros e consistentes, com teoría fundamentada e com muitas experiencias á compartilhar. Como bem sabem, a arquitetura de uma base de dados na versão Oracle 12c sofreram muitas melhorias, agora já podemos fala rem “Pluggable Database”, e de “Cointainer” e de “SEED”. Para aqueles que já estão familiarizados com esses novos conceitos de “multitanent” já estão cientes de que uma base de dados do tipo “CDB” pode conter de zero á 253 “Pluggable Databases” incluindo as “SEED”.A base de dados que abriga todas as outras bases de dados internas denomina-se “CDB$ROOT” e as bases de dados internas denominam-se “Pluggable Databases” ou “PDB”, o “PDB$SEED” também é uma PDB, portanto a diferença é que só poderá estar aberta em modo leitura e será utilizada como um modelo para a criação de novas PDB’s.

Com essa nova arquitetura surgem as dúvidas de cómo serão administrados os usuários, roles e respectivos privilégios. Bem, é esse o objetivo deste artigo, demonstrar a maioria dos casos possiveis de usuários e roles; e seus respectivos privilégios, tanto para objetos como para sistema. Agora , na versão 12c, existem duas grandes categorías :

  • LOCAL
  • COMMON

Características:

  • Os usuários podem ser locais ou comuns
  • As roles podem ser criadas como locais ou comuns.
  • Os privilégios podem ser atribuidos localmente ou em comum.
  • Cada PDB contém seu próprio dicionário de dados.
  • Os usuários comuns se registram no dicionário do "CDB$ROOT".
  • Os usuários locais se registram no dicionário da PDB.
  • Alguns dados dentro de um PDB são extraidos do dicionário de dados do "CDB$ROOT", isto é realizado através de ponteiros.
  • O parâmetro _common_user_prefix controla o prefixo dos usuários comuns. Não se deve alterar este parâmetro sem o suporte da Oracle.

common-local-oracle

Descrição da Imagem:

  • C##DBA como SYS estão presentes no "CDB$ROOT", e na PDB "hrpdb" e na PDB "salespdb", isto se debe ao fato de os dois usuários serem do tipo comum.
  • Existem dois usuários com o nome "hr", um para a PDB "hrpdb" e outro para a PDB "salespdb", embora os dois usuários tenham o mesmo nome, são totalmente diferentes, pois são locais.
  • Existem dois usuários com o nome “rep”, um para a PDB “hrpdb” e outro para a “salespdb”, embora os dois usuários tenham o mesmo nome são totalmente diferentes pois são locais.
  • Os usuários "SYS" e "C##DBA" tem schemas diferentes em cada container.

Tudo que devemos saber sobre “LOCAL”:

  1. Os usuários criados como locais funcionam como usuparios em versões anteriores. Podem realizar tarefas somente dentro da PDB onde foram criados. Não podem conectar-se a nenhuma outra PDB embora tenham privilégio “SET CONTAINER”.
    2.   SQL> select privilege from user_sys_privs;

					PRIVILEGE
					---------------------------------------- 
					SET CONTAINER CREATE SESSION SQL> 
					alter session set container=pdb2; ERROR:
					ORA-01031: insufficient privileges
  2. Um usuário local pode criar usuários locais, mas não usuários comuns.
    3.   SQL> select privilege from user_sys_privs;

					PRIVILEGE 
					---------------------------------------- 
					SET CONTAINER CREATE SESSION CREATE USER SQL> 
					create user LOCALUSER2 identified by "localuser2"
					container=current; User created. 
					SQL> create user LOCALUSER3 identified by "localuser3" container=all;
					create user LOCALUSER3 identified by "localuser3"
					container=all * ERROR at line 1: ORA-65050: Common DDLs only allowed in CDB$ROOT
  3. Os usuários locais só podem ter privilégios concedidos localmente.
    4.   SQL> grant connect to localuser container=all; grant connect to

					localuser container=all * ERROR at line 1: ORA-65030: one may not grant a Common Privilege
					to a Local User or Role SQL> grant connect to localuser container=current; Grant succeeded.
  4. Os usuários locais não podem conceder comúnmente privilégios. Neste caso não se aplica a nenhum exemplo debido ao fato que , se um usuário é local jamais poderá ter privilégios concedidos comúnmente.
  5. Los usuarios locales solo pueden ser creados en las PDBs.
    6.   SQL> show con_name CON_NAME ------------------------------ PDB1

					SQL> create user LOCALUSER identified by LOCALUSER container=current; User created. 
					SQL> alter session set container=cdb$root; Session altered. 
					SQL> create user LOCALUSER identified by LOCALUSER container=current;
					create user LOCALUSER identified by LOCALUSER container=current 
					* ERROR at line 1: ORA-65049: creation of local user or role is not allowed in CDB$ROOT
  6. Podemos criar usuários locais com o mesmo nome em todas PDB’s, se chamaram iguais porém são usuários completamente diferentes.
    7.   SQL> show con_name

					CON_NAME 
					------------------------------ 
					PDB1
					SQL> create user LOCALUSER 
					identified by LOCALUSER container=current; User created. 
					SQL> alter session set container=pdb2; Session altered.
					SQL> create user LOCALUSER identified by LOCALUSER container=current; User created.
					SQL> alter session set container=cdb$root; Session altered. 
					SQL> select username, common, con_id from cdb_users where
					username='LOCALUSER'; 
					USERNAME        COM     CON_ID
					--------------- --- ---------- 
					LOCALUSER       NO           4
					LOCALUSER       NO           3
  7. Os privilégios atribuidos localmente afetam somente os objetos ou funções do sistema somente dentro da PDB onde foram atribuidos.
  8. As Roles criadas como locaos, mantem o mesmo comportamento dos usuários locais. Não podem iniciar com o prefixo “C##” ou “c##”.
    9.   SQL> create role C##localrole; create role C##localrole * ERROR at line 1: ORA-65094: invalid local user or role name SQL>
  9. As roles locais só podem ser concecidas localmente.
    10.   SQL> create role localrole container=current; Role created. SQL>

					grant localrole to localuser container=all; grantlocalrole to localuser container=all 
					* ERROR at line 1: ORA-65030: one may not grant a Common Privilege to a Local User or Role 
					SQL> grant localrole to localuser container=current; Grant succeeded.
  10. As roles locais só podem ser criadas nas PDBs.
    11.   SQL> show con_name CON_NAME

					------------------------------ 
					PDB1 
					SQL> create role localrole container=current; Role created. 
					SQL> create role localrole container=current; create role localrole container=current 
					* ERROR at line 1: ORA-65049: creation of local user or role is not allowed in CDB$ROOT
  11. As Roles locais não podem conter nenhum privilégio concecido comummente.
    12.   SQL> select role, common from dba_roles where role='LOCALROLE'; ROLE

					COM ---------- --- LOCALROLE  NO
    SQL> grant select any table to LOCALROLE container=all; grant select any table to LOCALROLE container=all * ERROR at line 1: ORA-65030: one may not grant a Common Privilege to a Local User or Role
  12. As roles locais são definidas no dicionário da PDB onde foram criadas.
    13.   SQL> show con_name CON_NAME

					------------------------------ 
					PDB1 
					SQL> select role, common from dba_roles where role='LOCALROLE'; ROLE
					COM ---------- ---
					LOCALROLE  NO
					SQL> show con_name 
					CON_NAME ------------------------------
					CDB$ROOT 
					SQL> select role, common from dba_roles where role='LOCALROLE';
					no rows selected SQL>
					show con_name 
					CON_NAME ------------------------------ PDB2
					SQL> select role, common from dba_roles where 
					role='LOCALROLE'; no rows selected
  13. Se uma PDB estiver fechada, seus usuários não são visivéis, pois os metadados são extraidos do tablespace SYSTEM desta PDB.
    14.   SQL> show con_name

					CON_NAME ------------------------------ PDB1 
					SQL> select username from dba_users where common='NO'; USERNAME 
					--------------------------------------------------------------------------------
					LOCALUSER PDBADMIN 
					SQL> alter session set container=cdb$root; Session altered.
					SQL>  select username, con_id from cdb_users where username='LOCALUSER'; USERNAME  
					CON_ID ---------- ---------- LOCALUSER        
					3 SQL> alter pluggable database pdb1 close; Pluggable database altered. 
					SQL> select username, con_id from cdb_users where username='LOCALUSER';
					No rows selected

    Tudo que devemos saber sobre “COMMON”:

  14. Os usuários comuns podem exercer funções sobre o “CDB$ROOT” e sobre qualquer PDB de acordo com os privilégios que são atribuidos em cada container. Para isso devem usar a cláusula “SET CONTAINER”.
    15.   SQL> create user C##COMMONUSER identified by "commonuser" container=all;

					User created.
					SQL> grant connect to C##COMMONUSER container=all; Grant succeeded.
					SQL>grant create table  to C##COMMONUSER container=current; Grant succeeded.
					SQL> create table t1(a number ); Table created. 
					SQL>conn  C##COMMONUSER/commonuser@pdb1; Connected.
					SQL> create table t1(a number ); create table t1(a number )
					* ERROR at line 1: ORA-01031: insufficient privileges
  15. Os usuários comuns só podem ser criados no CDB$ROOT.
    16.   SQL> show con_name

					CON_NAME ------------------------------ CDB$ROOT 
					SQL> alter session set container=pdb1; Session altered.
					SQL> create user C##COMMONUSER2 
					identified by "commonuser" container=current; create user C##COMMONUSER2 
					identified by "commonuser" container=current 
					* ERROR at line 1: ORA-65094: invalid local user or role name
  16. Um usuário comum pode criar usuários locais e comuns.
    17.   SQL> show con_name

					CON_NAME ------------------------------ CDB$ROOT
					SQL> show user USER is "C##COMMONUSER"
					SQL> create user C##COMMONUSER2 identified by "commonuser" container=all; User created.
					SQL> conn C##COMMONUSER/commonuser@pdb1; Connected. 
					SQL> create user LOCALUSER  identified by "localuser" container=current; User created.
					SQL>  create user C##COMMONUSER  identified by "commonuser" container=all; Usercreated.
  17. Os usuários comuns devem iniciar com o prefixo “C##” ou “c##”.
    18.   SQL> create user C##COMMONUSER  identified by "commonuser" container=all; User created.

					SQL> 
					create user COMMONUSER identified by "commonuser" container=all;
					create user COMMONUSER identified by "commonuser" container=all * 
					ERROR at line 1: ORA-65096: invalid common user or role name
  18. Os usuários comuns podem conceder privilégios comunmente e localmente.
    19.   SQL>conn  C##COMMONUSER/"commonuser" Connected.

					SQL> grant select any table to system container=all; Grant succeeded.
					SQL> grant select any table to system container=current; Grant succeeded.
  19. Os privilégios podem ser atribuidos comunmente somente no “CDB$ROOT”.
    20.   SQL> show con_name

					CON_NAME ------------------------------ CDB$ROOT
					SQL> grant select any table to C##COMMONUSER2 container=all; Grant succeeded.
					SQL>conn  C##COMMONUSER/"commonuser"@pdb1 Connected.
					SQL> grant select any table to C##COMMONUSER2 container=all;
					grant select any table to C##COMMONUSER2 container=all * ERROR at line 1: 
					ORA-65050: Common DDLs only allowed in CDB$ROOT
  20. Os usuários comuns são definidos no dicionário de dados do ROOT e uma descrição é agregada á eles no dicioário da PDB.
    21.   SQL> show con_name

					CON_NAME ------------------------------ CDB$ROOT
					SQL> select username, con_id from cdb_users where username='C##COMMONUSER'; norowsselected
					SQL> create user C##COMMONUSER identified by "commonuser" container=all; Usercreated.
					SQL> select username from dba_users where username='C##COMMONUSER'; 
					USERNAME --------------- C##COMMONUSER
					SQL>alter session set container=pdb1; Session altered.
					SQL> select username from dba_users where username='C##COMMONUSER'; 
					USERNAME --------------- C##COMMONUSER 
					SQL> alter session set container=pdb2; Session altered. 
					SQL> select username from dba_users where username='C##COMMONUSER'; 
					USERNAME --------------- C##COMMONUSER
  21. As roles comuns devem iniciar necesariamente com “C##” ou “c##”.
    22.   SQL> create role COMMONROLE container=all;

					create role COMMONROLE container=all * ERROR at line 1: ORA-65096: 
					invalid common user or role name SQL> create role C##COMMONROLE container=all;
    Role created.
  22. As roles comuns só podem ser criadas no CDB$ROOT.
    23.   SQL> show con_name

					CON_NAME ------------------------------ 
					CDB$ROOT 
					SQL> create role C##COMMONROLE container=all; Role created.
					SQL> alter session set container=pdb1; Session altered.
					SQL> create role C##COMMONROLE2 container=all; 
					create role C##COMMONROLE2 container=all
					* ERROR at line 1: ORA-65050: Common DDLs only allowed in CDB$ROOT
  23. Quando se especifica uma cláusula adicional a sentença “CREATE USER”, está cláusula tem que ser cumprida necesariamente em todas as PDBs, caso contrario o usuário não será criado.
    24.   SQL> select con_id, name from v$pdbs;

					CON_ID NAME ------ -------- 2
					PDB$SEED 3 PDB1 4 PDB2 
					SQL> SELECT TABLESPACE_NAME,
					CON_ID FROM CDB_TABLESPACES 
					WHERE TABLESPACE_NAME ='TBS2'; 
					TABLESPACE_NAME 
					CON_ID 
					--------------- ------ 
					TBS2           1 
					TBS2            3

    Como podemos ver, o Tablespace “TBS2” só será criado no “CDB$ROOT” e no PDB1, pois não existe no PDB2.

      SQL> CREATE USER C##DEIBY IDENTIFIED BY MANAGER1 CONTAINER=ALL DEFAULT TABLESPACE TBS2;

					CREATE USER C##DEIBY IDENTIFIED BY MANAGER1 CONTAINER=ALL DEFAULT TABLESPACE TBS2 
					* ERROR at line 1: ORA-65048: error encountered when processing the current DDL 
					statement in pluggabledatabase PDB2 ORA-00959: tablespace 'TBS2' does not exist
  24. Ao excluir um usuário comum este usuário será excluido também das demais PDB’s.(Exceção na nota do Metalink 1619287.1).
    25.   SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

					USERNAME            CON_ID --------------- ---------- C##COMMONUSER   
					4 C##COMMONUSER           
					3 C##COMMONUSER        
					1 SQL> drop user C##COMMONUSER; User dropped. 
					SQL> select username, 
					con_id from cdb_users where username='C##COMMONUSER'; no rows selected
  25. Os privilégios atribuidos comúnmente afetam o “CDB$ROOT” e todas as PDB’s existentes.
  26. As roles comuns, manteram o mesmo comportamento dos usuários comuns.
  27. As roles comuns podem ser concedidas a usuários comuns e a usuários locais.
    28.   SQL> create role C##commonrole container=all; Role created.

					SQL> select username, common from dba_users where username='C##COMMONUSER'; USERNAME    
					COM --------------- --- C##COMMONUSER   YES SQL> grant C##COMMONROLE to C##COMMONUSER;
					Grant succeeded. SQL>  alter session set container=pdb1; Sessionaltered. 
					SQL> select username, common from dba_users where username='LOCALUSER'; 
					USERNAME   COM ---------- --- LOCALUSER  NO SQL> grant C##COMMONROLE to LOCALUSER; Grant succeeded.
  28. Uma role comum pode conter privilégios concedidos comúnmente e também localmente.
    29.   SQL> grant create table to C##COMMONROLE container=all; Grant succeeded. SQL> grant select any table to C##COMMONROLE container=current;
    Grant succeeded.
  29. As roles comuns devem iniciar com o prefixo "C##" ou "c##".
    30.   SQL> create role COMMONROLE container=all; create role

					COMMONROLE container=all * ERROR at line 1: ORA-65096: invalid common user or 
					role name SQL> create role c##commonrole container=all; Role created.
  30. Os usuários comuns são donos de vários schemas, em cada PDB teremos um schema diferente.
    31.   SQL> show con_name

					CON_NAME ------------------------------ 
					CDB$ROOT SQL> show user USER is "C##COMMONUSER" 
					SQL> select table_name from user_tables;
					TABLE_NAME ------------------------------------------------ T1 
					SQL> conn C##COMMONUSER/commonuser@pdb1; Connected. 
					SQL> select table_name from user_tables; norowsselected

    O que precisamos saber sobre a cláusula “CONTAINER”:

  31. Quando se cria um usuário no ROOT especificando o valor ALL ou deixando nulo na clausula CONTAINER, o usuário que será criado será COMUN no dicionário do ROOT.
    32.   SQL> show con_name

					CON_NAME ------------------------------ CDB$ROOT 
					SQL> create user C##COMMONUSER identified by "commonuser"; Usercreated.
					SQL> select distinct  username,common from cdb_users
					where username='C##COMMONUSER'; USERNAME       
					COM --------------- --- C##COMMONUSER   YES
  32. Quando se cria um usuário na PDB especificando o calor “CURRENT” ou deixando nulo na cláusula “CONTAINER”, o usuário será criado como LOCAL.
    33.   SQL> show con_name CON_NAME ------------------------------ PDB1

					SQL> create user LOCALUSER identified by "localuser"; Usercreated.
					SQL> select username,common from dba_users where username='LOCALUSER';
					USERNAME   COM ---------- --- LOCALUSER  NO
  33. Se especificarmos o valor “CURRENT” quando formos criar um usuário no ROOT teremos o seguinte erro .
    34.   SQL> show con_name CON_NAME ------------------------------ CDB$ROOT

					SQL> create user C##COMMONUSER identified by "commonuser" container=current; create user
					C##COMMONUSER identified by "commonuser" container=current * ERROR at line 1: ORA-65094:
					invalid local user or role name SQL>  create user LOCALUSER identified by "commonuser" 
					container=current; create user LOCALUSER identified by "commonuser" container=current
					* ERROR at line 1: ORA-65049: creation of local user or role is not allowed in CDB$ROOT
  34. Se especificarmos o valor “ALL” quando estivermos criando um usuário na PDB receberemos o erro.
    35.   SQL> show con_name CON_NAME ------------------------------ PDB1

					SQL> create user LOCALUSER identified by "commonuser" container=ALL; create user 
					LOCALUSER identified by "commonuser" container=ALL * ERROR at line 1: ORA-65050:
					Common DDLs only allowed in CDB$ROOT SQL> create user C##COMMONUSER identified 
					by "commonuser" container=ALL; create user C##COMMONUSER identified by 
					"commonuser" container=ALL * ERROR at line 1: ORA-65050: Common DDLs only allowed in CDB$ROOT
  35. Se um privilégio é dado a um usuário ( comum ou local) sem especificar a clausula CONTAINER, o privilégio será concedido únicamente no container onde foi executada a sentença.
    36.   SQL> show con_name CON_NAME

					------------------------------ CDB$ROOT
					SQL> grant create table to C##COMMONUSER; Grant succeeded.
					SQL> select privilege from dba_sys_privs where grantee='C##COMMONUSER';
					PRIVILEGE ---------------------------------------- CREATE TABLE CREATE
					SESSION SQL> select privilege from dba_sys_privs where grantee='C##COMMONUSER';
					PRIVILEGE ---------------------------------------- CREATE SESSION
  36. Se a role é dada a um usuário ( comum ou local) sem especificar a clausula CONTAINER, a role será concedida únicamente no container onde a sentença foi executada.
    37.   SQL> show con_name CON_NAME ------------------------------ CDB$ROOT

					SQL> select granted_role from dba_role_privs where grantee='C##COMMONUSER'; no rows selected 
					SQL> grant C##COMMONROLE to C##COMMONUSER; Grant succeeded.
					SQL> select granted_role from dba_role_privs where grantee='C##COMMONUSER'; 
					GRANTED_ROLE -------------------------------------------- C##COMMONROLE 
					SQL> alter session set container=pdb1; Session altered.
					SQL> select granted_role from dba_role_privs where grantee='C##COMMONUSER'; no rows selected
  37. A cláusula “CONTAINER” é fundamental toda hora que for conceder um privilégio, pois esta clausula faz a diferença entre conceder privilégios localmente (CONTAINER=CURRENT) ou concede-lo comúnmente (CONTAINER=ALL).

OraWorld é um grupo que está constantemente trabalhando com a comunidade Oracle por meio de artigos, conferências, webinars e cursos de Banco de Dados Oracle. O OraWorld possui membros "Oracle Certified Masters" e "Oracle ACEs".

Você pode seguir este grupo através dos seguintes links:

//www.facebook.com/oraworldteam //twitter.com/oraworld_team //www.oraworld-team.com