Blog – Page 2 – Mehmet Tahsin Can
Oracle veritabanında bir kullanıcıya başka bir şemada yetki vermek
Oracle veritabanlarında bir kullanıcıya başka bir kullanıcının objeleri üzerinde yetki vermek için, normal şartlarda objelere tek tek yetki vermeniz gerekir. Ancak bu yöntemde yeni eklenen objeler için ayrıca yetki verilmesi gerekmektedir.
Bunun yanında bir kullanıcıya başka bir kullanıcının altında DDL (Data Definition Language) yetkisi vermek için ise tüm diğer şemalarda DDL yetkisi vermek gerekiyor.
Bu işlem de güvenlik ve kontrol açısından zaaflar doğuruyor.
Yazının devamında verilen DDL yetkilerini belirli bir şema ile sınırlamak ve yeni oluşan objelere otomatik DML yetkilerini vermek için yazdığım bazı prosedür ve triggerları paylaşacağım. Bu yapıyı ortak şema kullanması gereken bir yazılım yada raporlama grubu için rahatlıkla kullanbilirsiniz.
Senaryomuzda User1, User2 ve User3 isminde üç kullanıcımız olacak. User1 ve User2 kullanıcılarına User3 kullanıcısı üzerinde obje oluşturma ve okuma yazma gibi yetkileri vereceğiz. Kullanıcıları oluşturmak için username’i değiştirerek aşağıdaki sciprti kullanbilirsiniz.
-- 2 Roles for USER1
-- 1 System Privilege for USER1
Gördüğünüz gibi ilk etapta kullanıcılarımıza bir yetki vermedik. Sysdba hakkına sahip bir kullanıcı ile sisteme bağlanarak aşağıdaki sciptleri çalıştırıyoruz. Bu scriptler ile önce USER1 ve USER2 ye tüm şemalarda create ve drop table yetkisi veriyoruz. Daha sonra da bu yetkiyi sadece kendi şemalarında ve USER3 altında kullanabilecekleri şekilde kısıtlayacak bir trigger yazıyoruz.
grant create any table to user1;
grant create any table to user2;
grant drop any table to user1;
grant drop any table to user2;
CREATE OR REPLACE TRIGGER SYS.DDL_TRIGGER_USER1 BEFORE DDL ON USER1.SCHEMA
-- USER1 için ddl('CREATE','ALTER','DROP','TRUNCATE','RENAME') yapılabilecek şemaları USER1 ve USER3 ile kısıtlar
IF ora_dict_obj_owner NOT IN ('USER1','USER3') AND ora_sysevent IN ('CREATE','ALTER','DROP','TRUNCATE','RENAME') THEN
raise_application_error(-20001 , 'USER2 şeması haricinde Bu islem yapilamaz.');
CREATE OR REPLACE TRIGGER SYS.DDL_TRIGGER_USER2 BEFORE DDL ON USER2.SCHEMA
-- USER2 için ddl('CREATE','ALTER','DROP','TRUNCATE','RENAME') yapılabilecek şemaları USER2 ve USER3 ile kısıtlar
IF ora_dict_obj_owner NOT IN ('USER2','USER3') AND ora_sysevent IN ('CREATE','ALTER','DROP','TRUNCATE','RENAME') THEN
raise_application_error(-20001 , 'USER3 şeması haricinde Bu islem yapilamaz.');
Hakları verip triggerı oluşturduktan sonra aşağıdaki şekilde deneme yapabiliriz. Deneme sonucunda USER1 ile USER3 şeması altında tablo oluşturabilirken, USER2 altında oluşturamadığımızı görüyoruz.
-bash-4.1$ sqlplus user1/test@tahsinc
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 15 16:00:18 2017
SQL> create table user3.deneme ( a varchar2(10));
SQL> create table user2.deneme ( a varchar2(10));
create table user2.deneme ( a varchar2(10))
ORA-20001: USER3 şeması haricinde Bu islem yapilamaz.
USER1 ve USER2 ye User3 altında oluşan ve gelecekte oluşacak tüm tablolara yetki vermek için aşağıda tanımladığımız proesdürü ve trigger’ı oluşturmamız gerekiyor. Aşağıda oluşturduğumuz prosedür ile tablo ismi ve owner bilgisini alarak bu tablolarda USER1 ve USER2 ye yetki veren bir prosedür oluşturduk.
CREATE OR REPLACE PROCEDURE SYS.USER3_GRANT
(p_table_name IN VARCHAR2,p_table_owner IN VARCHAR2) IS
EXECUTE IMMEDIATE 'grant select,insert,update,delete on ' ||p_table_owner||'.'||p_table_name || ' to USER1';
EXECUTE IMMEDIATE 'grant select,insert,update,delete on ' ||p_table_owner||'.'||p_table_name || ' to USER2';
Şimdi de bu prosedürü USER3 Altında bir obje oluşturulduğunda çağıracak triggerları yazacağız. Bizim senaryomuzda USER3 altına tablo oluşturabilen 3 user olduğundan bu üç user için birer trigger oluşturduk.
Not: Burada trigger içerisinde yaptığımız grant işlemini, obje oluştuktan 1 saniye sonra çalışacak şekilde set ettik. Eğer bunu yapmazsak obje tam oluşturulmadan grant vermeye çalışarak hata alabiliyor.
CREATE OR REPLACE TRIGGER SYS.DDL_TRIGGER_USER1_GRANT AFTER CREATE ON USER1.SCHEMA
declare l_jobno PLS_INTEGER;
IF ora_dict_obj_owner IN ('USER3') and ora_dict_obj_type IN ('TABLE') THEN
'BEGIN USER3_GRANT( ''' || ora_dict_obj_name || ''' , ''' || ora_dict_obj_owner || ''' ); END;',
sysdate + interval '1' second );
CREATE OR REPLACE TRIGGER SYS.DDL_TRIGGER_USER2_GRANT AFTER CREATE ON USER2.SCHEMA
CREATE OR REPLACE TRIGGER SYS.DDL_TRIGGER_USER3_GRANT AFTER CREATE ON USER3.SCHEMA
Bunun yanında önceden oluşturulan tablolar için de bir kereliğine aşağıdaki scipri çalıştırıp;
select 'grant select on ' ||owner||'.'||table_name||' to USER1,USER2;' from dba_tables where owner in ('USER3');
Script sonucunda elde ettiğimiz outputu execute ederek geçmişte oluşturulmuş tablolara da yetki vermiş oluruz.
grant select on USER3.DENEME to USER1,USER2;
Bu sayede, select any table yetkisi vermeden veya tablo oluştuğunda manuel yetki vermemize gerek kalmadan, USER3 altındaki tüm objelere USER1 ve USER2 ile erişebileceğiz.
Şimdi testimizi yapalım. USER2 ile USER2 ve USER3 altına birer obje oluşturup sonrasında USER1 ile bu tablolardan select çekmeyi deneyeceğiz.
-bash-4.1$ sqlplus USER2/test@tahsinc
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 15 16:45:10 2017
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 15 16:46:59 2017
SQL> select * from user3.deneme;
SQL> select * from user2.deneme;
select * from user2.deneme
Sonuçta beklediğimiz gibi USER1, USER3 altında oluşan tabloya erişebilirken USER2 altında oluşan tabloya erişemiyor.
Bu prosedür ve trigger’ı ihtiyaçlarınız doğrultusunda modifiye ederek. Tüm şemlara yetki vermeden sadece başka bir şema altındaki objelere yetki verebilirsiniz. Örneğin raporlama grubu için oluşturmanız gereken ortak bir şema bulunuyorsa bu sciptler çok işinize yarayacaktır.
Flashback Table ile Tabloyu Geçmişteki Bir Ana Döndürme
Flashback table özelliği, Oracle’ın undo tablespace sayesinde bir tablonun belirli bir zaman kadar önceki haline erişmemize yardımcı olur. Gidebileceğimiz maksimum süre undo retention değerimiz ve undo tablespace büyüklüğümüz ile alakalıdır. Flashback database özelliği ile karıştırılmamalıdır. Flashback database özelliği flashback loglarını kullanırken, flashback table özelliği undo’yu kullanır.
Bu özelliği kullanmak için tablonun row movement özelliği enable olmalıdır, row movement enable olmadan bir tabloda flashback özelliği denenirse aşağıdaki hata alınır.
“ORA-08189: cannot flashback the table because row movement is not enabled”
Herhangi bir tabloda özelliğin açık olup olmadığı aşağıdaki script ile kontrol edilebilir.
SQL> select owner, table_name, row_movement from all_tables where table_name = 'TAHSINC';
OWNER                          TABLE_NAME                     ROW_MOVE
SYS                            TAHSINC                        ENABLED
Tüm kontroller yapıldıktan sonra, İstenilen bir tarih zamana geri dönmek için aşağıdaki kod kullanılabilir.
SQL> FLASHBACK TABLE table_name TO TIMESTAMP  TO_TIMESTAMP('01-01-2017 01:00:00','DD-MM-YYYY HH24:MI:SS');
System change number (scn) ile geri dönme, bir işlem başarısız olduğunda, işlem yapılmadan önceki hale geri dönmek için çok faydalıdır. mevcut scn’yi aşağıdaki komut ile görebilirsiniz
SCN’ye geri dönme.
SQL> FLASHBACK TABLE table_name TO SCN 123456;
Kayıt ettiğiniz bir scn’nin hangi zaman dilimine ait olduğuna bakmak isterseniz, aşağıdaki komutu kullanabilirsiniz.
SQL> SELECT SCN_TO_TIMESTAMP(123456) AS TIMESTAMP FROM DUAL;
Drop olan bir tabloyu recyle bin den geri döndürmek için
Drop olan bir tabloyu recyle bin den, yeni bir isimle geri döndürmek için
SQL> FLASHBACK TABLE table_name TO BEFORE DROP rename to table_name2;
Not: Drop olan tablo system tablesapce’inde ise recycle bin e gitmez ve bu şekilde geri döndürülemez.