M2 - Bases de dades / Continguts RA2: Usuaris i privilegis

Crear Usuaris

Una de les més bàsiques tasques d'un administrador de base de dades és identificar els usuaris. Cada usuari que connecta en la base de dades ha de tenir un compte.

Per crear un usuari utilitzem la sentència CREATE USER. Quan es crea un compte com a mínim cal assignar un nom únic (username) i una contrasenya per poder autenticar-se.

A l'hora de crea un compte d'usuaris cal tenir en compte:

• El nom d'usuari no ha de superar 30 caràcters, no ha de tenir caràcters especials i ha d'iniciar amb una lletra.

• Un mètode d'autenticació. El mes comú és una clau o password, però Oracle 10g i posteriors suporten altres mètodes (com biomètric, certificat i autenticació per mitjà de token).

• Un Tablespace default, el qual és on l'usuari va a poder crear els seus objectes per defecte, no obstant això, això no significa que pugui crear objectes, o que tingui una quota d'espai. Aquests permisos s'assignen de forma separada, excepte si utilitza el privilegi RESOURCE el que assigna una quota unlimited, fins i tot en el Tablespace SYSTEM! No obstant això si això ocorre, un. pot posteriorment moure els objectes creats en el SYSTEM a un altre Tablespace.

• Un Tablespace temporal, on l'usuari crea els seus objectes temporals i fa els sort o ordenaments.

• Un perfil o profile d'usuari, que són les restriccions que pot tenir el seu compte (opcional).

Per exemple, connectat com l'usuari SYS, pots crear un usuari i la seva clau així:

SQL> CREATE USER ahernandez IDENTIFIED BY ahz
DEFAULT TABLESPACE users;

Si no s'indica un Tablespace per defecte, l'usuari pren el que està definit en la BD (generalment el SYSTEM). Per modificar el Tablespace default d'un usuari es fa de la següent manera:

SQL> ALTER USER jperez DEFAULT TABLESPACE datos;

També podem assignar als usuaris un Tablespace temporal on s'emmagatzemen operacions d'ordenament. Aquestes inclouen les clàusules ORDER BY, GROUP BY, SELECT DISTINCT, MERGE JOIN, o CREATE INDEX (també és utilitzat quan es creen Taules temporals).

SQL> CREATE USER jperez IDENTIFIED BY jpz
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

Addicionalment, a cada usuari es pot assignar a un profile o perfil, que té dos propòsits principalment:

• Limita l'ús de recursos, la qual cosa és recomanable, per exemple en ambients de Desenvolupament.

• Garanteix i reforça regles de Seguretat a nivell de comptes.

Exemples, quan es crea l'usuari o assignar un perfil existent:

SQL> CREATE USER jperez IDENTIFIED BY jpz
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE resource_profile;

SQL> ALTER USER jperez
PROFILE perfil_desa;

Modificar comptes d'Usuari

Per modificar un usuari creat, per exemple canviar la seva clau, tenim la sintaxis:

SQL> ALTER USER NOMBRE_USUARIO
IDENTIFIED BY CLAVE_ACCESO
[DEFAULT TABLESPACE ESPACIO_TABLA]
[TEMPORARY TABLESPACE ESPACIO_TABLA]
[QUOTA {ENTERO {K | M } | UNLIMITED } ON ESPACIO_TABLA
[PROFILE PERFIL];

Privilegis de Sistema i d'Objectes

En Oracle existeixen dos tipus de privilegis d'usuari.

• System:

- permet a l'usuari fer certes tasques sobre la BD, com per exemple crear un Tablespace.

- atorgats per l'administrador o per algú que hagi rebut el permís per administrar aquest tipus de privilegi.

Exemples:

- create session: per connectar-se a la base de dades

- create table: per poder crear taules

- create sequence: per poder crear seqüències

- create view: per poder crear vistes

- create trigger: per poder crear disparadors dins del propi esquema

- create procedure: crear poder crear procediments i funcions

- execute any procedure: permet executar qualsevol procediment en qualsevol esquema

- create user: permet crear usuaris i especificar claus

- create role: permet crear rols

- drop user: permet eliminar usuaris

• Oracle 11g té 208 privilegis de sistema els quals poden ser vists consultant la vista: SYSTEM_PRIVILEGE_MAP

• Els privilegis de sistema: SYSDBA i SYSOPER. permeten que altres usuaris tinguin privilegis d'administradors de base de dades.

• Atorgar diversos privilegis alhora:

SQL> GRANT CREATE USER, ALTER USER, DROP USER TO ahernandez;

• Object:

- permet a l'usuari realitzar certes accions en objectes de la BD, com una Taula, Vista, un Procedure o Funció, etc.

- per defecte un usuari només pot accedir als seus propis objectes (USER_OBJECTS).

- atorgats pel propietari de l'objecte, l'administrador o algú que hagi rebut aquest privilegi explícitament (amb GRANT OPTION).

Per exemple, per atorgar permisos a una taula Vendes per a un usuari particular:

SQL> GRANT SELECT,INSERT,UPDATE ON analista.venta TO jperez;

Addicionalment:

- podem restringir els DML a una columna de la taula.

- podem donar el priveligi de poder donar el privilegi (amb WITH GRANT OPTION).

Exemple:

SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON venta TO mgarcia WITH GRANT OPTION;

Atorgant privilegis

A un usuari podem atorgar-li una sèrie de privilegis. Un privilegi permet a un usuari accedir a certs objectes o realitzar certes accions:

• Privilegis sobre Objectes ( Object privileges ). Permisos sobre vistes, taules, seqüències, procediments, paquets.

• Privilegis del Sistema ( System privileges ). Permisos sobre "nivells de la base de dades" com poden ser connexió a la base de dades, creació d'usuaris, limitar comptes.

• Privilegis sobre Rols ( Role privileges ). Molts permisos són atorgats mitjançant rols agrupant un conjunt de privilegis. Per atorgar privilegis utilitzem la sentencia GRANT, per llevar un privilegi o permís a un usuari utilitzem la sentència REVOKE

EXEMPLES

Privilegi sobre una taula:

SQL > GRANT ALL ON taula_alumnes TO david

Sent taula_alumnes una taula de la nostra base de dades i david un usuari d'aquesta, hem assignat mitjançant GRANT ALL,tots els permisos a l'usuari david sobre aquesta taula.

GRANT ALL = permisos SELECT, INSERT, UPDATE, DELETE

Si volem assignar només un d'aquests permisos utilitzem la mateixa sentència però amb el permís que vulguem atorgar.

SQL > GRANT SELECT ON taula_alumnes TO david

SQL > GRANT SELECT,INSERT ON taula_alumnes TO david

Privilegi sobre una vista:

Per al cas de les vistes podem a un usuari atorgar permisos SELECT, INSERT, UPDATE, DELETE, DEBUG, REFERENCES. Sent vesteixi_alumnes una vista de la nostra base de dades i david un usuari d'aquesta: Atorguem a l'usuari david tots els permisos sobre la vista vista_alumnes.

SQL > GRANT ALL ON vista_alumnes TO david

Atorguem a l'usuari david alguns permisos sobre la vista_alumnes

SQL > GRANT SELECT ON vista_alumnes TO david

SQL > GRANT SELECT,INSERT ON vista_alumnes TO david

Privilegi sobre una seqüència:

Amb les seqüències passa el mateix que amb els anteriors objectes vists, per atorgar permisos s'utilitza GRANT. Els permisos que podem atorgar a una seqüència és SELECT o ALTER.

Privilegi sobre un paquet,funció o procediment:

Els permisos que podem atorgar a les funcions, paquets o procediments emmagatzemats en la nostra base de dades són els següents: EXECUTE, DEBUG.

LLevant privilegis

Si volem llevar un privilegi a un d'aquests objectes farem el mateix que amb GRANT però utilitzant la sentència REVOKE.

Rols

• Conjunt de privilegis que es poden atorgar a un usuari o a un altre Rol.
• Simplifiquen el treball dels DBA en l'assignació de privilegis.
• Per defecte, quan es crea un usuari se li assigna el rol CONNECT, que permet que l'usuari pugui connectar-se a la BD i crear els seus propis objectes en el seu propi esquema. D'una altra manera, hem d'assignar-los en forma manual.

Exemple:

Creació del rol:

SQL> CREATE ROLE appl_dba;   

Opcionalment, es pot assignar una clau al Rol:

SQL> SET ROLE appl_dba IDENTIFIED BY app_pwd;

Per assignar aquest Rol a un usuari:

SQL> GRANT appl_dba TO jperez;

També es poden assignar als rols privilegis sobre Objectes, per exemple en una Taula de Factures on només volem que es puguin fer Consultes i insercions:

SQL> CREATE ROLE consulta;

SQL> GRANT SELECT,INSERT on analista.factura TO consulta;

I finalment assignem aquest rol amb aquest "perfil" a diferents usuaris finals:

SQL> GRANT consulta TO ahernandez;

Nota: Existeixen alguns rols predefinits, tals com:

CONNECT, RESOURCE, DBA, SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, EXP_FULL_DATABASE, IMP_FULL_DATABASE, etc.

• DBA: Té la majoria dels privilegis, no és recomanable assignar-ho a usuaris que no són administradors.

• SELECT_CATALOG_ROLE: No té privilegis de sistema, però té prop de 1600 privilegis d'objecte.

Per consultar els rols definits i els privilegis atorgats a través d'ells, utilitzi les vistes:

SQL> select * from DBA_ROLES;

SQL> select * from DBA_ROLE_PRIVS order by GRANTEE;

Assignar quotes a Usuaris

Per defecte cap usuari té assignada quota en els Tablespaces i es tenen tres opcions per poder proveir a un usuari d'una quota:

• Sense límit, que permet a l'usuari usar tot l'espai disponible d'un Tablespace.

• Per mitjà d'un valor, que pot ser en kilobytes o megabytes que l'usuari pot usar. Aquest valor pot ser major o menor que la grandària del Tablespace assignat a ell.

• Per mitjà del privilegi UNLIMITED TABLESPACE, es té prioritat sobre qualsevol quota donada en un Tablespace pel que tenen disponibilitat de tot l'espai incloent en SYSTEM i SYSAUX.

No es recomana donar quotes als usuaris en els Tablespaces SYSTEM i SYSAUX, doncs normalment només els usuaris SYS i SYSTEM poden crear objectes en aquests. Tampoc donar quotes en els Tablespaces Temporal o del tipus Undo.

Esborrat d'un usuari

Per esborrar un usuari utilitzem la sentència DROP USER, podem opcionalment incloure l'opció CASCADE, s'utilitza per esborrar recursivament els objectes de l'usuari que es pretén esborrar.

SQL > DROP USER david CASCADE