Grant Select, Insert, Update to a Tablespace

Question

I've got a lot of tables in a tablespace, nearly 100. I have to grant Select, Insert, Update privileges on all those tables to a user. Is it possible? When I write:

GRANT USE OF TABLESPACE MYTABLESPACE TO USERNAME

I get oracle error "invalid or missing privilege"

Answer 1

Use the data dictionary view dba_tables (resp. all_tables, if you cannot access dba_tables):

declare
  l_SQL varchar2(4000);
begin
  for cur in (
    select * from dba_tables where tablespace_name = 'mytablespace')
  loop
    l_sql := 'grant select, insert, update on ' || cur.owner || '.' || cur.table_name || ' to myuser';
    --dbms_output.put_line(l_SQL || ';');
    execute immediate l_SQL;
  end loop;
end;

If you just want to generate a script, comment out the execute immediate and un-comment the dbms_output.