CODEWITHSUNDEEP
sqldatabaseoracle-databaseprivilegessql-grant
user983983
user983983

Reputation: 158

Grant Privileges for Oracle

I want to grant update privileges to a user to all tables within the schema except to update the primary keys. Is there a easy way to do it? Or should I do it separately for each table and define each column.

How does it work? Im sure there is a easy way to do it. I'm reading all documentation but it gives me all generic examples.

Upvotes: 0

Views: 436

Answers (2)

Andrey Hideki Nakano
Andrey Hideki Nakano

Reputation: 41

Connect on user in sequence for grant permission

For except UPDATE remove this

BEGIN
  FOR x IN (SELECT * FROM user_tables)
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, DELETE, INSERT ON ' || x.table_name || ' TO <user>';
  END LOOP;
END;

Upvotes: 0

are
are

Reputation: 2615

declare 
  myquery varchar2(1024);
begin
  for rc in (select * from user_tab_cols u
  where not exists (
    select * from 
       user_constraints c
      ,user_cons_columns cc        
    where c.constraint_type = 'P'
      and c.constraint_name = cc.column_name
      and u.table_name = cc.table_name
      and u.column_name = cc.column_name
  )) loop
    myquery := 'GRANT  update ('||rc.column_name||') on '||rc.table_name||' to myuser';
    --dbms_output.put_line(myquery);
    execute immediate myquery;
  end loop;
end;

p.s. I'm not sure about what "all tables" in description means . In my query it for all tables within current schema, if you need all tables within database change user*** to all***, i.e. user_constraints to all_constraints, etc.

Upvotes: 4

Related Questions