Oracle 12c : Usuarios, Roles y Privilegios - Local vs Común

En Oracle 12c, la administración de los usuarios, roles y privilegios ha mejorado para poder amoldarse a la nueva arquitectura de base de datos Multitenant.

Oracle 12c maneja dos tipos de definiciones para temas de seguridad: Local y Común. 

Esto nos permite:

- Crear un Usuario local o un Usuario común.

- Definir un Privilegio como local o un Privilegio como común.

- Crear un Rol como local o un Rol como común.

En este articulo quiero explicar, con ejemplos, la diferencia entre el dominio LOCAL VS el dominio COMÚN. 

Empezamos explicando a los usuarios, luego los privilegios y finalmente los roles.

Usuarios Locales

Un usuario local es un usuario creado dentro de un PDB.  El usuario local tiene las mismas características que un usuario dentro de una base de datos tradicional Non-CDB.

La forma en la que creamos un usuario local es similar a la de versiones anteriores, sin embargo, se le agrega una nueva opción.

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL>

SQL> create user usu1 identified by oracle CONTAINER=CURRENT;

User created.

La opción CONTAINER=CURRENT indica que el usuario es creado de manera local y  en el contenedor al que estamos conectados, para nuestro ejemplo PDB1.

Es posible crear usuarios con el mismo nombre en diferentes PDBs, sin embargo, ambos usuarios son completamente diferentes.

Esto se debe al aislamiento que tiene cada PDB dentro de la arquitectura Multitenant

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL>

SQL> create user usu2 identified by oracle CONTAINER=CURRENT;

User created.

SQL> connect sys@PDB2 as sysdba

Enter password:

Connected.

SQL>

SQL> create user usu2 identified by oracle CONTAINER=CURRENT;

User created.

Para comprobar que ambos usuarios son diferentes podemos realizar una consulta a la vista CDB_USERS desde el contenedor ROOT.

SQL> connect / as sysdba

Connected.

SQL>  select CON_ID,USER_ID, USERNAME from CDB_USERS

where USERNAME='USU2';

    CON_ID    USER_ID USERNAME

---------- ---------- --------------------

         3        105 USU2

         4        103 USU2

Observamos que cada usuarios pertenece a un contenedor diferente (3=PDB1, 4=PDB2) y tiene un código de usuario distinto.

Los usuarios locales solo pueden crearse dentro de un PDB.  Dentro del contenedor ROOT, no está permitido.

SQL> connect / as sysdba

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

SQL> create user usu3 identified by oracle CONTAINER=CURRENT;

create user usu3 identified by oracle CONTAINER=CURRENT

                               *

ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

Usuarios Comunes

Los usuarios comunes son una nueva definición el Oracle 12c.  Son usuarios que cuando son creados, automáticamente se crean en todos los contenedores de la base de datos (Root y PDBs).

Los usuarios comunes tienen la misma autenticación en todos los contendores ya que solo se define una sola autenticación al momento de crearlos.

Para poder diferenciar un usuario común de un usuario local, es obligatorio colocar C## delante del nombre del usuario y agregar la siguiente opción.

SQL> connect / as sysdba

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> create user C##_usu1 identified by oracle CONTAINER=ALL;

User created.

La opción CONTAINER=ALL indica que el usuario es creado de manera común en todos los contenedores de la base de datos.

Solo se puede crear usuarios comunes en el contendor ROOT. Si se quiere crear usuarios comunes dentro de un PDB, nos muestra el siguiente error:

SQL> connect sys/oracle@PDB1  as sysdba

Connected.

SQL>  create user C##_usu2 identified by oracle CONTAINER=ALL;

 create user C##_usu2 identified by oracle CONTAINER=ALL

                                    *

ERROR at line 1:

ORA-65050: Common DDLs only allowed in CDB$ROOT

Si no definimos el  nombre del usuario con C## nos muestra el siguiente error:

SQL> connect / as sysdba

Connected.

SQL> create user comun1 identified by oracle CONTAINER=ALL;

create user comun1 identified by oracle CONTAINER=ALL

            *

ERROR at line 1:

ORA-65096: invalid common user or role name

Podemos consultar la vista CDB_USERS para saber que usuarios con comunes y que usuarios son locales

SQL> select CON_ID,USER_ID, USERNAME, COMMON

  2  from CDB_USERS

  3  where USERNAME='C##_USU1';

    CON_ID    USER_ID USERNAME             COM

---------- ---------- -------------------- ---

         1        111 C##_USU1             YES

         3        107 C##_USU1             YES

         4        104 C##_USU1             YES

         6        102 C##_USU1             YES

Como se puede observar el usuario común C##_USU1 ha sido creado en todos los contendores de la base de datos.

Los únicos usuarios comunes que no tienen C## delante son SYS y SYSTEM.

Privilegios Locales y Comunes

Un privilegio se define dependiendo del contexto en el que se entrega. 

Si el privilegio es entregado dentro de una base de datos PDB o ROOT y con la opción CONTAINER=CURRENT se define como un privilegio Local.

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL>

SQL> grant create any table to usu1 CONTAINER=CURRENT;

Grant succeeded.

En el ejemplo se entrega el privilegio CREATE ANY TABLE de manera local al usuario USU1 y solo es válido dentro de la base de datos PDB1.

Si el privilegio es entregado dentro en el contenedor ROOT y con la opción CONTAINER=ALL se define como un privilegio Común.

SQL> connect / as sysdba

Connected.

SQL>

SQL> grant select any table to C##_USU1 CONTAINER=ALL;

Grant succeeded.

En el ejemplo se entrega el privilegio SELECT ANY TABLE al usuario común C##_USU1.  Este privilegio será válido en todos los contenedores de la base de datos.

A un usuario local solo se le puede asignar privilegios locales.  Si se desea asignarle un privilegio común se muestra el siguiente error:

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL>

SQL>  grant create user to usu1 CONTAINER=ALL;

 grant create user to usu1 CONTAINER=ALL

*

ERROR at line 1:

ORA-65030: one may not grant a Common Privilege to a Local User or Role

A un usuario común se le puede asignar privilegios comunes y privilegios locales.  Los privilegios comunes serán validos en todos los contendores y los privilegios locales, solo en el contenedor donde se asignan.

Privilegio Común

SQL> connect / as sysdba

Connected.

SQL>

SQL> grant create user to C##_USU1 CONTAINER=ALL;

Grant succeeded.

Privilegio Local

SQL> connect sys@PDB2 as sysdba

Enter password:

Connected.

SQL>

SQL> grant alter user to C##_USU1 CONTAINER=CURRENT;

Grant succeeded.

Un usuario común puede tener diferente grupos de privilegios en cada contenedor.

Se puede consultar las vista CDB_SYS_PRIVS o CDB_TAB_PRIVS  para revisar los privilegios comunes y locales de cada usuario en cada contenedo

SQL> select CON_ID,  GRANTEE, PRIVILEGE, COMMON

  2  from cdb_sys_privs where GRANTEE='C##_USU1'

  3  order by PRIVILEGE;

    CON_ID GRANTEE         PRIVILEGE                                COM

---------- --------------- ---------------------------------------- ---

         4 C##_USU1        ALTER USER                               NO

         4 C##_USU1        CREATE USER                              YES

         3 C##_USU1        CREATE USER                              YES

         6 C##_USU1        CREATE USER                              YES

         1 C##_USU1        CREATE USER                              YES

         6 C##_USU1        SELECT ANY TABLE                         YES

         1 C##_USU1        SELECT ANY TABLE                         YES

         3 C##_USU1        SELECT ANY TABLE                         YES

         4 C##_USU1        SELECT ANY TABLE                         YES

9 rows selected.

Como se observa en la consulta el usuario común C##_USU1 tiene 2 privilegios comunes y un privilegio local en el contenedor 4 (PDB2).

Para poder revocar un privilegio utilizamos el comando REVOKE. Usamos la opción CONTAINER para indicar si queremos revocar un privilegio Local o un privilegio Común.

Revocamos un privilegio Local.

SQL> connect sys@PDB2 as sysdba

Enter password:

Connected.

SQL> revoke alter user from C##_USU1 CONTAINER=CURRENT;

Revoke succeeded.

Revocamos un privilegio Común

SQL> connect / as sysdba

Connected.

SQL>

SQL> revoke CREATE USER from C##_USU1 CONTAINER=ALL;

Revoke succeeded.

No es posible revocar un privilegio común de manera local. 

Por ejemplo el usuario C##_USU1 tiene el privilegio común SELECT ANY TABLE

    CON_ID GRANTEE         PRIVILEGE                                COM

---------- --------------- ---------------------------------------- ---

         1 C##_USU1        SELECT ANY TABLE                         YES

         3 C##_USU1        SELECT ANY TABLE                         YES

         4 C##_USU1        SELECT ANY TABLE                         YES

         6 C##_USU1        SELECT ANY TABLE                         YES

Si queremos eliminar el privilegio SELECT ANY TABLE en el contenedor 4 (PDB2) se muestra un error.

SQL> connect sys@PDB2 as sysdba

Enter password:

Connected.

SQL>

SQL>

SQL> revoke SELECT ANY TABLE from C##_USU1 CONTAINER=CURRENT;

revoke SELECT ANY TABLE from C##_USU1 CONTAINER=CURRENT

*

ERROR at line 1:

ORA-65092: system privilege granted with a different scope to 'C##_USU1'

Roles Locales y Comunes

Al igual que los usuarios, podemos tener Roles Locales y Comunes.  El Rol local es creado dentro del contexto de un contenedor PDB o ROOT y un Rol común se crea en todos los contenedores de la base de datos.

Creamos un Rol local en el PDB1

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL>

SQL> create role LOCAL_ROL1 CONTAINER=CURRENT;

Role created.

Creamos un Rol Común desde el contenedor ROOT.

Es importante aclarar que el nombre de los roles comunes debe empezar con C##

SQL> connect / as sysdba

Connected.

SQL>

SQL> create role C##_ROl2 CONTAINER=ALL;

Role created.

Para el tema de asignación de privilegios a roles, se trabaja con el mismo esquema que con los usuarios.

A un Rol local se le puede asignar privilegios locales.

A un Rol común se le puede asignar privilegios comunes y locales.  Es decir que un Rol común puede tener diferentes privilegios en los diferentes contenedores.

Asignamos privilegios al Rol local

SQL> connect  sys@PDB1 as sysdba

Enter password:

Connected.

SQL>

SQL> grant alter any table to LOCAL_ROL1 CONTAINER=CURRENT;

Grant succeeded.

SQL> grant select any table to LOCAL_ROL1 CONTAINER=CURRENT;

Grant succeeded.

Asignamos privilegios comunes al Rol común desde el contenedor ROOT

SQL> connect / as sysdba

Connected.

SQL>

SQL> grant create user to C##_ROL2 CONTAINER=ALL;

Grant succeeded.

SQL> grant alter user  to C##_ROL2 CONTAINER=ALL;

Grant succeeded.

SQL> grant alter any table to C##_ROl2 CONTAINER=ALL;

Grant succeeded.

Asignamos privilegios locales al Rol común desde el contendor PDB1

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL>

SQL> grant create user to C##_ROl2 CONTAINER=CURRENT;

Grant succeeded.

Podemos revisar los privilegios locales y comunes que tiene cada Rol en las vistas CDB_SYS_PRIVS y CDB_TAB_PRIVS.

SQL> select CON_ID,GRANTEE, PRIVILEGE, COMMON

  2   from cdb_sys_privs

  3  where GRANTEE='C##_ROL2'

  4  order by PRIVILEGE;

    CON_ID GRANTEE         PRIVILEGE                                COM

---------- --------------- ---------------------------------------- ---

         6 C##_ROL2        ALTER ANY TABLE                          YES

         4 C##_ROL2        ALTER ANY TABLE                          YES

         3 C##_ROL2        ALTER ANY TABLE                          YES

         1 C##_ROL2        ALTER ANY TABLE                          YES

         6 C##_ROL2        ALTER USER                               YES

         4 C##_ROL2        ALTER USER                               YES

         3 C##_ROL2        ALTER USER                               YES

         1 C##_ROL2        ALTER USER                               YES

         6 C##_ROL2        CREATE USER                              YES

         4 C##_ROL2        CREATE USER                              YES

         1 C##_ROL2        CREATE USER                              YES

         3 C##_ROL2        CREATE USER                              YES

         3 C##_ROL2        CREATE USER                              NO

13 rows selected.

Se puede observar que el Rol común C##_ROL2 tiene un privilegio local en el contenedor 3 (PDB1) y 3 privilegios comunes en todos los contendores.

Para el tema de asignación a usuarios, los privilegios de cada rol son validos en el contexto en el que se encuentra el usuario.  Es decir:

A un usuario local se le puede asignar un Rol local y un Rol común, sin embargo, los privilegios solo serán validos dentro del contendor en donde se encuentra el usuario.

A un usuario común se le puede asignar un Rol local y un Rol común, sin embargo, los privilegios de los roles locales solo serán validos dentro del contendor donde se encuentra el rol local.  Los privilegios de los roles comunes si se extienden a todos los contendores de la base de datos.

Haremos un ejemplo de cada uno.

Asignamos al usuario local USU1, del contendor PDB1, el rol común C##_ROL2

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL>

SQL> grant C##_ROL2 to USU1 CONTAINER=CURRENT;

Grant succeeded.

Una vez asignado se puede observar que el usuario USU1 tiene los privilegios ALTER ANY TABLE, CREATE USER y ALTER USER. Estos privilegios son comunes en el rol C##_ROL2, sin embargo, para el usuario USU1, los privilegios serán validos solo dentro del contendor PDB1.

SQL> select ROLE, PRIVILEGE, COMMON from role_sys_privs where ROLE='C##_ROL2';

ROLE                 PRIVILEGE                                COM

-------------------- ---------------------------------------- ---

C##_ROL2             CREATE USER                              NO

C##_ROL2             ALTER ANY TABLE                          YES

C##_ROL2             CREATE USER                              YES

C##_ROL2             ALTER USER                               YES

SQL> select GRANTEE, GRANTED_ROLE, COMMON

  2  from dba_role_privs

  3  where GRANTEE='USU1';

GRANTEE                        GRANTED_ROLE                   COM

------------------------------ ------------------------------ ---

USU1                           C##_ROL2                       NO

USU1                           CONNECT                        NO

Asignamos al usuario común C##_USU1 el rol local LOCAL_ROL1 dentro del contenedor PDB1

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL>

SQL>

SQL> grant LOCAL_ROL1 to C##_USU1 CONTAINER=CURRENT;

Grant succeeded.

El usuario C##_USU1 tiene los privilegios SELECT ANY TABLE y ALTER ANY TABLE pero solo dentro del contexto del contendor PDB1.

SQL> select ROLE, PRIVILEGE, COMMON from role_sys_privs where ROLE='LOCAL_ROL1';

ROLE                 PRIVILEGE                                COM

-------------------- ---------------------------------------- ---

LOCAL_ROL1           ALTER ANY TABLE                          NO

LOCAL_ROL1           SELECT ANY TABLE                         NO

De esta manera he querido explicar un poco las nuevas opciones que se tienen en Oracle 12c para administrar Usuarios, Privilegios y Roles.

Espero les pueda servir de ayuda.