Administración de Usuarios, Roles y Privilegios en Oracle

1. Consultar Vistas de Perfiles y Roles

Para visualizar la configuración actual de perfiles y roles, ejecutamos las siguientes consultas:

SELECT * FROM dba_profiles;
SELECT * FROM dba_roles;

Nota: Si no se han creado perfiles o roles adicionales, se mostrarán los perfiles y roles predeterminados de Oracle.

Los perfiles limitan el uso de recursos, mientras que los roles agrupan privilegios para asignarlos a usuarios.

2. Usuarios y Privilegios de Conexión

SELECT * FROM dba_users;

Esta consulta muestra los usuarios creados y los predeterminados de Oracle. El usuario HR, con tablespace USER, está bloqueado por defecto. Para desbloquearlo y asignarle una contraseña:

ALTER USER HR ACCOUNT UNLOCK;
ALTER USER HR IDENTIFIED BY hr;

3. Privilegios de Usuarios

Para ver los roles, privilegios de objeto y privilegios de sistema asignados a cada usuario:

SELECT * FROM dba_role_privs;
SELECT * FROM dba_tab_privs;
SELECT * FROM dba_sys_privs;

4. Crear Usuario "administrador"

Conectarse como usuario SYSTEM y crear el usuario "administrador":

CREATE USER administrador
IDENTIFIED BY admin
DEFAULT TABLESPACE USERS
QUOTA 500K ON USERS
TEMPORARY TABLESPACE TEMP;

Si el usuario se crea desde una interfaz gráfica, la cuota se asigna posteriormente:

ALTER USER administrador DEFAULT TABLESPACE USERS QUOTA 500K ON USERS;

5. Conexión del Usuario "administrador"

Al intentar conectar con CONNECT administrador/admin, se produce un error por falta del privilegio CREATE SESSION.

6. Privilegios del Usuario "administrador"

Para verificar los privilegios del usuario "administrador":

SELECT * FROM dba_role_privs WHERE grantee='ADMINISTRADOR';
SELECT * FROM dba_tab_privs WHERE grantee='ADMINISTRADOR';
SELECT * FROM dba_sys_privs WHERE grantee='ADMINISTRADOR';

Nota: Las búsquedas en Oracle distinguen entre mayúsculas y minúsculas.

7. Otorgar Privilegio CREATE SESSION

Conectarse como SYSTEM y otorgar el privilegio:

GRANT CREATE SESSION TO administrador;

8. Crear Usuario "prueba00"

Intentar crear el usuario "prueba00" como "administrador" genera un error ORA-01031: privilegios insuficientes.

CREATE USER prueba00
IDENTIFIED BY prueba00
DEFAULT TABLESPACE USERS
QUOTA 0K ON USERS
TEMPORARY TABLESPACE TEMP;

9. Otorgar Privilegio CREATE USER

Otorgar el privilegio CREATE USER al usuario "administrador" (conectado como SYSTEM):

GRANT CREATE USER TO administrador;

Ahora se puede crear el usuario "prueba00" sin errores.

10. Usuarios con Privilegio CREATE USER

Para ver los usuarios con el privilegio CREATE USER:

SELECT * FROM dba_sys_privs WHERE privilege='CREATE USER';

11. Usuarios con Privilegio CREATE SESSION

SELECT * FROM dba_sys_privs WHERE privilege='CREATE SESSION';

12. Crear Rol "administrador"

Intentar crear un rol con el mismo nombre que un usuario existente genera un error.

13. Usuarios/Roles con Privilegio CREATE ROLE

SELECT * FROM dba_sys_privs WHERE privilege='CREATE ROLE';

14. Crear Rol ADMIN

CREATE ROLE admin;
GRANT CREATE SESSION, CREATE USER, CREATE ROLE TO admin;
GRANT admin TO administrador;

15. Privilegios Directos de "administrador"

SELECT * FROM dba_sys_privs WHERE grantee='ADMINISTRADOR';
REVOKE CREATE SESSION FROM ADMINISTRADOR;
GRANT ADMIN TO ADMINISTRADOR;

16. Crear Usuario "prueba01"

CREATE USER prueba01 IDENTIFIED BY prueba01;

17. Tablespaces y Cuotas

SELECT username, tablespace_name, max_bytes FROM dba_ts_quotas WHERE username IN ('SYS','ADMINISTRADOR','SYSTEM','PRUEBA01','PRUEBA00');

18. Crear Tablespaces NOMINA y ACADEMO

CREATE TABLESPACE academo DATAFILE 'academo.dbf' SIZE 1M AUTOEXTEND ON NEXT 200K MAXSIZE 1400K DEFAULT STORAGE (INITIAL 16K NEXT 16K MINEXTENTS 1 MAXEXTENTS 3);
CREATE TABLESPACE nomina DATAFILE 'nomina.dbf' SIZE 1M AUTOEXTEND ON NEXT 100K MAXSIZE 1500K DEFAULT STORAGE (INITIAL 16K NEXT 16K MINEXTENTS 1 MAXEXTENTS 3);

19. Crear Tablespaces Temporales

CREATE TEMPORARY TABLESPACE temp_academo TEMPFILE 'temp_academo.tmp' SIZE 500K AUTOEXTEND ON NEXT 50K MAXSIZE 600K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K;

Se omite DEFAULT STORAGE al usar EXTENT MANAGEMENT LOCAL.

20. Crear Usuario "prueba02"

CREATE USER prueba02 IDENTIFIED BY prueba02 DEFAULT TABLESPACE nomina TEMPORARY TABLESPACE temp_nomina;

21. Asignar Tablespaces a "prueba01"

ALTER USER prueba01 DEFAULT TABLESPACE academo TEMPORARY TABLESPACE temp_academo;

22. Tablespaces y Cuotas de "prueba01" y "prueba02"

SELECT username, tablespace_name, max_bytes FROM dba_ts_quotas WHERE username IN ('PRUEBA01','PRUEBA02');

23. Crear Rol CONEXIÓN

CREATE ROLE conexion;
GRANT CREATE SESSION TO conexion;

24. Asignar Rol CONEXIÓN

GRANT conexion TO prueba00, prueba01, prueba02;

25. Roles de "prueba00", "prueba01" y "prueba02"

SELECT grantee, granted_role FROM dba_role_privs WHERE grantee IN ('PRUEBA00','PRUEBA01','PRUEBA02');

26. Crear Tabla CODIGOS (Intento 1)

Conectarse como "prueba01" e intentar crear la tabla CODIGOS en el tablespace ACADEMO genera un error por falta de privilegios.

27. Crear Rol DESARROLLO

CREATE ROLE desarrollo;
GRANT CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW TO desarrollo;
GRANT desarrollo TO prueba00, prueba01, prueba02;

28. Crear Tabla CODIGOS (Intento 2)

Intentar crear la tabla CODIGOS nuevamente como "prueba01" genera un error ORA-01950 por falta de cuota en el tablespace ACADEMO.

29. Asignar Cuota Ilimitada a "prueba01"

ALTER USER prueba01 QUOTA UNLIMITED ON academo;

30. Crear Tabla CODIGOS (Éxito)

Ahora se puede crear la tabla CODIGOS correctamente.

31. Asignar Cuota Ilimitada a "prueba02"

ALTER USER prueba02 QUOTA UNLIMITED ON nomina;

32. Información de "prueba00", "prueba01" y "prueba02"

SELECT grantee AS "Usuario", granted_role AS "Rol" FROM dba_role_privs WHERE grantee IN ('PRUEBA00','PRUEBA01','PRUEBA02');
SELECT grantee AS "Usuario", owner AS "Privilegio" FROM dba_tab_privs WHERE grantee IN ('PRUEBA00','PRUEBA01','PRUEBA02');
SELECT username AS "Usuario", tablespace_name AS "Tablaspace", bytes AS "Quota" FROM dba_ts_quotas WHERE username IN ('PRUEBA00','PRUEBA01','PRUEBA02');

33. Asignar Cuota Cero a "prueba01"

ALTER USER prueba01 QUOTA 0K ON academo;

Los objetos existentes se mantienen, pero no se pueden crear nuevos.

34. Modificar Cuota (Intento de "prueba01")

El usuario "prueba01" no puede modificar su propia cuota. Se requiere el privilegio ALTER USER.

35. Modificar Contraseña de "prueba01"

El usuario "prueba01" puede modificar su propia contraseña.

36. Usuarios/Roles con Privilegio ALTER USER

SELECT grantee FROM dba_sys_privs WHERE privilege='ALTER USER';

37. Eliminar Usuario Conectado

No se puede eliminar un usuario mientras está conectado.

38. Otorgar DROP USER a ADMIN

GRANT DROP USER TO admin;

39. Usuarios/Roles con Privilegio DROP USER

SELECT grantee FROM dba_sys_privs WHERE privilege='DROP USER';

40. Crear Usuario "prueba03"

CREATE USER prueba03 IDENTIFIED BY prueba03 QUOTA 500K ON academo QUOTA 200K ON nomina TEMPORARY TABLESPACE temp;