Grant selection, insert, update to tablespace

Question

I have many tables in the table space, almost 100. I must provide the user with Select, Insert, Update privileges for all these tables. Is it possible? When I write:

GRANT USE OF TABLESPACE MYTABLESPACE TO USERNAME 

I get oracle error "invalid or missing privileges"

Answer 1

Use the dba_tables data dictionary dictionary (respectively 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 immediate execution and do not comment on dbms_output.

Answer 2

USE OF TABLESPACE not a documented option, where did you find this?

You can do this to allow the user to create objects in the table space:

 alter user username quota [amount] on mytablespace; 

To provide a selection, insert, update, and delete objects, you must run a separate grant command for each table:

 grant select, insert, update, delete on mytable1 to username; ....