Berechtigungen auf anderes Schema vergeben
Manchmal steht die Aufgabe im Raum, daß man mit einem Oracle User auf die Daten eines anderen Users zugreifen möchte. Bei ein paar Tabellen ist die Rechtevergabe relativ überschaubar. Soll eine Berechtigung auf alle Tabellen bzw. Objekte vergeben werden, hört der Spaß mit einer händischen Rechtevergabe meistens auf. Leider gibt es bei Oracle nämlich kein Statement der Form GRANT SELECT ON SCHEMA. Was bleibt einem übrig? Was man nicht selber machen möchte, muß man skripten.
Nachfolgendes Skript vergibt an den Berechtigungsempfänger die gebräuchlichsten Rechte, damit man mit den Daten in einem anderen Schema arbeiten kann.
-- Vollzugriff (SELECT, INSERT, UPDATE, DELETE) auf alle
-- Tabellen und Views eines Schemas vergeben
DECLARE
V_OWNER VARCHAR2(30) := 'USER_THAT_OWNS_THE_SCHEMA';
V_GRANTEE VARCHAR2(30) := 'USER_THAT_WILL_GET_THE_GRANTS';
BEGIN
FOR Rec IN (SELECT object_name,
object_type FROM all_objects WHERE owner=V_OWNER AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE','SEQUENCE')) LOOP
IF Rec.object_type IN ('TABLE','VIEW') THEN
EXECUTE IMMEDIATE 'GRANT SELECT,
UPDATE,
INSERT,
DELETE ON ' || V_OWNER || '.' || Rec.object_name || ' TO ' || V_GRANTEE;
ELSIF Rec.object_type IN ('SEQUENCE') THEN
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || V_OWNER || '.' || Rec.object_name || ' TO ' || V_GRANTEE;
ELSIF Rec.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || V_OWNER || '.' || Rec.object_name || ' TO ' || V_GRANTEE;
END IF;
END LOOP;
END;
/
-- Lesenden Zugriff auf alle Tabellen eines Schemas vergeben
DECLARE
lOwner VARCHAR2(30) := 'USER_THAT_OWNS_THE_SCHEMA';
lGrantee VARCHAR2(30) := 'USER_THAT_WILL_GET_THE_GRANTS';
BEGIN
FOR Rec IN (SELECT owner, table_name FROM all_tables WHERE owner = lOwner)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || Rec.owner || '.' || Rec.table_name || ' TO ' || lGrantee;
END LOOP;
END;
/
-- Über einen Scheduler Job automatisiert (hier wöchentlich)
-- lesenden Zugriff auf alle Tabellen eines Schemas vergeben
-- (falls bspw. durch Servicepakete neue Tabellen erstellt werden).
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,start_date => TO_TIMESTAMP_TZ('2021/07/25 20:00:00.000000 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=WEEKLY;INTERVAL=1'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'DECLARE
lOwner VARCHAR2(30) := ''USER_THAT_OWNS_THE_SCHEMA'';
lGrantee VARCHAR2(30) := ''USER_THAT_WILL_GET_THE_GRANTS'';
BEGIN
FOR Rec IN (SELECT owner, table_name FROM all_tables WHERE owner = lOwner)
LOOP
EXECUTE IMMEDIATE ''GRANT SELECT ON '' || Rec.owner || ''.'' || Rec.table_name || '' TO '' || lGrantee;
END LOOP;
END;'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'AUTO_DROP'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'RESTART_ON_RECOVERY'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'RESTART_ON_FAILURE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES'
,attribute => 'STORE_OUTPUT'
,value => TRUE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'USER_THAT_OWNS_THE_SCHEMA.GRANT_SELECT_TABLES');
END;
/