Usuaris i rols

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

Introducció¶

El control d'accés decideix qui es pot connectar a la base de dades i què hi pot fer. El primer pas és identificar qui accedeix: cada persona o aplicació que es connecta ho fa amb un compte propi, de manera que se li poden donar només els permisos que necessita i es pot saber qui ha fet cada operació.

En Oracle DB, aquest control es basa en dos conceptes:

Usuari: compte amb què es connecta una persona o una aplicació. Cada usuari té associat un esquema amb el mateix nom, on es guarden els objectes que crea.
Rol: conjunt de privilegis amb nom, que es pot assignar a diversos usuaris alhora.

Els usuaris s'han de crear dins de la PDB (XEPDB1), no en la CDB.

Els usuaris creats en l'arrel de la CDB són usuaris comuns, que existeixen en totes les PDB i el nom dels quals ha de començar per C##. Els usuaris d'una aplicació, en canvi, són usuaris locals de la seua PDB. Per això, abans de crear-los cal connectar-se a la PDB:

ALTER SESSION SET CONTAINER = XEPDB1;

Preparació de l'entorn d'exemple

Els exemples d'aquests apunts s'executen en la PDB XEPDB1, connectat amb l'usuari SYS. L'usuari botiga és el propietari de les taules que s'utilitzaran en els exemples:

ALTER SESSION SET CONTAINER = XEPDB1;

CREATE USER botiga IDENTIFIED BY "botiga1234"
    DEFAULT TABLESPACE users
    QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO botiga;

CREATE TABLE botiga.productes (
    id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom   VARCHAR2(100),
    preu  NUMBER(10,2),
    stock NUMBER
);

CREATE TABLE botiga.clients (
    id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom    VARCHAR2(100),
    email  VARCHAR2(100)
);

Usuaris¶

Creació d'un usuari (`CREATE USER`)¶

Un usuari es crea amb l'ordre CREATE USER, que n'indica la contrasenya i, opcionalment, l'emmagatzematge i les limitacions del compte:

CREATE USER <nom> IDENTIFIED BY "<contrasenya>"
    [DEFAULT TABLESPACE <tablespace>]
    [TEMPORARY TABLESPACE <tablespace>]
    [QUOTA {<mida> | UNLIMITED} ON <tablespace>]
    [PROFILE <perfil>]
    [PASSWORD EXPIRE]
    [ACCOUNT {LOCK | UNLOCK}];

<nom>: nom de l'usuari. Oracle el guarda en majúscules.
IDENTIFIED BY "<contrasenya>": contrasenya de l'usuari.
[DEFAULT TABLESPACE <tablespace>]: (opcional) tablespace on es creen els seus objectes (vegeu Emmagatzematge).
[TEMPORARY TABLESPACE <tablespace>]: (opcional) tablespace per a les operacions temporals.
[QUOTA {<mida> | UNLIMITED} ON <tablespace>]: (opcional) espai màxim que pot ocupar en un tablespace. Sense quota, l'usuari no hi pot crear objectes.
[PROFILE <perfil>]: (opcional) perfil de recursos i contrasenyes (vegeu Perfils).
[PASSWORD EXPIRE]: (opcional) obliga a canviar la contrasenya en el primer inici de sessió.
[ACCOUNT {LOCK | UNLOCK}]: (opcional) crea el compte bloquejat o desbloquejat (per defecte).

Documentació oficial: CREATE USER – Oracle DB

Crear un usuari no li permet connectar-se: cal concedir-li el privilegi CREATE SESSION (vegeu Privilegis).

Exemple: Crear els usuaris ana i marc

Es creen dos usuaris amb el tablespace users per defecte. ana té una quota de 10 MB, mentre que marc haurà de canviar la contrasenya en el primer inici de sessió:

CREATE USER ana IDENTIFIED BY "ana1234"
    DEFAULT TABLESPACE users
    QUOTA 10M ON users;

CREATE USER marc IDENTIFIED BY "marc1234"
    DEFAULT TABLESPACE users
    PASSWORD EXPIRE;

GRANT CREATE SESSION TO ana, marc;

S'observa que, sense l'última ordre, cap dels dos usuaris es podria connectar.

Consulta dels usuaris¶

La vista DBA_USERS mostra la informació de tots els usuaris de la base de dades:

SELECT username, account_status, default_tablespace, profile, created
    FROM dba_users
    WHERE username IN ('ANA', 'MARC');

USERNAME  ACCOUNT_STATUS  DEFAULT_TABLESPACE  PROFILE  CREATED
--------  --------------  ------------------  -------  ---------
ANA       OPEN            USERS               DEFAULT  15-JAN-26
MARC      EXPIRED         USERS               DEFAULT  15-JAN-26

S'observa que el compte de MARC apareix com a EXPIRED, ja que s'ha creat amb PASSWORD EXPIRE.

A més, la vista DBA_TS_QUOTAS mostra les quotes de cada usuari:

SELECT username, tablespace_name, bytes, max_bytes
    FROM dba_ts_quotas
    WHERE username = 'ANA';

Cada vista DBA_ té una versió ALL_ (objectes accessibles) i una versió USER_ (objectes propis), que poden consultar els usuaris sense privilegis d'administració.

Modificació d'un usuari (`ALTER USER`)¶

L'ordre ALTER USER permet canviar qualsevol de les opcions de CREATE USER. Els usos més habituals són canviar la contrasenya, modificar la quota i bloquejar o desbloquejar el compte:

ALTER USER <nom> <opció> [...];

<opció>: qualsevol de les opcions de CREATE USER, com IDENTIFIED BY, QUOTA, PROFILE, PASSWORD EXPIRE o ACCOUNT LOCK.

Documentació oficial: ALTER USER – Oracle DB

Exemple: Modificar i bloquejar usuaris

Es canvia la contrasenya d'ana, se li amplia la quota i es bloqueja el compte de marc:

ALTER USER ana IDENTIFIED BY "nova_ana1234";
ALTER USER ana QUOTA 50M ON users;
ALTER USER marc ACCOUNT LOCK;

Si marc intenta connectar-se, obté l'error següent:

ORA-28000: The account is locked.

S'observa que el compte continua existint, amb tots els seus objectes i privilegis, però no es pot utilitzar fins que es desbloqueja amb ACCOUNT UNLOCK.

Per als exemples següents, es torna a desbloquejar el compte:

ALTER USER marc ACCOUNT UNLOCK;

Eliminació d'un usuari (`DROP USER`)¶

Un usuari s'elimina amb l'ordre DROP USER:

DROP USER <nom> [CASCADE];

<nom>: nom de l'usuari que s'elimina.
[CASCADE]: (opcional) elimina també tots els objectes del seu esquema. Sense aquesta clàusula, l'ordre falla si l'usuari té algun objecte.

Documentació oficial: DROP USER – Oracle DB

DROP USER ... CASCADE elimina definitivament totes les taules i dades de l'esquema de l'usuari.

Exemple: Eliminar un usuari amb objectes

Es crea l'usuari de proves proves, amb permís per crear taules, i se li crea la taula comandes:

CREATE USER proves IDENTIFIED BY "proves1234" QUOTA 10M ON users;
GRANT CREATE SESSION, CREATE TABLE TO proves;
CREATE TABLE proves.comandes (id NUMBER PRIMARY KEY);

Com que l'usuari té objectes, no es pot eliminar directament:

DROP USER proves;

ORA-01922: CASCADE must be specified to drop 'PROVES'

Amb CASCADE, s'eliminen l'usuari i la taula:

DROP USER proves CASCADE;

S'observa que, a diferència del bloqueig, aquesta operació no es pot desfer.

Perfils (`CREATE PROFILE`)¶

Un perfil és un conjunt de límits que s'aplica als usuaris que el tenen assignat. Serveix per aplicar polítiques de seguretat a les contrasenyes (caducitat, intents fallits…) i per limitar els recursos que pot consumir cada sessió. Tots els usuaris tenen el perfil DEFAULT si no se n'indica cap altre.

CREATE PROFILE <nom> LIMIT <límit> <valor> [...];

<nom>: nom del perfil.

<límit> <valor>: cada límit que defineix el perfil. El valor pot ser un número, UNLIMITED o DEFAULT (el valor del perfil DEFAULT).

Límits habituals de CREATE PROFILE

Límit	Significat
`FAILED_LOGIN_ATTEMPTS`	Intents fallits abans de bloquejar el compte
`PASSWORD_LOCK_TIME`	Dies que el compte roman bloquejat després dels intents fallits
`PASSWORD_LIFE_TIME`	Dies que dura la contrasenya abans de caducar
`PASSWORD_GRACE_TIME`	Dies de gràcia per canviar la contrasenya un cop caducada
`PASSWORD_REUSE_MAX`	Nombre de canvis abans de poder reutilitzar una contrasenya
`SESSIONS_PER_USER`	Nombre màxim de sessions simultànies
`IDLE_TIME`	Minuts d'inactivitat abans de tancar la sessió
`CONNECT_TIME`	Minuts màxims que pot durar una sessió

Documentació oficial: CREATE PROFILE – Oracle DB

Exemple: Perfil per als usuaris de vendes

Es crea un perfil que bloqueja el compte durant un dia després de tres intents fallits, fa caducar la contrasenya cada 90 dies i limita cada usuari a dues sessions:

CREATE PROFILE perfil_vendes LIMIT
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME 1
    PASSWORD_LIFE_TIME 90
    SESSIONS_PER_USER 2;

ALTER USER ana PROFILE perfil_vendes;

Els límits del perfil es poden consultar a la vista DBA_PROFILES:

SELECT resource_name, limit
    FROM dba_profiles
    WHERE profile = 'PERFIL_VENDES' AND limit != 'DEFAULT';

S'observa que, a partir d'ara, si ana s'equivoca tres vegades de contrasenya, el seu compte es bloqueja automàticament.

Els límits de recursos (SESSIONS_PER_USER, IDLE_TIME…) només s'apliquen si el paràmetre RESOURCE_LIMIT val TRUE, que és el valor per defecte.

Rols¶

Assignar els privilegis un a un a cada usuari és difícil de mantindre. En canvi, si els privilegis s'agrupen en un rol i el rol s'assigna als usuaris, n'hi ha prou amb modificar el rol perquè el canvi afecte tots els usuaris alhora.

Creació d'un rol (`CREATE ROLE`)¶

Un rol es crea amb l'ordre CREATE ROLE:

CREATE ROLE <nom> [IDENTIFIED BY "<contrasenya>"];

<nom>: nom del rol.
[IDENTIFIED BY "<contrasenya>"]: (opcional) exigeix una contrasenya per activar el rol amb SET ROLE.

Una vegada creat, se li assignen privilegis i s'assigna als usuaris amb GRANT, igual que qualsevol privilegi (vegeu Privilegis):

CREATE ROLE rol_vendes;
GRANT rol_vendes TO ana, marc;

Documentació oficial: CREATE ROLE – Oracle DB

Rols predefinits¶

Oracle DB inclou alguns rols predefinits, que agrupen els privilegis més habituals:

CONNECT: conté el privilegi CREATE SESSION, és a dir, permet connectar-se.
RESOURCE: permet crear taules, seqüències, procediments i altres objectes en el propi esquema.
DBA: conté pràcticament tots els privilegis del sistema. Només s'ha d'assignar als comptes d'administració.

El rol RESOURCE no inclou quota en cap tablespace: cal concedir-la amb QUOTA perquè l'usuari puga crear taules amb dades.

Rols per defecte i activació (`SET ROLE`)¶

Quan un usuari inicia sessió, s'activen automàticament els seus rols per defecte, que inicialment són tots els que té assignats. Es poden restringir amb ALTER USER ... DEFAULT ROLE, de manera que alguns rols s'hagen d'activar explícitament amb SET ROLE:

ALTER USER ana DEFAULT ROLE ALL EXCEPT rol_vendes;

SET ROLE rol_vendes;

Els rols actius de la sessió actual es poden consultar a la vista SESSION_ROLES:

SELECT * FROM session_roles;

Modificació i eliminació d'un rol (`ALTER ROLE` i `DROP ROLE`)¶

L'ordre ALTER ROLE permet afegir o llevar la contrasenya d'un rol, i DROP ROLE l'elimina. En eliminar un rol, tots els usuaris que el tenien assignat perden els seus privilegis:

ALTER ROLE rol_vendes IDENTIFIED BY "vendes1234";
ALTER ROLE rol_vendes NOT IDENTIFIED;
DROP ROLE rol_vendes;

Documentació oficial: DROP ROLE – Oracle DB

Consulta dels rols¶

Els rols existents i els rols assignats a cada usuari es consulten a les vistes DBA_ROLES i DBA_ROLE_PRIVS, respectivament:

SELECT role FROM dba_roles WHERE role = 'ROL_VENDES';

SELECT grantee, granted_role, admin_option, default_role
    FROM dba_role_privs
    WHERE grantee IN ('ANA', 'MARC');

GRANTEE  GRANTED_ROLE  ADMIN_OPTION  DEFAULT_ROLE
-------  ------------  ------------  ------------
ANA      ROL_VENDES    NO            NO
MARC     ROL_VENDES    NO            YES

S'observa que ROL_VENDES no és un rol per defecte d'ANA, de manera que l'ha d'activar amb SET ROLE.

📌 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.

Usuaris i rols

Introducció¶

Usuaris¶

Creació d'un usuari (CREATE USER)¶

Consulta dels usuaris¶

Modificació d'un usuari (ALTER USER)¶

Eliminació d'un usuari (DROP USER)¶

Perfils (CREATE PROFILE)¶

Rols¶

Creació d'un rol (CREATE ROLE)¶

Rols predefinits¶

Rols per defecte i activació (SET ROLE)¶

Modificació i eliminació d'un rol (ALTER ROLE i DROP ROLE)¶

Consulta dels rols¶

Creació d'un usuari (`CREATE USER`)¶

Modificació d'un usuari (`ALTER USER`)¶

Eliminació d'un usuari (`DROP USER`)¶

Perfils (`CREATE PROFILE`)¶

Creació d'un rol (`CREATE ROLE`)¶

Rols per defecte i activació (`SET ROLE`)¶

Modificació i eliminació d'un rol (`ALTER ROLE` i `DROP ROLE`)¶