Pràctica ASI-C6-ASGBD: Seguretat a Oracle II

Objectius

En aquesta pràctica executarem el script admin_seguretat2.sql, que ilustra els conceptes vistos anteriorment, i a més a més la creació de rols. Creem dos rols, ACCES1 i ACCES2, i el rol ACCESTOTAL que és la suma dels dos rols anteriors.

Teoria

• UD3_ASI-C6-ASGBD._Seguretat_a_Oracle

Desenvolupament

Rols

En aquesta pràctica executaràs el script admin_seguretat2.sql, però abans d'executar-lo l'analitzaràs per determinar el seu funcionament. El script el modificaràs amb el sufix de l'alumne.

Un cop funcioni correctament i entenguis tot el procediment, implementaràs regles de seguretat més complexes mitjançant rols.

Permisos sobre vistes en comptes de taules

El problema que plantegem a continuació té molt sentit:

Tenim la taula TENDA i la taula EMPLEAT_TENDA. Cada tenda té un encarregat, i una de les seves missions és actualitzar el camp facturació. Si creem els usuaris U_TENDA1 (Maria) i U_TENDA2 (Clara) amb permisos d'actualitzar facturació sobre la taula TENDA, ens trobem que tant l'un com l'altre poden actualitzar la facturació de les dues tendes, i això és un forat de seguretat gran. Per solucionar-ho (i és pràctica habitual), creem dues vistes, una per cada tenda, i que només visualitzi la facturació. Donem permisos als usuaris sobre la vista corresponent, i així ens assegurem que els encarregats només poden modificar la facturació de la seva tenda. Per tant, donem permisos no sobre les taules, sino sobre les vistes que hem creat sobre les taules. L'exercici complet és el següent:

REM TENDA-EMPLEAT_TENDA és una relació 1:M
REM els encarregats de les tendes 1 i 2 són els empleats 1 i 5

CONNECT ASI2AXX/ASI@BBDD

DROP TABLE EMPLEAT_TENDA;
DROP TABLE TENDA;

CREATE TABLE TENDA(
id_tenda NUMBER(2) PRIMARY KEY,
direccio VARCHAR2(15),
facturacio NUMBER(5)
);

CREATE TABLE EMPLEAT_TENDA (
id_empleat NUMBER(2) PRIMARY KEY,
nom VARCHAR2(10),
categoria VARCHAR2(15),
id_tenda NUMBER(2) REFERENCES TENDA
);


INSERT INTO TENDA VALUES(1,'C/Pau 23',1250);
INSERT INTO TENDA VALUES(2,'C/Llibertat 32',2542);

INSERT INTO EMPLEAT_TENDA VALUES(1,'Maria','encarregat',1);
INSERT INTO EMPLEAT_TENDA VALUES(2,'Pau','caixer',1);
INSERT INTO EMPLEAT_TENDA VALUES(3,'Robert','reposador',1);
INSERT INTO EMPLEAT_TENDA VALUES(4,'Pere','fruiteria',1);
INSERT INTO EMPLEAT_TENDA VALUES(5,'Clara','encarregat',2);
INSERT INTO EMPLEAT_TENDA VALUES(6,'Esther','caixer',2);
INSERT INTO EMPLEAT_TENDA VALUES(7,'Maurici','reposador',2);
INSERT INTO EMPLEAT_TENDA VALUES(8,'Marta','xarcuteria',2);

CREATE VIEW V_TENDA1 AS SELECT facturacio FROM TENDA WHERE id_tenda=1;
CREATE VIEW V_TENDA2 AS SELECT facturacio FROM TENDA WHERE id_tenda=2;

drop user "U_TENDA1" CASCADE;
drop user "U_TENDA2" CASCADE;

create user "U_TENDA1" identified by "TENDA1"
default tablespace users
temporary tablespace temp
quota 1M on users
profile default;

create user "U_TENDA2" identified by "TENDA2"
default tablespace users
temporary tablespace temp
quota 1M on users
profile default;

grant "CONNECT" to "U_TENDA1";
grant "CONNECT" to "U_TENDA2";

REM dono permisos sobre les vistes, no sobre la taula TENDA
GRANT UPDATE(facturacio) ON ASI2AXX.V_TENDA1 TO U_TENDA1;
GRANT UPDATE(facturacio) ON ASI2AXX.V_TENDA2 TO U_TENDA2;

CONNECT U_TENDA1/TENDA1@BBDD

REM l'usuari U_TENDA1 pot modificar la facturació de la seva botiga, però no de l'altra. La informació ''ORA-00942: table or view does not exist'' despista. La vista si que existeix, però no hi ha permisos.

UPDATE ASI2AXX.V_TENDA1 SET facturacio=2200;
UPDATE ASI2AXX.V_TENDA2 SET facturacio=2200;

Perfils

Provar de crear un perfil propi (limitant el número de sessions i el temps de connexió). Comprova com els recursos es limiten. Al final, elimina el perfil.

CREATE PROFILE limitar_sessions LIMIT SESSIONS_PER_USER 2 CONNECT_TIME 2;
ALTER USER "PEREXX" PROFILE limitar_sessions;

Entrega

Entregaràs els script (i el logs) resultant al Moodle.

Recursos

script admin_seguretat2.sql:

REM ROLS: el rol serveix per agrupar un conjunt de privilegis. Els privilegis d'un rol poden ser de sistema i a nivell d'objecte
REM INFORMACIÓ SOBRE ROLS EN EL DICCIONAR DE DADES:
REM USER_TAB_PRIVS: concessions sobre objectes que són propietat de l'usuari, concedits o rebuts per aquest
REM ROLE_SYS_PRIVS: privilegis de sistema assignats a rols
REM ROLE_TAB_PRIVS: privilegis sobre les taules aplicats a rols
REM ROLE_ROLE_PRIVS: rols assignats a d'altres rols
REM SESSION_ROLES: rols actius per l'usuari
REM USER_ROLE_PRIVS: rols assignats a l'usuari
REM DBA_SYS_PRIVS: privilegis del sistema assignats als usuaris o rols
REM DBA_ROLE_PRIVS: privilegis assignats a tots els usuaris i rols
REM DBA_ROLES: tots els rols

SPOOL /home/joan/log.txt
SET echo on
prompt ens connectem amb l'usuari ASI2AXX...
pause
connect ASI2AXX/ASI@bbdd

prompt i creem la taula JTEMP1 i JTEMP2 amb tres files per a fer proves
pause
drop table jtemp1;
create table jtemp1(camp1 varchar2(10), camp2 varchar2(10));
insert into jtemp1 values('fila1col1','fila1col2');
insert into jtemp1 values('fila2col1','fila2col2');
insert into jtemp1 values('fila3col1','fila3col2');
select * from jtemp1;

drop table jtemp2;
create table jtemp2(camp1 varchar2(10));
insert into jtemp2 values('fila1col1');
insert into jtemp2 values('fila2col1');
insert into jtemp2 values('fila3col1');
select * from jtemp2;

REM l'opcio CASCADE es per esborrar les taules de 'pere' si es que n'hi han
drop user "PEREXX" CASCADE;

prompt creem l'usuari PERE. Es important donar una quota en l'espai de taules d'usuari, doncs si la quota és 0, encara que hi hagi permisos no es podran crear taules.

create user "PEREXX" identified by "PERE"
default tablespace users
temporary tablespace temp
quota 1M on users
profile default;


grant "CONNECT" to "PEREXX";

prompt Creem un ROL anomenat ACCES1. L'usuari que crea el ROL ha de ser administrador o bé tenir el ROL de CREATE ROL.
pause
DROP ROLE ACCESTOTAL;
DROP ROLE ACCES1;
DROP ROLE ACCES2;

CREATE ROLE ACCES1;
 
prompt ara afegim privilegis als rol que acabem de crear
pause
GRANT SELECT, INSERT ON ASI2AXX.JTEMP1 TO ACCES1;
GRANT UPDATE ON ASI2AXX.JTEMP2 TO ACCES1;

prompt comprovem que els privilegis s'han afegit al ROL mirant la vista USER_TAB_PRIVS
pause
select * from USER_TAB_PRIVS;

prompt ara afegim aquest ROL a l'usuari PERE
pause
GRANT ACCES1 TO "PEREXX";

PROMPT i comprovem que el ROL funciona correctament...
pause

connect PEREXX/PERE@bbdd

REM funciona
INSERT INTO ASI2AXX.JTEMP1(camp1) VALUES('fila4col1');
SELECT * FROM ASI2AXX.JTEMP1;
REM no funciona
UPDATE ASI2AXX.JTEMP1 SET CAMP1='fila5' WHERE CAMP1='fila4';

REM no funciona
INSERT INTO ASI2AXX.JTEMP2 VALUES('fila4');
SELECT * FROM ASI2AXX.JTEMP2;
REM funciona
UPDATE ASI2AXX.JTEMP2 SET CAMP1='modificada' WHERE CAMP1='fila3col1';

prompt finalment, podem revocar privilegis del ROL. Ens tornem a connectar com a Administrador...
pause

connect ASI2AXX/ASI@bbdd
REVOKE INSERT ON ASI2AXX.JTEMP1 FROM ACCES1;
connect PEREXX/PERE@bbdd
REM ara no funciona perquè s'ha retirat el privilegi del rol
INSERT INTO ASI2AXX.JTEMP1(camp1) VALUES('fila5col1');


connect ASI2AXX/ASI@bbdd
select * from USER_TAB_PRIVS;

REVOKE ACCES1 FROM "PEREXX";

CREATE ROLE ACCES2;
GRANT DELETE, UPDATE(camp2) ON ASI2AXX.JTEMP1 TO ACCES2;
GRANT ALTER ON ASI2AXX.JTEMP2 TO ACCES2;

CREATE ROLE ACCESTOTAL;
GRANT ACCES1 TO ACCESTOTAL;
GRANT ACCES2 TO ACCESTOTAL;
GRANT ACCESTOTAL TO "PEREXX";

connect PEREXX/PERE@bbdd

REM funciona
SELECT * FROM ASI2AXX.JTEMP1;
UPDATE ASI2AXX.JTEMP1 SET camp2='modificada' where camp2='fila1col2';
DELETE FROM ASI2AXX.JTEMP1 WHERE camp1='fila2col1';
UPDATE ASI2AXX.JTEMP1 SET camp2='modificada' WHERE camp2='fila3col2';
ALTER TABLE ASI2AXX.JTEMP2 ADD (camp2 varchar2(10));

REM no funciona
UPDATE ASI2AXX.JTEMP1 SET camp1='modificada' WHERE camp1='fila1col2';
INSERT INTO ASI2AXX.JTEMP2 VALUES('fila5col1','fila5col2');

spool off

REM amb la comanda HOST podem executar comandes del SO
HOST cat /home/joan/log.txt

Durarda

2 hores

---

creat per Joan Quintana Compte, gener 2010