Usuarios, privilegios y roles de Oracle

2.1. Recordatorio: Tipos de fichero según su uso

• Permanentes (permanent)
  • Datos que deben ser guardados
  • Ejemplo: Empleados contratados, nóminas pagadas, declaraciones de impuestos,…
• De movimiento (undo)
  • Cambios que deben ser incluidos en archivos permanentes
  • Ejemplo: un puesto de peaje debe guardar todos los pagos con tarjeta, y enviarlos juntos
• De maniobra (temporary)
  • Se utilizan como extensión a la RAM de un ordenador, se borran cuando el proceso termina
  • Ejemplo: caché de disco de los navegadores

2.2. ¿Por qué tantas normas?

• Disponibilidad
  • ¿Es mejor garantizar el espacio para las tablas?
  • ¿Es mejor ahorrar espacio mientras se pueda?
• Velocidad
  • Hacer crecer un fichero es lento
  • Un fichero que ha crecido poco a poco está disperso en el disco (y es más lento)
• Capacidad
  • Cada sistema de ficheros tiene un tamaño de fichero máximo

2.3. Tablespaces por defecto

• Por defecto, Oracle crea en una nueva base de datos
  • users: Tablespace asignado por defecto para los datos de todos los usuarios
  • system: Datos acerca de la instancia y del diccionario de datos
  • sysaux: Operaciones temporales del administrador que no caben en memoria
  • undo (undotbs1): Datos para deshacer las transacciones (rollback)
  • temp: Operaciones temporales de usuarios que no caben en memoria

select tablespace_name, contents from dba_tablespaces;

Mas información en:

• https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm
• https://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm

2.4. Crear un tablespace

CREATE TABLESPACE ejemplo_tablespace
  DATAFILE
  '/tablespaces/ejemplo_1.dbf'  SIZE 10M
  AUTOEXTEND ON  NEXT 200k  MAXSIZE 14M,
  '/tablespaces/ejemplo_2.dbf'  SIZE 10M
  AUTOEXTEND ON  NEXT 200k  MAXSIZE 14M;

Más en docs.oracle.com

2.5. ¿Por qué es tan complicado?

• Esta flexibilidad permite:
  • Que cada usuario tenga sus tablespaces
  • Que cada tablespace esté en discos distintos (rapidez)
  • Que un tablespace se localice en varios discos (rapidez, tamaño)
  • Mover tablespaces una vez creados

2.6. Ejercicio: Llena un tablespace

• Crea un tablespace con un tamaño inicial de 10MB, y un tamaño máximo de 14MB
• Crea una tabla sobre el tablespace
• Inserta datos en la tabla hasta conseguir el error ORA-01653

2.7. Ejercicio: tablespace por defecto

• Consulta el tablespace por defecto de los usuarios (dba_users)
• Cambia el tablespace por defecto de un usuario (alter user)
• Consulta el tablespace por defecto por (DATABASE_PROPERTIES)
• Cambia el tablespace por defecto por defecto (alter database)
  • Nota: esto cambia el tablespace por defecto de todos los usuarios existentes
• Cambiar una tabla de tablespace: ALTER TABLE ... MOVE TABLESPACE

2.8. Ejercicio: más tablespaces

1. Crea un tablespace PRUEBA1
   • inicialmente 10M, máximo 20M
2. Crea un usuario
   • no le digas tablespace por defecto
   • pero que al crearlo su tablespace por defecto sea PRUEBA1
3. Crea una tabla
   • MISDATOS(DATOS VARCHAR(255))
4. Llena la tabla hasta que no quede espacio en PRUEBA1
5. Crea un tablespace PRUEBA2
   • tamaño inicial igual al máximo, 30M
6. mueve MISDATOS a PRUEBA2
7. Vuelve a llenar la tabla hasta que se llene PRUEBA2

2.9. Conceptos de almacenamiento

Más información en Oracle.com

• Oracle tiene dos modos de autentificar usuarios
  • Autentificación de sistema operativo (parámetro os_authent_prefix)
  • Autentificación con seguridad nativa de oracle
• Al instalarlo, elegimos que el grupo wheel era administrador

3.1. Creación de usuarios

CREATE USER usuario IDENTIFIED BY contrasena
  DEFAULT TABLESPACE tablespace
  TEMPORARY TABLESPACE tablespace
  QUOTA UNLIMITED ON tablespace
  QUOTA tamano ON tablespace
  ACCOUNT LOCK
  ACCOUNT UNLOCK

3.2. Modificación de usuario

• Modificación de un usuario ya creado

    ALTER USER usuario
      cualquier opcion valida al crear usuario
  

• Borrado de usuario

    DROP USER usuario
  

4.1. Privilegios de Oracle

4.2. Sintaxis de Grant

grant PRIVILEGIO1,PRIVILEGIO2,...,PRIVILEGION 
on OBJETO
to USUARIO
with grant option;

create table alumnos(...);
create user profesor ...;
grant select on alumnos to profesor;

Fuente: docs.oracle.com

4.3. Ejercicio

• Crea un usuario CONPERMISOS
  • Que tenga privilegios de connect y resource
  • Utilízalo para crear una tabla DATOS(TEXTO varchar2(255),numero integer)
  • Inserta datos (puede que necesite cuota)
• Crea un usuario LIMITADO
• Haz que CONPERMISOS de privilegios a LIMITADO para que:
  • Pueda leer todos los campos de la tabla DATOS
  • Pueda actualizar el campo NUMERO de tabla DATOS
  • Pero no pueda modificar el campo TEXTO, ni borrar filas, ni insertar filas

4.4. Ejercicio

• Haz que el usuario CONPERMISOS tenga una cuota de 100k en el tablespace USERS
• Llena toda su cuota insertando filas en la tabla DATOS
• ¿Qué ocurre?

4.5. Quitar privilegios

• Los privilegios se quitan con revoke
• Cuando un usuario pierde un privilegio, los pierden también todos los que recibieron el mismo privilegio a través de él
  • Por la cláusula with grant option

connect sys/*******
grant select on unatabla to unusuario with grant option;

connect unusuario/*******
grant select on unatabla to otrousuario;

connect sys/*******
revoke select on unatabla from unusuario;

-- AQUI NI unusuario NI otrousuario TIENEN PRIVILEGIO SOBRE unatabla

4.6. Privilegios de sistema

• No se aplican sobre tablas/objetos concretos
  • CREATE ANY TABLE, CREATE ANY VIEW,…
  • CREATE SESSION, CREATE SYNONYM,..
  • UNLIMITED TABLESPACE, SYSDBA,…

5.1. Sintaxis de roles

create role NOMBREROL;
grant PRIVILEGIOS on OBJETOS to NOMBREROL;
grant NOMBREROL to USUARIO;

Fuente: docs.oracle.com

5.2. Ejercicio

Se pueden asignar privilegios a PUBLIC, para que todos los usuarios tengan dicho privilegio. Decide si PUBLIC es un usuario o un rol, y compruébalo en las tablas de diccionario.

5.3. Ejercicio

• Imagina que
  1. Creas un rol con sus permisos
  2. Le asignas privilegios
  3. Lo asignas al usuario USUARIOANTES
  4. Quitas algún privilegio del rol
  5. Asignas el rol al usuario USUARIODESPUES
• El usuario USUARIODESPUES, ¿tiene más, menos o los mismos privilegios que USUARIOANTES?
  • O lo que es lo mismo, ¿los permisos del rol se copian al usuario o se enlazan?

5.4. ¿Qué privilegios tengo?

• Un usuario puede tener muchos permisos otorgados directamente y a través de un rol
• Además, algunos roles son por defecto, pero otros hay que activarlos con SET ROLE

select * from session_roles;
select * from session_privs;

5.5. Consultas recursivas

• start with: Primeras filas seleccionadas de la tabla
• connect by:
  • prior: Hace referencia a la columna de la fila previamente ya seleccionada
  • Columna sin prior: Hace referencia a la columna de la fila actual

select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;

select * from dba_sys_privs  where grantee = '&USER' or grantee in (
  select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER'
)
order by 1,2,3;

select * from dba_tab_privs  where grantee = '&USER' or grantee in (
  select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER'
)
order by 1,2,3,4;

Ver connect by prior

6.1. Creación de perfiles

CREATE PROFILE nombreperfil LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   IDLE_TIME                  1
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000; 

ALTER SYSTEM SET resource_limit = TRUE scope = BOTH 

• Nota: Según la fuente, los tiempos se miden en días. Se pueden especificar fracciones de día.
  • Pero a mí me funcionan como minutos

Fuente: docs.oracle.com

6.2. Asignación de perfil a un usuario

• En la creación (create user), o posteriormente

    alter user USUARIO profile NOMBREDEPERFIL
  

6.3. Ejercicio

• Haz que el usuario LIMITADO
  • se quede sin sesión tras 1 minuto de inactividad
  • se quede sin sesión a los 2 minutos de conectarse, aunque no haya estado inactivo

6.4. Ejercicio

• Utiliza las vistas de Oracle para conocer los límites del profile por defecto.

6.5. Ejercicio

• Usa la opción PASSWORD_VERIFY_FUNCTION para evitar que las contraseñas sean más largas de tres caracteres