Gestión de usuarios, privilegios y roles en Oracle: sentencias GRANT, REVOKE y CREATE ROLE

Gestión de Usuarios en Bases de Datos

Para modificar los usuarios, utilizaremos la sentencia ALTER.

Creación de un Usuario

La sintaxis básica para crear un usuario es la siguiente:

CREATE USER <nombre>
IDENTIFIED BY <password>
[DEFAULT TABLESPACE <nombretablespace>]
[TEMPORARY TABLESPACE <nombretablespace>]
[QUOTA {<espacio> {K | M} | UNLIMITED} ON <nombretablespace>]
[PROFILE <nombreperfil>];

Consultas de Usuarios

• Para ver todos los usuarios creados:

SELECT * FROM all_users;

• Para ver los tablespaces asignados a cada usuario:

SELECT username, default_tablespace FROM dba_users;

Gestión de Privilegios

Roles Predeterminados y sus Privilegios

A continuación, se detallan algunos roles comunes y los privilegios asociados:

Rol (Función)	Privilegios
CONNECT	ALTER SESSION, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
RESOURCE	CREATE PROCEDURE, CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER
DBA	Todos los privilegios
EXP_FULL_DATABASE	SELECT ANY TABLE, BACKUP ANY TABLE

Privilegios sobre Objetos

Los principales privilegios sobre objetos son: ALTER, DELETE, EXECUTE, INSERT, SELECT y UPDATE.

Sintaxis para Conceder Privilegios sobre Objetos

GRANT [privilegio1 [, privilegio2] ... | ALL PRIVILEGES]
ON <objeto>
TO {usuario | Rol | PUBLIC} [, {usuario | Rol | Public} ] ...
[WITH GRANT OPTION];

Ejemplos de Concesión de Privilegios sobre Objetos

• Milagros concede a Fran SELECT e INSERT en T1:

GRANT SELECT, INSERT ON T1 TO Fran;

Milagros concede a Fran todos los privilegios sobre T1:

GRANT ALL ON T1 TO Fran;

Mili concede sobre T1 a todos (PUBLIC):

GRANT ALL ON T1 TO PUBLIC;

Mili da a Juan la modificación del campo Temperatura en T1:

GRANT UPDATE (temperatura) ON T1 TO Juan;

Mili da a Juan INSERT sobre T1 y además le permite conceder este privilegio a otros:

GRANT INSERT ON T1 TO Juan WITH GRANT OPTION;

Privilegios de Sistema

El formato de GRANT para otorgar privilegios del sistema es:

GRANT {Privilegio | Rol} [ , {Privilegio | Rol | ALL PRIVILEGES} ] ...
TO {usuario | Rol | PUBLIC} , [ {usuario | Rol | PUBLIC} ] ...
[WITH ADMIN OPTION];

Ejemplos de Concesión de Privilegios de Sistema

• Creación del usuario Pedro y concesión del privilegio de iniciar sesión:

CREATE USER Pedro IDENTIFIED BY oracle DEFAULT TABLESPACE Prueba1;
GRANT CREATE SESSION TO Pedro;

Concesión del Rol CONNECT a Pedro:

GRANT CONNECT TO Pedro;

Concesión del privilegio de administrador (DBA) a Pedro y Juan:

GRANT DBA TO Pedro, Juan;

Mili obtiene el privilegio de borrar usuarios y la capacidad de concederlo:

GRANT DROP USER TO Mili WITH ADMIN OPTION;

Permitir que todos los usuarios puedan hacer SELECT en cualquier tabla de cualquier usuario:

GRANT SELECT ANY TABLE TO PUBLIC;

Retirada de Privilegios

Retirada de Privilegios sobre Objetos

Sintaxis para retirar privilegios de objeto a un usuario:

REVOKE {privilegio1 [, Privilegio2] ... | ALL PRIVILEGES}
ON [usuario.]objeto
FROM {usuario | Rol | PUBLIC} [, {usuario | Rol | PUBLIC} ] ...;

Retirada de Privilegios de Sistema

Sintaxis para retirar privilegios de sistema:

REVOKE {privilegio1 [, Privilegio2] ... | ALL PRIVILEGES}
FROM {usuario | Rol | PUBLIC} [, {usuario | Rol | PUBLIC} ] ...;

Ejemplos de Retirada de Privilegios

• Mili retira los privilegios SELECT y UPDATE de T1 a Fran:

REVOKE SELECT, UPDATE ON T1 FROM Fran;

Mili retira todos los privilegios concedidos a Francisco y Juan sobre T1:

REVOKE ALL PRIVILEGES ON T1 FROM Francisco, Juan;

Retirada del privilegio de borrar usuarios a Milagros, y de DBA a Juan y Pedro:

REVOKE DROP USER FROM Milagros;
REVOKE DBA FROM Juan, Pedro;

Gestión de Roles

Creación de Roles

• Creación de un Rol con contraseña (requiere SET ROLE para activarse):

CREATE ROLE <NombreRol> IDENTIFIED BY <123>;

La contraseña indica que el usuario que desee usar estos privilegios tiene que introducir la clave de acceso en la orden SET ROLE para activar el Rol.

• Creación de un Rol sin contraseña:

CREATE ROLE <NombreRol> 
-- Ejemplo: CREATE ROLE Acceso

Asignación de Privilegios a Roles

Una vez creados los roles, se les deben conceder privilegios con la orden GRANT. Vamos a dar privilegios de SELECT e INSERT en EMPLE, INSERT en DEPART y CREATE SESSION para poder iniciar sesión en Oracle:

GRANT SELECT, INSERT ON emple TO acceso;
GRANT INSERT ON depart TO acceso;
GRANT CREATE SESSION TO acceso;

Asignación de Roles a Usuarios

1. Se crea un usuario para concederle posteriormente el rol creado:

CREATE USER Miguel IDENTIFIED BY Oracle
DEFAULT TABLESPACE Prueba1 QUOTA 200K ON Prueba1;

2. Se concede el Rol de Acceso al usuario creado:

GRANT acceso TO Miguel;

Modificación y Eliminación de Roles

• Retiramos el Rol de Acceso el privilegio de INSERT en la tabla EMPLE. Ahora, si Miguel intenta insertar una fila en EMPLE no podrá:

REVOKE INSERT ON emple FROM acceso;

• Eliminación de un Rol:

DROP ROLE acceso;