Uprawnienia w bazach danych to soczysty kawałek chleba. Odpowiednie nadanie uprawnień to klucz do bezpieczeństwa danych. Nie należy uprawnień nadawać hojną ręką, bez należytego uzasadnienia.
Są jednak tacy użytkownicy w systemach lub sytuacje, gdzie te uprawnienia możemy i chcemy nadawać szczodrze, praktycznie bez większej weryfikacji. Może to być w przypadku użytkownika/roli technicznej, gdzie użytkownik ten powinien mieć dostęp do wszystkich tabel/obiektów w kilku schematach ale nie powinien mieć uprawnień dba. Możemy też chcieć nadać uprawnienia do wszystkich tabel w systemach testowych czy developerskich. W takich przypadkach przydałoby nam się jakieś sprytne narzędzie, które zrobiłoby to za nas.
Jak nadać uprawnienia do tabel użytkownikowi?
Nadajemy uprawnienia do tabeli takie jak SELECT, INSERT, DELETE, UPDATE lub w skrócie ALL używając polecenia GRANT .
GRANT SELECT, UPDATE, DELETE, INSERT ON <table_name> to <user_name>
GRANT ALL ON <table_name> to <user_name>Usuwamy za pomocą polecenia REVOKE.
REVOKE SELECT, UPDATE, DELETE, INSERT ON <table_name> FROM <user_name>
REVOKE ALL ON <table_name> FROM <user_name>Tylko że te uprawnienia nadajemy tylko dla pojedynczej tabeli. Nie ma niestety czegoś w stylu GRANT ALL ON ALL TABLES :D . Ale. Od czego mamy tabele słownikowe i EXECUTE IMMEDIATE! EXECUTE IMMEDIATE jest bardzo niewydajne i z reguły nie zalecam używania ale do takich jednorazowych akcji się nada.
---------------------------------------------------------------------------------------------------------------------------------
-- grant to all tables
---------------------------------------------------------------------------------------------------------------------------------
set serveroutput on
DECLARE
vUserName ALL_TABLES.OWNER%TYPE := 'PERF_IDX';
vOwnerName ALL_TABLES.OWNER%TYPE := 'HR';
BEGIN
FOR z IN (
SELECT owner, table_name
FROM all_tables
WHERE owner = vOwnerName
)
LOOP
EXECUTE IMMEDIATE
'GRANT SELECT, UPDATE, DELETE, INSERT ON ' || z.owner||'.' || z.table_name|| ' to '|| vUserName ;
END LOOP;
END;
/
---------------------------------------------------------------------------------------------------------------------------------
-- revoke from all tables
---------------------------------------------------------------------------------------------------------------------------------
set serveroutput on
DECLARE
vUserName ALL_TABLES.OWNER%TYPE := 'PERF_IDX';
vOwnerName ALL_TABLES.OWNER%TYPE := 'HR';
vSql varchar2(500);
BEGIN
FOR z IN (
SELECT owner, table_name
FROM all_tables
WHERE owner = vOwnerName
)
LOOP
vSql := 'REVOKE SELECT, UPDATE, DELETE, INSERT ON ' || z.owner||'.' || z.table_name|| ' from '|| vUserName ;
dbms_output.put_line(vsql);
EXECUTE IMMEDIATE
vsql;
END LOOP;
END;
/
A co jeśli byśmy chcieli by automatycznie po utworzeniu tabeli - były nadawane użytkownikowi uprawnienia do niej?
Możemy to zrobić wykorzystując triger DDL. Jest tylko jeden haczyk - ponieważ nadawanie uprawnień należy również do poleceń DDL - nie możemy wykonać poleceń GRANT czy REVOKE bezpośrednio z trigera. Ale możemy utworzyć joba z poleceniem nadania uprawnień. I voila, recepta na automatyczne nadawanie uprawnień gotowa.
Procedura do nadawania uprawnień. Procedura będzie uruchomiona z joba.
create or replace procedure grant_all_on_table(pvTableName varchar2, pvUserName varchar2 , pvOwner varchar2) is
begin
EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, DELETE, INSERT ON ' ||pvOwner||'.'|| pvTableName || ' TO ' || pvUserName ;
end;
Triger DDL - AFTER CREATE ON SCHEMA. Żeby ograniczyć wykonywanie tylko do tabel wykorzystamy atrybuty:
sys.dictionary_obj_type = typ obiektu, nas interesują tylko TABELE
sys.dictionary_obj_name - nazwa obiektu
sys.dictionary_obj_owner - właściciel obiektu
Do uruchomienia joba wykorzystam proccedurę dbms_job.submit.
create or replace trigger tab_priv_grants after create on schema
declare
iJobId integer;
vsql varchar2(500);
begin
IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' then
vsql := 'BEGIN grant_all_on_table( pvTableName => '''|| sys.dictionary_obj_name || ''' ,
pvUserName => ''PERF_IDX'' ,
pvOwner => '''||sys.dictionary_obj_owner ||''') ;
'|| ' END;';
dbms_output.put_line(vsql);
dbms_job.submit( iJobId,
vsql,
sysdate +3/86400 );
end if ;
end;
Test przeprowadziłam na schemacie HR oraz nowo utworzonym użytkowniku PERF_TEST.
-- hr user
create table test_grants (a number);
--perf_test user
select * from hr.test_grants;
ORA-00942: tabela lub perspektywa nie istnieje
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Error at Line: 1 Column: 18
--- after 3 seconds
no rows selected
Działa :D
--------------------------------------------------------------------------------------------------------------------------
A na koniec kod z wieloma zmiennymi, którymi można sobie sterować, jakie uprawnienia nadać. Czy nadać czy odebrać. Jeden z tych kodów, które bardzo fajnie się pisało, a które prawdopodobnie nigdy nie będą użyte. Łatwiej jest puścić zwykły skrypt z ręki niż bawić się ustawianie tylu zmiennych :D Ale co tam, fajnie się pisało :D
set serveroutput on
DECLARE
vUserName ALL_TABLES.OWNER%TYPE := 'PERF_IDX';
vOwnerName ALL_TABLES.OWNER%TYPE := 'HR';
-------------------------------------------
bGrants_Revokes varchar2(1) := 'G'; --G - grants /R - revokes
-------------------------------------------
bSel boolean := true;
bUpd boolean := true;
bDel boolean := true;
bIns boolean := true;
-------------------------------------------
bExecute boolean := true;
-------------------------------------------
vSql varchar2(200);
vSqlGrants varchar2(200);
eNoGrants exception;
BEGIN
if (bSel or bUpd or bDel or bIns) = false
then
raise eNoGrants;
end if;
vSqlGrants := case bGrants_Revokes when 'G' then 'GRANT ' else 'REVOKE ' end||
case when bSel = true then 'SELECT, ' end ||
case when bUpd = true then 'UPDATE, ' end ||
case when bDel = true then 'DELETE, ' end ||
case when bIns = true then 'INSERT, ' end ;
-- cut last ,
vSqlGrants := substr(vSqlGrants, 1, length(trim(vSqlGrants)) -1);
FOR z IN (
SELECT owner, table_name
FROM all_tables
WHERE owner = vOwnerName
)
LOOP
vSql := vSqlGrants || ' ON '||z.owner||'.'||z.table_name||
case bGrants_Revokes when 'G' then ' TO ' else ' FROM ' end|| vUserName;
dbms_output.put_line (vSql);
if bExecute then
EXECUTE IMMEDIATE
vSql;
end if;
END LOOP;
exception when eNoGrants then
dbms_output.put_line ('No grants was specified');
END;
/
Komentarze
Prześlij komentarz