Todo lo que tienes que saber sobre COMMON y LOCAL en Oracle Database 12c

Por Deiby Mauricio Gómez y Yenugula Venkata RaviKumar (OCM)
Publicado en Abril 2014

Bienvenidos de nuevo colegas de Oracle Latinoamérica, es de mucho agrado para nosotros seguir contribuyendo con nuestros artículos a la comunidad Oracle, hemos trabajado constantemente en traerles contenido de calidad, con el cual puedan aprender lo que la versión 12c nos trae de una manera eficaz y eficiente, con ejemplos claros y concisos, con teoría fundamental y con muchas experiencias listas para compartir. Como bien saben, la arquitectura de la base de datos Oracle en su versión 12c ha sufrido muchas mejoras, ahora ya se puede hablar de “Pluggable Database”, de “Container” y de “SEED”. Para quienes ya están adentrados en estos nuevos conceptos de “multitanent” ya estarán al tanto de que una base de datos de tipo “CDB” puede contener desde cero a hasta 253 “Pluggable Databases” incluyendo el “SEED”.  A la base de datos que aloja todas las bases de datos internas se le denomina  “CDB$ROOT” y a las bases de datos internas se les denominan “Pluggable Database” o “PDB”, el “PDB$SEED” también es una PDB, pero la diferencia es que solo puede estar abierta en modo lectura y es utilizada como una plantilla para crear nuevas PDB.

Bajo esta nueva arquitectura surge la duda de cómo se administran los usuarios, roles y sus respectivos privilegios. Bueno,  es ese el objeto de este artículo, demostrar la mayoría de los casos posibles de usuarios y roles; y sus respectivos privilegios, tanto de objetos como de sistema. Ahora, en la versión 12c, existen dos grandes categorías:

  • LOCAL
  • COMMON

 

Características:

  • Los usuarios pueden ser locales o comunes
  • Los roles pueden ser creados locales o comunes
  • Los privilegios pueden ser asignados localmente o comúnmente
  • Cada PDB contiene su propio diccionario de Datos.
  • Los usuarios comunes se registran en el diccionario del "CDB$ROOT".
  • Los usuarios locales se registran en el diccionario de la PDB.
  • Algunos datos dentro de un PDB son extraídos desde el diccionario de datos del "CDB$ROOT", esto es hecho con apuntadores.
  • El parámetro _common_user_prefix controla el prefijo de los usuarios comunes. No se debe alterar este parámetro sin soporte de Oracle.

 

Descripción de la imagen:

  • C##DBA al igual que SYS están presentes en el "CDB$ROOT", en la PDB "hrpdb" y en la PDB "salespdb", esto es porque los dos usuarios son de tipo común.
  • Existen dos usuarios con el nombre "hr", uno para la PDB "hrpdb" y otro para la PDB "salespdb", aunque los dos usuarios se llaman igual, son totalmente diferentes pues son locales.
  • Existen dos usuarios con el nombre "rep", uno para la PDB "hrpdb" y otro para la PDB "salespdb", aunque los dos usuarios se llaman igual, son totalmente diferentes pues son locales.
  • Los usuarios "SYS" y "C##DBA" tienen esquemas diferentes en cada contenedor.

 

Todo lo que tiene que saber sobre “LOCAL”:

  1. Los usuarios creados como locales funcionan como usuarios en las versiones anteriores. Pueden realizar tareas únicamente dentro de la PDB donde fueron creados. No pueden conectarse a ninguna otra PDB aunque tenga el privilegio "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. Un usuario local puede crear usuarios locales, pero no comunes.
  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. Los usuarios locales solo pueden tener privilegios cedidos 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. Los usuarios locales no pueden ceder comúnmente privilegios. En este caso no aplica ningún ejemplo debido a que, un usuario local jamás podrá tener un privilegio cedido 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. Se puede crear usuarios locales con el mismo nombre en todas las PDBs, se llamarán igual pero son usuarios 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. Los privilegios asignados localmente afectan únicamente los objetos o funciones del sistema únicamente dentro del PDB donde fueron asignados.
  13. Los Roles creados como locales, mantienen el mismo comportamiento de los usuarios locales. No pueden iniciar con el prefijo "C##" o "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. Los roles locales solo pueden ser cedidos 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. Los roles locales solo pueden ser creados en las 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. Los roles locales no pueden contener ningún privilegio cedido comúnmente.
  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. Los roles locales son definidos en el diccionario de la PDB donde fueron creados.
  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. Si una PDB está cerrada, sus usuarios no son visibles, pues los metadatos son extraídos del Tablespace SYSTEM de dicha PDB.
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

Todo lo que tiene que saber sobre “COMMON”:

  1. Los usuarios comunes pueden ejercer acciones sobre el "CDB$ROOT" y sobre cualquier PDB de acuerdo con los privilegios que se le han asignado en cada contenedor. Para esto deben usar la cláusula “SET CONTAINER”.
  2. 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

  3. Los usuarios comunes solo pueden ser creados en el CDB$ROOT.
  4. 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

  5. Un usuario común puede crear usuarios locales y comunes.
  6. 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.

  7. Los usuarios comunes deben iniciar con el prefijo "C##" o "c##".
  8. 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

  9. Los usuarios comunes pueden ceder privilegios comúnmente y localmente.
  10. 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.

  11. Los privilegios pueden ser asignados comúnmente únicamente en el "CDB$ROOT".
  12. 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

  13. Los usuarios comunes son definidos en el diccionario de datos del ROOT y una descripción de ellos es agregada en el diccionario de cada PDB.
  14. 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

  15. Los roles comunes deben iniciar necesariamente con “C##” o “c##”.
  16. 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.

  17. Los roles comunes solo pueden ser creados en el CDB$ROOT.
  18. 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

  19. Cuando se especifica una clausula adicional en la sentencia "CREATE USER", dicha cláusula tiene que cumplirse necesariamente en todas las PDBs, de lo contrario el usuario no se creará.
  20. 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 se ve, el tablespace "TBS2" solo está creado en el "CDB$ROOT" y en PDB1, pero no existe en 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

  21. Al borrar un usuario común dicho usuario se borrará también de las demás PDBs. (Excepción en la nota de Metalink 1619287.1).
  22. 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

  23. Los privilegios asignados comúnmente afectan al "CDB$ROOT" y a todas las PDBs existentes.
  24. Los Roles comunes, mantienen el mismo comportamiento de los usuarios comunes.
  25. Los roles comunes pueden ser cedidos a usuarios comunes y a usuarios locales.
  26. 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.

  27. Un rol común puede contener privilegios cedidos comúnmente y también localmente.
  28. SQL>  grant create table to C##COMMONROLE container=all;
    Grant succeeded.
    SQL> grant select any table to C##COMMONROLE container=current;
    Grant succeeded.

  29. Los roles comunes deben iniciar con el prefijo "C##" o "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.

  31. Los usuarios comunes son dueños de varios esquemas, en cada PDB tienen un esquema diferente.
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

Lo que hay que saber sobre la cláusula “CONTAINER”:

  1. Cuando se crea un usuario en el ROOT especificando el valor ALL o dejándolo nulo en la cláusula CONTAINER, el usuario se creará como COMMON en el diccionario del ROOT.
  2. 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


  3. Cuando se crea un usuario en una PDB especificando el valor “CURRENT” o dejándolo nulo en la cláusula “CONTAINER”, el usuario se creará como “LOCAL”.
  4. 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

  5. Si se especifica el valor “CURRENT” cuando se crea un usuario en el ROOT se obtendrá un error.
  6. 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

  7. Si se especifica el valor “ALL” cuando se crea un usuario en una PDB se obtendrá un error.
  8. 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

  9. Si un privilegio es dado a un usuario (común o local) sin especificar la cláusula CONTAINER, el privilegio será cedido únicamente en el contenedor en el que fue ejecutada la sentencia.
  10. 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

  11. Si un rol es dado a un usuario (común o local) sin especificar la cláusula CONTAINER, el rol será cedido únicamente en el contenedor en el que fue ejecutada la sentencia.
  12. 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

  13. La cláusula "CONTAINER" es fundamental a la hora de ceder privilegios, pues esta cláusula hace la diferencia entre ceder un privilegio localmente (CONTAINER=CURRENT) o cederlo comúnmente (CONTAINER=ALL).


Deiby Gomez es un DBA con experiencia en Oracle ExadataDatabase Machine y Soluciones de Alta Disponibilidad. Deiby frecuentemente da conferencias en distintos eventos de Oracle en Guatemala tales. Deiby es el primer Oracle ACE de Guatemala. Es OCP 11g y 12c, "Oracle RAC 11g and Grid Infraestructure Administrator", "Exadata Database Machine  X3 Administrator", "Oracle Linux Certified Implementation Specialist" y "Oracle SOA Implementation Certified Expert".

Yenugula Venkata Ravikumar es un DBA con más de 15 años de experiencia especializada en entornos de alta disponibilidad de bases de datos (RAC, Data Guard, entre otros), afinamiento y rendimiento, migraciones, backup y recuperación, Oracle Exadata X2 y X3, es experto en sistemas operativos tales como AIX, HP-UX y Linux. Ha participado como conferencista en varios eventos de Oracle en la India, donde reside actualmente. Obtuvo el título "Oracle Certified Master (OCM 10g)" en el 2009.