Oracle 23c - Privilegios de Esquema

Otra nueva característica que nos trae Oracle Database 23c es la posibilidad de otorgar permisos en forma sencilla sobre todos los objetos de un esquema, sin necesidad de identificarlos individualmente ni de agregar nuevos permisos al agregar nuevos objetos.

Escenario

Tenemos una aplicación con 600 tablas distribuidas en los esquemas Ventas y RRHH. Para poder analizar las ventas, desarrollamos un conjunto de reportes que se acceden con un nuevo usuario VentasR que debe tener acceso a las tablas del esquema Ventas pero solo para lectura.

Hasta Oracle 23c, para poder hacer esto teníamos dos opciones:

• Otorgar el privilegio READ o SELECT al usuario VentasR de cada una de las tablas del esquema Ventas en forma individual. Si en el esquema Ventas se agregan nuevas tablas, es necesario que otorgar el privilegio de READ o SELECT a cada nueva tabla.
• Otorgar el privilegio de sistema SELECT ANY TABLE al usuario VentasR, con lo cual no es necesario darle privilegios individuales al agregar nuevas tablas a Ventas, pero el usuario podrá ver también información de las tablas del esquema RRHH.

Como vemos, una solución es la correcta desde el punto de vista de requerimiento (otorgar permisos individuales) aunque es costosa de implementar y mantener, y la otra es sencilla desde el punto de vista de su otorgamiento y no requiere más interacción si se agregan tablas, pero introduce un riesgo de seguridad al permitir el acceso a TODAS las tablas de la base de datos en vez de sólo las del esquema Ventas.

Otorgar Privilegios de Esquema

Oracle Database 23c incluye un nuevo tipo de privilegio adicional al de sistema o de objeto, que es el privilegio de esquema. Con el, podemos otorgar un privilegio a todos los objetos existentes en un esquema, sin necesidad de identificar los mismos en forma individual. Para nuestro caso, la sintaxis sería la siguiente:

GRANT SELECT ANY TABLE ON SCHEMA Ventas TO VentasR;

Veamos ahora cómo funciona este privilegio, probando con un ejemplo en Oracle 23c. Primero, vamos a crear dos esquemas (Ventas y RRHH) con un par de tablas cada uno, y un esquema VentasR que deberá acceder a los datos de Ventas (hacemos estos directamente con el usuario SYSTEM para ahorrar tiempo).

CREATE USER Ventas IDENTIFIED BY Ventas23c QUOTA UNLIMITED ON users;
GRANT db_developer_role TO Ventas;
CREATE TABLE Ventas.TablaV1 (ID NUMBER(10), Descripcion VARCHAR(100));
CREATE TABLE Ventas.TablaV2 (ID NUMBER(10), Descripcion VARCHAR(100));
INSERT INTO Ventas.TablaV1 VALUES (1, 'TablaV1 - 1');
INSERT INTO Ventas.TablaV1 VALUES (2, 'TablaV1 - 2');
INSERT INTO Ventas.TablaV2 VALUES (1, 'TablaV2 - 1');
INSERT INTO Ventas.TablaV2 VALUES (2, 'TablaV2 - 2');
--
CREATE USER RRHH IDENTIFIED BY RRHH23c QUOTA UNLIMITED ON users;
GRANT db_developer_role TO RRHH;
CREATE TABLE RRHH.TablaV1 (ID NUMBER(10), Descripcion VARCHAR(100));
CREATE TABLE RRHH.TablaV2 (ID NUMBER(10), Descripcion VARCHAR(100));
INSERT INTO RRHH.TablaV1 VALUES (1, 'TablaV1 - 1');
INSERT INTO RRHH.TablaV1 VALUES (2, 'TablaV1 - 2');
INSERT INTO RRHH.TablaV2 VALUES (1, 'TablaV2 - 1');
INSERT INTO RRHH.TablaV2 VALUES (2, 'TablaV2 - 2');
--
CREATE USER VentasR IDENTIFIED BY VentasR23c QUOTA UNLIMITED ON users;
GRANT CONNECT TO VentasR;

A continuación, nos vamos a conectar con el usuario VentasR y ejecutar lo siguiente:

SELECT * FROM Ventas.TablaV1;
SELECT * FROM Ventas.TablaV2;
SELECT * FROM RRHH.TablaV1;

Lo cual nos va a generar los siguientes errores, ya que el usuario VentasR no posee permisos de SELECT sobre ninguna de esas tablas:

Con el usuario SYSTEM le vamos a otorgar el privilegio de sistema "SELECT ANY TABLE" y vamos a volver a ejecutar las sentencias SELECT:

SELECT * FROM Ventas.TablaV1;
SELECT * FROM Ventas.TablaV2;
SELECT * FROM RRHH.TablaV1;

Como vemos a continuación, podemos consultar las tablas del esquema Ventas, pero también las del esquema RRHH, lo cual es un problema de seguridad:

Volviendo al usuario SYSTEM, vamos a revocar el permiso SELECT ANY TABLE y vamos a otorgar el mismo nuevamente pero con la opción ON SCHEMA para indicarle que aplique solo a los objetos del esquema Ventas:

REVOKE SELECT ANY TABLE FROM VentasR;
GRANT SELECT ANY TABLE ON SCHEMA Ventas TO VentasR;

El resultado es el siguiente:

Revoke correcto.

Grant correcto.

Ahora si volvemos a ejecutar la consulta con el usuario VentasR, y vemos que podemos consultar las tablas de Ventas pero no las de RRHH:

Como último paso, vamos a crear una tercer tabla en el esquema Ventas:

CREATE TABLE Ventas.NuevaTabla (ID NUMBER(10), Descripcion VARCHAR(100));
INSERT INTO Ventas.NuevaTabla VALUES (1, 'Nueva');

Y podemos validar que la misma puede ser consultada automáticamente por el usuario VentasR sin necesidad de otorgar un permiso individual sobre la misma:

Consultando Privilegios de Esquema

La incorporación de esta nueva funcionalidad implica nuevas tablas del diccionario de datos donde se pueden consultar los privilegios de esquema que han sido otorgados:

• DBA_SCHEMA_PRIVS
• ROLE_SCHEMA_PRIVS
• USER_SCHEMA_PRIVS
• SESSION_SCHEMA_PRIVS
• V$ENABLEDSCHEMAPRIVS

Aquí podemos ver un ejemplo de su uso, donde nos muestra el privilegio otorgado a VentasR:

Conclusión

Con esta nueva funcionalidad se hace mucho más fácil gestionar los permisos otorgados a nivel de objetos de usuario. Ya no es necesario actualizar los permisos otorgados de un usuario A a un usuario B ante cualquier objeto que se agregue al usuario A.

Si bien demostramos el mismo con el permiso de SELECT, puede usarse con cualquier privilegio como se detalla en la documentación.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.