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;
/

Suche