CODEWITHSUNDEEP
sqloracle-database
SebastianH
SebastianH

Reputation: 2182

How to grant access to views programatically in Oracle?

How can I give a user access rights (select, update, ...) to a set of views at once?

Upvotes: 0

Views: 52

Answers (2)

ibre5041
ibre5041

Reputation: 5288

There is also (quite misleadingly named) construct in Oracle called CREATE SCHEMA. By using this you can group several DB objects into one "entity" to be subject of granting.

Upvotes: 1

ntodorov70
ntodorov70

Reputation: 71

You can this script (not tested)

  DECLARE
    targetUser VARCHAR2(200 CHAR);
  BEGIN
    targetUser := 'fooBar';
    FOR Rec IN (SELECT object_name, object_type FROM user_objects WHERE object_type IN ('VIEW')) LOOP
      DBMS_OUTPUT.PUT_LINE('GRANT SELECT, UPDATE, INSERT, DELETE ON '||Rec.object_name||' TO ' || targetUser);
      EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON '||Rec.object_name||' TO ' || targetUser;
    END LOOP;
  END;

Upvotes: 1

Related Questions