Visit Amazan Store

Sunday, May 15, 2016

grant select on all tables in a schema



GRANT  SELECT on all tables in a schema

Single statement to grant privileges

FOR  x  IN (SELECT * FROM user_tables)
LOOP
 EXECUTE IMMEDIATE  ‘GRANT SELECT ON  ‘ || x.table_name || ‘  TO  << someone >>’ ;
END LOOP;

OR

DECLARE
CURSOR  cur1 IS SELECT TABLE_NAME from user_tables;
cmd varchar2(200);
BEGIN
FOR c in c1 LOOP
cmd := ‘GRANT SELECT ON ‘||c.table_name||<< TO YOURUSERNAME>>’;
execute immediate cmd;
END LOOP;
END;