Usuarios, privilegios y roles de Oracle

Índice

1. Introducción
2. Tablespaces
3. Usuarios
4. Privilegios
5. Roles
6. Perfiles
7. Referencias

1. Introducción

Oracle puede utilizarse simultáneamente por varios procesos y clientes
Cada uno puede tener distintos permisos y capacidades
- Espacio de disco disponible
- Gasto en CPU, red
- Acceso a diferentes tablas de datos

2. Tablespaces

Oracle almacena datos en los tablespaces
- Conjuntos de ficheros
- Normas para su tamaño: inicial, máximo, crecimiento
Cada tablespace puede usarse para diferentes funciones
- Datos de usuario o del sistema: permanent tablespace
- Datos de recuperación: undo tablespace
- Datos temporales: temporary tablespace

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:

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

create table datos(valor varchar(2048)) tablespace ejemplo_tablespace; begin for j in 1..1 loop for i in 1..1000 loop insert into datos values( 'datos ' || i ); end loop; commit; end loop; end; /

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

ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>

2.8. Ejercicio: más tablespaces

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

create tablespace PRUEBA1 DATAFILE '/tablespaces/prueba1.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 20M;

ALTER DATABASE DEFAULT TABLESPACE prueba1;

alter session set "_oracle_script"=true;

create user usuarioprueba1 identified by a; grant connect,resource to usuarioprueba1; alter user usuarioprueba1 quota unlimited on prueba1;

connect usuarioprueba1/a create table misdatos(datos varchar(255));

select * from dba_tables where tABLE_NAME='MISDATOS';

alter session set current_schema=prueba1;

BEGIN LOOP insert into misdatos values('hola); commit; end loop;

END; /

create tablespace PRUEBA2 DATAFILE '/tablespaces/prueba2.dbf' SIZE 30M;

alter user usuarioprueba1 quota unlimited on prueba2;

connect usuarioprueba1/a alter table misdatos move tablespace prueba2;

begin LOOP for i in 1 .. 10 loop insert into misdatos values('hola'); end loop; commit; end loop;

END; /

2.9. Conceptos de almacenamiento

Más información en Oracle.com

https://www.erpgreat.com/oracle-database/tablespace-datafile-data-block-segment-extent.htm Data Blocks

At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk. You set the data block size for every Oracle database when you create the database. This data block size should be a multiple of the operating system's block size within the maximum limit. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.

Extents

The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks that is allocated for storing a specific type of information.

Segments

The level of logical database storage above an extent is called a segment. A segment is a set of extents that have been allocated for a specific type of data structure, and that all are stored in the same tablespace. For example,each table's data is stored in its own data segment, while each index's datails stored in its own index segment. Oracle allocates space for segments in extents. Therefore, when the existing extents of a segment are full, Oracle allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk. The segments also can span files, but the individual extents cannot.

Databases and tablespaces

An Oracle database is comprised of one or more logical storage units called tablespaces. The database's data is collectively stored in the database's tablespaces.

Tablespaces and datafiles

Each tablespace in an Oracle database is comprised of one or more operating system files called datafiles. A tablespace's datafiles physically store the associated database data on disk.

Databases and datafiles

A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. A more complicated database might have three tablespaces, each comprised of two datafiles (for a total of six datafiles).

3. Usuarios

¿Qué usuario hemos utilizado con sqlplus hasta ahora?

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

SQL> SHOW PARAMETER os_authent_prefix

NAME TYPE VALUE -------------------------------–— ------–— -------------------------–— os_authent_prefix string ops$ SQL>

– UNIX CREATE USER ops$tim_hall IDENTIFIED EXTERNALLY; GRANT CREATE SESSION TO ops$tim_hall;

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

Cada usuario puede tener unos permisos distintos
Ya hemos visto dos permisos
- En qué tablespaces se puede escribir
- Cuántos datos se pueden escribir en esos tablespaces
- Si una cuenta está bloqueada
Pero hay más permisos
- Veremos los privilegios de Oracle

4.1. Privilegios de Oracle

Privilegio	Objeto sobre el que se aplica
`Create, alter, drop`	`Table, sequence, view, user, synonym, session, procedure`
`select, update, delete, insert`	Sobre campos de tablas y filas

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

grant update(column-name) on table-name to user-name

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?

create user conpermisos identified by conpermisos; grant resource,connect to conpermisos; alter user conpermisos quota 100k on users;

connect conpermisos/conpermisos

create table datos(texto varchar2(255),numero integer); insert into datos(texto) values('En un lugar de la Mancha de cuyo nombre no quiero acordarme'); insert into datos(texto) values('En un lugar de la Mancha de cuyo nombre no quiero acordarme'); insert into datos(texto) values('En un lugar de la Mancha de cuyo nombre no quiero acordarme'); insert into datos(texto) values('En un lugar de la Mancha de cuyo nombre no quiero acordarme'); insert into datos(texto) values('En un lugar de la Mancha de cuyo nombre no quiero acordarme'); commit;

ORA-01536: space quota exceeded for tablespace 'USERS'

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

Asignar todos los privilegios a un usuario es trabajoso, pero factible
¿Qué ocurre si tenemos que manejar a muchos usuario?
Los roles permiten dar nombre a un grupo de privilegios
- Se pueden asignar privilegios a un rol
- Y después asignar ese rol a varios usuarios

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?

drop role ELROL;

create role ELROL; grant resource, connect to ELROL; grant select on LIMITADO.DATOS to ELROL;

drop user usuarioantes; create user usuarioantes identified by u; grant ELROL to usuarioantes;

revoke select on LIMITADO.DATOS from ELROL;

drop user usuariodespues; create user usuariodespues identified by u; grant ELROL to usuariodespues;

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. Consulta recursivas

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;

6. Perfiles

Un profile es un conjunto de limitaciones sobre el sistema Oracle
No limita acceso a datos, sino al propio SGBD y sistema operativo

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

alter system set resource_limit = true scope=both; drop profile limitado cascade; create profile limitado limit idle_time 1 connect_time 2; alter user limitado profile limitado;

ERROR at line 1: ORA-02396: exceeded maximum idle time, please connect again

ERROR at line 1: ORA-02399: exceeded maximum connect time, you are being logged off

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

Create or Replace Function tres_caracteres_o_menos (in_username in varchar2, in_new_password in varchar2, in_old_password in varchar2) return boolean as pwd_okay boolean; begin – * – you can write your own password validation here … – * if LENgth(in_new_password) > 3 then raise_application_error(-20001, 'LA CONTRASEÑA ES MUY LARGA'); end if; return true; end; /

CREATE PROFILE EJEMPLO LIMIT CONNECT_TIME 1 IDLE_TIME 1 PASSWORD_VERIFY_FUNCTION tres_caracteres_o_menos;

7. Referencias

Formatos:
- Transparencias
- PDF
- Página web
- EPUB
Creado con:
Alojado en Github

Usuarios, privilegios y roles de Oracle

Índice

1. Introducción

2. Tablespaces

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

2.2. ¿Por qué tantas normas?

2.3. Tablespaces por defecto

2.4. Crear un tablespace

2.5. ¿Por qué es tan complicado?

2.6. Ejercicio: Llena un tablespace

2.7. Ejercicio: tablespace por defecto

2.8. Ejercicio: más tablespaces

2.9. Conceptos de almacenamiento

3. Usuarios

3.1. Creación de usuarios

3.2. Modificación de usuario

4. Privilegios

4.1. Privilegios de Oracle

4.2. Sintaxis de Grant

4.3. Ejercicio

4.4. Ejercicio

4.5. Quitar privilegios

4.6. Privilegios de sistema

5. Roles

5.1. Sintaxis de roles

5.2. Ejercicio

5.3. Ejercicio

5.4. ¿Qué privilegios tengo?

5.5. Consulta recursivas

6. Perfiles

6.1. Creación de perfiles

6.2. Asignación de perfil a un usuario

6.3. Ejercicio

6.4. Ejercicio

6.5. Ejercicio

7. Referencias

4.2. Sintaxis de `Grant`