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.

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


  3. Um usuário local pode criar usuários locais, mas não usuários comuns.
  4. 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


  5. Os usuários locais só podem ter privilégios  concedidos localmente.
  6. 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.


  7. 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.
  8. Los usuarios locales solo pueden ser creados en las PDBs.
  9. 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

  10. Podemos criar usuários locais com o mesmo nome em todas PDB’s, se chamaram iguais porém são usuários completamente diferentes.
  11. 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

  12. Os privilégios atribuidos localmente afetam somente os objetos ou funções do sistema somente dentro da PDB onde foram atribuidos.
  13. As Roles criadas como locaos, mantem o mesmo comportamento dos usuários locais. Não podem iniciar com o prefixo “C##” ou “c##”.
  14. SQL> create role C##localrole;
    create role C##localrole
     *
    ERROR at line 1:
    ORA-65094: invalid local user or role name

    SQL>

  15. As roles locais só podem ser concecidas localmente.
  16. 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.

  17. As roles locais só podem ser criadas nas PDBs.
  18. 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

  19. As Roles locais não podem conter nenhum privilégio concecido comummente.
  20. 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

  21. As roles locais são definidas no dicionário da PDB onde foram criadas.
  22. 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

  23. 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.
  24. 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”:

  25. 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”.
  26. 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

  27. Os usuários comuns só podem ser criados no CDB$ROOT.
  28. 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

  29. Um usuário comum pode criar usuários locais e comuns.
  30. 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.

  31. Os usuários comuns devem iniciar com o prefixo “C##” ou “c##”.
  32. 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

  33. Os usuários comuns podem conceder privilégios comunmente e localmente.
  34. 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.

  35. Os privilégios podem ser atribuidos comunmente somente no “CDB$ROOT”.
  36. 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

  37. 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.
  38. 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

  39. As roles comuns devem iniciar necesariamente com “C##” ou “c##”.
  40. 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.

  41. As roles comuns só podem ser criadas no CDB$ROOT.
  42. 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

  43. 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.
  44. 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

  45. 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).
  46. 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

  47. Os privilégios atribuidos comúnmente afetam o “CDB$ROOT” e todas as PDB’s existentes.
  48. As roles comuns, manteram o mesmo comportamento dos usuários comuns.
  49. As roles comuns podem ser concedidas a usuários comuns e a usuários locais.
  50. 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.

  51. Uma role comum pode conter privilégios concedidos comúnmente e também localmente.
  52. SQL> grant create table to C##COMMONROLE container=all;

    Grant succeeded.

    SQL> grant select any table to C##COMMONROLE container=current;

    Grant succeeded.

  53. As roles comuns devem iniciar com o prefixo "C##" ou "c##".
  54. 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.

  55. Os usuários comuns são donos de vários schemas, em cada PDB teremos um schema diferente.
  56. 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”:

  57. 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.
  58. 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

  59. 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.
  60. 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

  61. Se especificarmos o valor “CURRENT” quando formos criar um usuário no ROOT teremos o seguinte erro .
  62. 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

  63. Se especificarmos o valor “ALL” quando estivermos criando um usuário na PDB receberemos o erro.
  64. 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

  65. 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.
  66. 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

  67. 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.
  68. 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

  69. 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:
https://www.facebook.com/oraworldteam
https://twitter.com/oraworld_team
http://www.oraworld-team.com