Privilegis Joan Puigcerver Ibáñez j.puigcerveribanez@edu.gva.es Llicència: CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Introducció Un privilegi és el dret a executar un tipus concret de sentència SQL o a accedir a un objecte d'un altre usuari. Els privilegis s'assignen a usuaris i a rols, de manera que cada usuari pot fer exactament allò que necessita i res més. És el que es coneix com a principi de mínim privilegi. En Oracle DB, es distingeixen dos tipus de privilegis: Privilegis de sistema: permeten fer una acció en general, com connectar-se, crear taules o consultar qualsevol taula de la base de dades. Privilegis d'objecte: permeten fer una acció sobre un objecte concret d'un altre usuari, com consultar la taula productes de l'usuari botiga. A més, l'usuari propietari d'un esquema té tots els privilegis sobre els seus propis objectes, sense necessitat que ningú els hi concedisca. Els exemples d'aquests apunts continuen l'escenari dels apunts d'usuaris: la PDB XEPDB1, l'usuari botiga amb les taules productes i clients, els usuaris ana i marc i el rol rol_vendes. Privilegis de sistema Tipus de privilegis de sistema Oracle DB té més de 200 privilegis de sistema. Els més habituals són els següents: Privilegi Permet CREATE SESSION Connectar-se a la base de dades CREATE TABLE Crear taules en el propi esquema CREATE VIEW Crear vistes en el propi esquema CREATE SEQUENCE Crear seqüències en el propi esquema CREATE PROCEDURE Crear procediments i funcions en el propi esquema CREATE ANY TABLE Crear taules en qualsevol esquema SELECT ANY TABLE Consultar qualsevol taula de la base de dades DROP ANY TABLE Eliminar qualsevol taula de la base de dades CREATE USER Crear usuaris UNLIMITED TABLESPACE Ocupar espai sense límit en tots els tablespaces Els privilegis amb ANY afecten tots els esquemes de la base de dades: s'han de reservar per als comptes d'administració. Assignació de privilegis de sistema (GRANT) Els privilegis de sistema es concedeixen amb l'ordre GRANT: GRANT <privilegis> TO <usuari_o_rol> [WITH ADMIN OPTION]; <privilegis>: llista de privilegis de sistema o de rols, separats per comes. <usuari_o_rol>: usuaris o rols que reben els privilegis. La paraula especial PUBLIC els concedeix a tots els usuaris. [WITH ADMIN OPTION]: (opcional) permet a qui els rep concedir-los, al seu torn, a altres usuaris i retirar-los. Documentació oficial: GRANT – Oracle DB Exemple: Concedir privilegis de sistema Es permet a ana crear taules i vistes en el seu esquema, i al rol rol_vendes, connectar-se: GRANT CREATE TABLE, CREATE VIEW TO ana; GRANT CREATE SESSION TO rol_vendes; Els privilegis de la sessió actual es poden consultar, connectat com ana, a la vista SESSION_PRIVS: SELECT * FROM session_privs; PRIVILEGE ---------------- CREATE SESSION CREATE TABLE CREATE VIEW S'observa que la vista inclou tant els privilegis concedits directament a ana com els que rep a través dels seus rols actius. Revocació de privilegis de sistema (REVOKE) Els privilegis de sistema es retiren amb l'ordre REVOKE: REVOKE <privilegis> FROM <usuari_o_rol>; REVOKE CREATE VIEW FROM ana; La revocació d'un privilegi de sistema no es propaga en cascada: si ana l'havia concedit a altres usuaris amb WITH ADMIN OPTION, aquests el conserven. Documentació oficial: REVOKE – Oracle DB Privilegis d'objecte Tipus de privilegis d'objecte Cada tipus d'objecte admet uns privilegis concrets. La taula següent recull els més habituals: Privilegi Objectes Permet SELECT Taules, vistes, seqüències Consultar les dades INSERT Taules, vistes Inserir files UPDATE Taules, vistes Modificar files (es pot limitar a columnes) DELETE Taules, vistes Eliminar files REFERENCES Taules Crear claus foranes que hi fan referència ALTER Taules, seqüències Modificar l'estructura de l'objecte INDEX Taules Crear índexs sobre la taula EXECUTE Procediments, funcions, paquets Executar el subprograma ALL Qualsevol Tots els privilegis aplicables a l'objecte Assignació de privilegis d'objecte (GRANT ... ON) Els privilegis d'objecte es concedeixen amb l'ordre GRANT, indicant l'objecte amb la clàusula ON: GRANT <privilegis> ON <esquema>.<objecte> TO <usuari_o_rol> [WITH GRANT OPTION]; <privilegis>: llista de privilegis separats per comes, o ALL. El privilegi UPDATE es pot limitar a unes columnes concretes: UPDATE (preu, stock). <esquema>.<objecte>: objecte sobre el qual es concedeixen. <usuari_o_rol>: usuaris, rols o PUBLIC. [WITH GRANT OPTION]: (opcional) permet a qui el rep concedir el privilegi a altres usuaris. No es pot utilitzar quan es concedeix a un rol. Exemple: Concedir privilegis sobre les taules de botiga Es permet al rol rol_vendes consultar i inserir productes, i modificar-ne només el preu i l'estoc. A més, marc pot consultar els clients: GRANT SELECT, INSERT ON botiga.productes TO rol_vendes; GRANT UPDATE (preu, stock) ON botiga.productes TO rol_vendes; GRANT SELECT ON botiga.clients TO marc; Es comprova connectat com marc, que té el rol rol_vendes: UPDATE botiga.productes SET stock = 5 WHERE id = 1; UPDATE botiga.productes SET nom = 'Ratolí' WHERE id = 1; 1 row updated. ORA-01031: insufficient privileges S'observa que marc pot modificar l'estoc, però no el nom, ja que el privilegi UPDATE només s'ha concedit sobre les columnes preu i stock. Per no haver d'escriure l'esquema davant de cada taula, es poden crear sinònims: CREATE SYNONYM productes FOR botiga.productes;. Revocació de privilegis d'objecte (REVOKE ... ON) Els privilegis d'objecte es retiren amb l'ordre REVOKE: REVOKE <privilegis> ON <esquema>.<objecte> FROM <usuari_o_rol> [CASCADE CONSTRAINTS]; [CASCADE CONSTRAINTS]: (opcional) en revocar REFERENCES o ALL, elimina també les claus foranes que l'usuari havia creat gràcies a aquest privilegi. REVOKE INSERT ON botiga.productes FROM rol_vendes; A diferència dels privilegis de sistema, la revocació d'un privilegi d'objecte es propaga en cascada: si l'usuari l'havia concedit a altres amb WITH GRANT OPTION, també el perden. Els privilegis revocats a un rol no es poden retirar a un sol usuari: REVOKE ... FROM marc no té efecte si marc el rep a través de rol_vendes. Assignació de rols Els rols s'assignen i es retiren amb les mateixes ordres que els privilegis de sistema. D'aquesta manera, un usuari rep de cop tots els privilegis del rol, i els perd tots quan se li retira: GRANT rol_vendes TO ana WITH ADMIN OPTION; REVOKE rol_vendes FROM ana; [WITH ADMIN OPTION]: (opcional) permet a l'usuari assignar el rol a altres usuaris. Un rol també es pot assignar a un altre rol, de manera que es creen jerarquies de privilegis: CREATE ROLE rol_encarregat; GRANT rol_vendes TO rol_encarregat; GRANT DELETE ON botiga.productes TO rol_encarregat; S'observa que rol_encarregat té tots els privilegis de rol_vendes i, a més, pot eliminar productes. Els privilegis rebuts a través d'un rol no estan disponibles dins dels procediments emmagatzemats1. Consulta dels privilegis El diccionari de dades inclou diverses vistes per consultar els privilegis concedits. Cada vista té la seua versió DBA_ (tots els usuaris) i USER_ (usuari actual): Vista Contingut DBA_SYS_PRIVS Privilegis de sistema concedits a usuaris i rols DBA_TAB_PRIVS Privilegis sobre objectes DBA_COL_PRIVS Privilegis sobre columnes DBA_ROLE_PRIVS Rols concedits a usuaris i rols ROLE_SYS_PRIVS Privilegis de sistema de cada rol ROLE_TAB_PRIVS Privilegis d'objecte de cada rol SESSION_PRIVS Privilegis actius en la sessió actual Exemple: Consultar els privilegis d'objecte Es consulten els privilegis concedits sobre la taula productes: SELECT grantee, privilege, grantable FROM dba_tab_privs WHERE owner = 'BOTIGA' AND table_name = 'PRODUCTES'; SELECT grantee, column_name, privilege FROM dba_col_privs WHERE owner = 'BOTIGA' AND table_name = 'PRODUCTES'; GRANTEE PRIVILEGE GRANTABLE -------------- --------- --------- ROL_VENDES SELECT NO ROL_ENCARREGAT DELETE NO GRANTEE COLUMN_NAME PRIVILEGE ---------- ----------- --------- ROL_VENDES PREU UPDATE ROL_VENDES STOCK UPDATE S'observa que els privilegis sobre columnes no apareixen a DBA_TAB_PRIVS, sinó a DBA_COL_PRIVS. Els procediments i les funcions s'executen per defecte amb els privilegis del seu propietari (definer's rights), i Oracle només té en compte els privilegis concedits directament a l'usuari, no els dels rols. ↩ 📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web. 🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.

CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez