Grant Select, Insert, Update to a Tablespace
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 mi开发者_JS百科ssing privilege"
USE OF TABLESPACE
is not a documented option, where did you find that?
You can do this to allow a user to create objects in a tablespace:
alter user username quota [amount] on mytablespace;
To grant select, insert, update and delete on objects you have to run a separate grant
command for each table:
grant select, insert, update, delete on mytable1 to username;
....
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.
精彩评论