Pràctica ASI-C6-ASGBD: Seguretat a Oracle I
Objectius
En aquesta pràctica executarem el script admin_seguretat1.sql, que ilustra els conceptes bàsics de seguretat a Oracle, creació d'usuaris, permisos i rols que vam estar veient a la teoria. Es treballa amb dos usuaris. Un que té permisos de DBA (per ex, MARIA), i un altre que té permisos limitats (per ex, PERE).
Teoria
Desenvolupament
Creem l'usuari MARIA (usuari DBA):
SQL> CREATE USER "MARIA" IDENTIFIED BY maria DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; SQL> GRANT CONNECT,DBA TO "MARIA";
Primer de tot fem un SPOOL per a registrar en un fitxer tot allò que va succeïnt en el script
SPOOL c:\log.txt SET echo on
Ens connectem amb l'usuari MARIA, que ja existeix i és DBA (té els permisos suficients per crear usuaris,...), i creem la taula de proves jtemp amb tres files per a fer proves. És important crear els usuaris amb majúscules. En la versió 8 dóna problemes a posteriori si hem creat els usuaris amb majúcules. (Comprovar si això també s'aplica a versions posteriors).
prompt ens connectem amb l'usuari MARIA... pause connect MARIA/BBDD@BBDD
i creem la taula JTEMP amb tres files per a fer proves
drop table jtemp;
create table jtemp(camp1 varchar2(10));
insert into jtemp values('fila1');
insert into jtemp values('fila2');
insert into jtemp values('fila3');
select * from jtemp;
Abans de crear l'usuari PERE l'esborrem, i també esborrem tots els objectes del seu esquema (CASCADE). Creem l'usuari sense cap mena de permís.
REM l'opcio CASCADE es per esborrar les taules de 'PERE' si es que n'hi ha drop user "PERE" CASCADE; prompt creem l'usuari PERE. create user "PERE" identified by "PERE" default tablespace users temporary tablespace temp quota 1M on users profile default;
Comprovem que l'usuari s'ha creat mirant la vista all_users del diccionari. Tanmateix, no ens podem connectar, no tenim permisos. Per donar permisos ens hem de tornar a connectar com a MARIA i donar els permisos mínims. Ara sí que podrem connectar-nos i crear una taula en l'esquema de PERE.
select * from all_users where username='PERE';
connect PERE/PERE@BBDD
prompt ens tornem a connectar amb 'MARIA'
pause
connect MARIA/BBDD@BBDD
prompt donem a l'usuari PERE el rol de CONNECT i RESOURCE, que entre d'altres inclou els privilegis de CREATE SESSION, CREATE TABLE i CREATE VIEW
pause
grant "CONNECT","RESOURCE" to "PERE";
connect PERE/PERE@BBDD
create table ptemp(camp1 varchar2(10));
insert into ptemp values('fila1');
select * from ptemp;
pause
Si PERE vol accedir a la taula jtemp de l'esquema MARIA, encara no té permisos per fer-ho. L'usuari MARIA li haurà de donar privilegis sobre aquest objecte per fer, per exemple, selects i inserts.
prompt volem accedir a la taula MARIA.jtemp prompt evidentment, encara no tenim privilegis per fer-ho prompt select * from MARIA.jtemp; pause select * from MARIA.jtemp; prompt encara que diu que la tabla no existeix, en realitat si que existeix pero no hi ha privilegis pause prompt S'ha de donar el privilegi de SELECT ON TEMP to PERE (ho ha de fer l'usuari MARIA, que es DBA i te permisos per poder-ho fer). prompt per tant tornem a connectar-nos com a MARIA per donar aquests privilegis prompt grant select, insert on MARIA.jtemp to PERE; pause connect MARIA/BBDD@BBDD grant select, insert on MARIA.jtemp to PERE; pause
comprovem que ara sí que tenim privilegis suficients per a select i insert, però no per a update.
prompt tornem a connectar-nos com a PERE
connect PERE/PERE@BBDD
prompt i ara si que podem fer la select que voliem
prompt select * from MARIA.jtemp;
pause
select * from MARIA.jtemp;
prompt i també tenim permisos per fer inserts
insert into MARIA.jtemp values('fila4');
select * from MARIA.jtemp;
pause
prompt pero no tenim permisos per a fer updates
update MARIA.jtemp set camp1='canvifila4' where camp1='fila4';
select * from MARIA.jtemp;
pause
Ara MARIA dóna permís per a update a PERE sobre l'objecte jtemp. Després dóno permisos totals (GRANT ALL)
connect MARIA/BBDD@BBDD grant update on MARIA.jtemp to PERE; pause connect PERE/PERE@BBDD update MARIA.jtemp set camp1='canvifila4' where camp1='fila4'; select * from MARIA.jtemp; pause prompt finalment, concedeixo tots els permisos sobre aquesta taula: promp grant all on MARIA.jtemp to PERE connect MARIA/BBDD@BBDD grant all on MARIA.jtemp to PERE pause prompt i ara puc fer coses mes radicals com prompt alter table MARIA.jtemp modify camp1 varchar2(12); prompt alter table MARIA.jtemp add camp2 varchar2(10); alter table MARIA.jtemp modify camp1 varchar2(12); alter table MARIA.jtemp add camp2 varchar2(10); describe MARIA.jtemp spool off
A realitzar
Estudia bé el contingut del script. Executa'l. Realitza modificacions del script que afectin a privilegis de sistema i privilegis d'objecte. En comptes d'usar l'usuari MARIA per fer proves, utilitza el teu usuari ASI2AXX.
Per tal de què els alumnes no entrin en conflicte entre sí, hauràs de treballar:
- en comptes de MARIA -> ASI2AXX
- en comptes de PERE -> PEREXX
Fica els noms d'usuari en majúscules. Encara que sembli un aspecte sense importància, s'ha vist que és vital per al funcionament de la pràctica.
Entrega
Entregaràs el script (i el log) resultant al Moodle.
Recursos
script admin_seguretat1.sql:
SPOOL c:\log.txt
SET echo on
prompt ens connectem amb l'usuari MARIA...
pause
connect MARIA/BBDD@BBDD
prompt i creem la taula JTEMP amb tres files per a fer proves
pause
drop table jtemp;
create table jtemp(camp1 varchar2(10));
insert into jtemp values('fila1');
insert into jtemp values('fila2');
insert into jtemp values('fila3');
select * from jtemp;
REM l'opcio CASCADE es per esborrar les taules de 'PERE' si es que n'hi han
drop user "PERE" CASCADE;
prompt creem l'usuari PERE.
create user "PERE" identified by "PERE"
default tablespace users
temporary tablespace temp
quota 1M on users
profile default;
select * from all_users where username='PERE';
connect PERE/PERE@BBDD
prompt ens tornem a connectar amb 'MARIA'
pause
connect MARIA/BBDD@BBDD
prompt donem a l'usuari PERE el rol de CONNECT i RESOURCE, que entre d'altres inclou els privilegis de CREATE SESSION, CREATE TABLE i CREATE VIEW
pause
grant "CONNECT","RESOURCE" to "PERE";
connect PERE/PERE@BBDD
create table ptemp(camp1 varchar2(10));
insert into ptemp values('fila1');
select * from ptemp;
pause
prompt volem accedir a la taula MARIA.jtemp
prompt evidentment, encara no tenim privilegis per fer-ho
prompt select * from MARIA.jtemp;
pause
select * from MARIA.jtemp;
prompt encara que diu que la tabla no existeix, en realitat si que existeix pero no hi ha privilegis
pause
prompt S'ha de donar el privilegi de SELECT ON TEMP to PERE (ho ha de fer l'usuari MARIA, que es DBA i te permisos per poder-ho fer).
prompt per tant tornem a connectar-nos com a MARIA per donar aquests privilegis
prompt grant select, insert on MARIA.jtemp to PERE;
pause
connect MARIA/BBDD@BBDD
grant select, insert on MARIA.jtemp to PERE;
pause
prompt tornem a connectar-nos com a PERE
connect PERE/PERE@BBDD
prompt i ara si que podem fer la select que voliem
prompt select * from MARIA.jtemp;
pause
select * from MARIA.jtemp;
prompt i també tenim permisos per fer inserts
insert into MARIA.jtemp values('fila4');
select * from MARIA.jtemp;
pause
prompt pero no tenim permisos per a fer updates
update MARIA.jtemp set camp1='canvifila4' where camp1='fila4';
select * from MARIA.jtemp;
pause
connect MARIA/BBDD@BBDD
grant update on MARIA.jtemp to PERE;
pause
connect PERE/PERE@BBDD
update MARIA.jtemp set camp1='canvifila4' where camp1='fila4';
select * from MARIA.jtemp;
pause
prompt finalment, concedeixo tots els permisos sobre aquesta taula:
promp grant all on MARIA.jtemp to PERE
connect MARIA/BBDD@BBDD
grant all on MARIA.jtemp to PERE
pause
prompt i ara puc fer coses mes radicals com
prompt alter table MARIA.jtemp modify camp1 varchar2(12);
prompt alter table MARIA.jtemp add camp2 varchar2(10);
alter table MARIA.jtemp modify camp1 varchar2(12);
alter table MARIA.jtemp add camp2 varchar2(10);
describe MARIA.jtemp
spool off
Durarda
2 hores
creat per Joan Quintana Compte, gener 2010
