Administración de Oracle: Tablespaces, Clusters, Perfiles, Usuarios y Privilegios

Ejercicios de administración de bases de datos Oracle

1. Creación y modificación de tablespaces

Se crea el tablespace diccionario con dos archivos de datos, gestión de extensiones mediante diccionario y almacenamiento inicial de 80K:

CREATE TABLESPACE diccionario DATAFILE 'primero.dbf' SIZE 20M AUTOEXTEND ON NEXT 5M MAXSIZE 100M, 'segundo.dbf' SIZE 5M AUTOEXTEND ON NEXT 30M MAXSIZE 1G EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (INITIAL 80K NEXT 80K MAXEXTENTS 20) ONLINE;

Añadir un nuevo archivo de datos:

ALTER TABLESPACE diccionario ADD DATAFILE 'tercero.dbf' SIZE 100M AUTOEXTEND ON NEXT 25M MAXSIZE UNLIMITED;

Renombrar el archivo de datos anterior a ultimo.dbf:

ALTER TABLESPACE diccionario RENAME DATAFILE 'tercero.dbf' TO 'ultimo.dbf';

2. Consulta de datafiles por tablespace

Consulta que muestra los tablespaces con dos o más datafiles, ordenados de forma descendente por número de archivos:

SELECT a.tablespace_name "NOMBRE TABLESPACE", COUNT(a.file_name) "Número de datafiles", a.status "Estado" FROM dba_data_files a GROUP BY a.tablespace_name, a.status HAVING COUNT(a.file_name) >= 2 ORDER BY 2 DESC;

3. Creación de un cluster e índice

Creación del cluster profesor_departamento y su índice asociado:

CREATE CLUSTER profesor_departamento (n_dep NUMBER(4)) TABLESPACE tblspc_1 PCTFREE 30 STORAGE (INITIAL 8 NEXT 8 PCTINCREASE 100 MAXEXTENTS 50);
CREATE INDEX idx_profesor_departamento ON CLUSTER profesor_departamento TABLESPACE user;

4. Consulta de cuotas de tablespace por usuario

SELECT a.tablespace_name "Quota total", a.username, bytes/1024 "Quota en KB" FROM dba_ts_quotas a WHERE username = :users GROUP BY a.tablespace_name, a.bytes;

5. Creación de un perfil limitado

Se crea el perfil PERFIL_LIMITADO con las siguientes restricciones:

CREATE PROFILE "PERFIL_LIMITADO" LIMIT IDLE_TIME 10 SESSIONS_PER_USER 1 PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 7 PASSWORD_LOCK_TIME 0.06 FAILED_LOGIN_ATTEMPTS 5;

6. Creación de usuario

CREATE USER user_1 IDENTIFIED BY examenprofile PROFILE perfil_limitado DEFAULT TABLESPACE tblspc_1 QUOTA 15M ON tblspc_1 QUOTA 300M ON user;

7. Concesión y revocación de privilegios

a) Privilegios de consulta a PUBLIC

El usuario SYSTEM otorga privilegios de consulta sobre la tabla A de su esquema a todos los usuarios:

GRANT SELECT ON tableA TO PUBLIC;

b) Privilegios de inserción con transmisión

El usuario user_1 otorga privilegios de inserción sobre las columnas a y b de la tabla A a user_2, permitiéndole transmitirlos:

GRANT INSERT (a) ON user_1.A TO user_2 WITH GRANT OPTION;
GRANT INSERT (b) ON user_1.A TO user_2 WITH GRANT OPTION;

c) Inserción sobre una columna

El usuario user_2 otorga privilegios de inserción sobre la columna a de la tabla A de user_1 a user_3:

GRANT INSERT (a) ON user_1.A TO user_3;

d) Privilegio de creación de tablas

El usuario SYSTEM otorga a user_1 el privilegio de crear tablas en cualquier esquema, con posibilidad de transmitirlo:

GRANT CREATE ANY TABLE TO user_1 WITH GRANT OPTION;

e) Otorgar cualquier rol

El usuario SYSTEM permite a user_2 otorgar cualquier rol a cualquier usuario:

GRANT GRANT ANY ROLE TO user_2;

f) Consulta de privilegios de sistema directos

user_2 consulta los privilegios de sistema que tiene otorgados directamente:

SELECT privilege FROM user_sys_privs;

g) Consulta de roles asignados

SELECT * FROM dba_role_privs WHERE grantee IN ('USER_1', 'USER_2');

h) Privilegios de sistema de ANA y JUAN

Consulta del diccionario dba_sys_privs para ver los privilegios concedidos a ANA y JUAN:

SELECT grantee, privilege FROM dba_sys_privs WHERE grantee = 'ANA' OR grantee = 'JUAN' ORDER BY grantee;