Roles en Oracle

Generalmente cuando uno toma el curso de Workshop I de 10g, y llega a la parte de Roles (lección 6), se habla muy poco de cómo se pueden manejar los roles, e incluso no hay un ejemplo claro del mismo.

Las veces que me toca dar el curso a mi, me gusta explicar a fondo lo más que puedo acerca de roles.

Se nos dice que un rol, es asignado por default a un usuario. Un rol, puede ser asegurado de manera adicional, que por default, no trae seguridad alguna.

Entonces, partiendo de la teoría, un rol puede ser creado con la siguiente seguridad:

* Ninguna (Default)
* Password
* External
* Global

Por falta de hardware/software, no puedo demostrar la seguridad Global, pero las demás sí.

Empecemos por crear un usuario, solo para no perder la costumbre, crearé un usuario identificado a nivel sistema operativo.

C:\>sqlplus "/ AS SYSDBA"

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> CREATE USER "OPS$HUGO-WIN\HUGO" IDENTIFIED externally;

Usuario creado.

SQL> GRANT CONNECT TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente. 

Ahora nos conectamos como el usuario creado, identificado de manera externa, y validamos el primer rol asignado "CONNECT"

SQL> conn /
Conectado.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

Aquí validamos dos cosas, la primera es que el rol de create session viene implícito en el connect, ya que de otra forma no hubieramos podido crear la sesión; y lo segundo es que el rol asignado, está activo por default.

Ahora crearemos un rol identificado por un password

SQL> conn / as sysdba
Conectado.

SQL> CREATE role dba_pass IDENTIFIED BY supersecreto;

Rol creado.

SQL> GRANT DBA TO dba_pass;

Concesión terminada correctamente.

SQL> GRANT dba_pass TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.

Ya que se tiene asignado al usuario el nuevo rol, nos conectamos como el usuario

SQL> conn /
Conectado.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
DBA_PASS
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

17 filas seleccionadas.

Como vemos, el rol está asignado por default, y no nos pide el password nunca, lo que debemos hacer si es que queremos que el usuario use el password, es quitarlo del default del usuario.

SQL> conn / as sysdba
Conectado.

SQL> ALTER USER "OPS$HUGO-WIN\HUGO"
  2  DEFAULT ROLE ALL EXCEPT dba_pass;

Usuario modificado.

SQL> conn /
Conectado.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

Ahora intentamos usar el rol, validando que se requiere un password.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"

SQL> SET role dba_pass;
SET role dba_pass
*
ERROR en línea 1:
ORA-01979: falta la contraseña para el rol 'DBA_PASS' o no es válida


SQL> SET role dba_pass IDENTIFIED BY supersecreto;

Rol definido.

SQL> SELECT COUNT(1)
  2  FROM v$session;

  COUNT(1)
----------
        15

Ahora crearemos un rol identificado a través de un procedimiento.

Lo primero es crear el procedimiento.

SQL> conn / as sysdba
Conectado.

SQL> CREATE OR REPLACE PROCEDURE sec_roles
  2  authid CURRENT_USER  AS
  3  usuario VARCHAR2(50);
  4  BEGIN
  5    usuario := LOWER((sys_context('userenv',   'session_user')));
  6    DBMS_OUTPUT.PUT_LINE(usuario);
  7    IF UPPER(usuario) = 'OPS$HUGO-WIN\HUGO' THEN
  8      dbms_session.set_role('DBA_PROC');
  9    ELSE
 10      NULL;
 11    END IF;
 12  END;
 13  /

Procedimiento creado.

Es importante el punto de AUTHID, debe estar en current user si no, no funcionaría. Ya que se tiene el procedimiento creado, continuamos con el resto del rol y grants necesarios al usuario.

SQL> GRANT EXECUTE ON sec_roles TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.

SQL> CREATE role dba_proc IDENTIFIED USING sys.sec_roles;

Rol creado.

SQL> GRANT DBA TO dba_proc;

Concesión terminada correctamente.

SQL> GRANT EXECUTE ON sys.dbms_session TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.

Es necesario que el usario que queremos que use el rol, tenga un grant al procedimiento de sys.sec_roles, y tambien permisos de ejecución en sys.dbms_session.

SQL> conn /
Conectado.

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

SQL> exec sys.sec_roles;
ops$hugo-win\hugo

Procedimiento PL/SQL terminado correctamente.

SQL> select * from session_roles;

ROLE
------------------------------
DBA_PROC
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

16 filas seleccionadas.

SQL>

Hay que notar que al usuario "ops$hugo-win\hugo" jamás se le dio un grant sobre el ROL, el ROL lo obtiene con la ejecución del procedimiento.

¿Alguien tendrá un ejemplo de la autenticación global para compartir?