luni, 16 noiembrie 2015

Grant select on all tables of a schema to another user

Just replace the "user1" and "user2" names. User2 is the grantee and user1 the owner.

BEGIN
FOR c IN (SELECT owner, table_name FROM all_tables WHERE owner='user1') LOOP
      EXECUTE IMMEDIATE 'grant select on '||c.owner||'.'||c.table_name||' to user2';
   END LOOP;
END;


and for all privileges: 

BEGIN
FOR c IN (SELECT owner, table_name FROM all_tables WHERE owner='user1') LOOP
      EXECUTE IMMEDIATE 'grant all privileges on '||c.owner||'.'||c.table_name||' to user2';
   END LOOP;
END;

Niciun comentariu:

Trimiteți un comentariu