22 September 2010

Grant/Revoke object privileges

begin
  for cur in (select do.owner || '.' || do.object_name obj
                from dba_objects do
               where do.owner = 'user1'
                 and do.object_type = 'TABLE') loop
    execute immediate 'grant select on ' || cur.obj || ' to user2';
    --execute immediate 'revoke select on '||cur.obj ||' from user2';
    dbms_output.put_line(cur.obj);
  end loop;
end;

No comments:

Post a Comment