授予对表空间的选择、插入、更新权限

发布于 2024-11-10 16:26:44 字数 169 浏览 5 评论 0原文

我的表空间中有很多表，接近 100 个。我必须向用户授予所有这些表的选择、插入、更新权限。是否可以？当我写入时：

GRANT USE OF TABLESPACE MYTABLESPACE TO USERNAME

我收到 oracle 错误“无效或缺少权限”

原文

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"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

成熟的代价 2024-11-17 16:26:44

USE OF TABLESPACE 没有记录在案的选项，您在哪里找到的？

您可以这样做以允许用户在表空间中创建对象：

alter user username quota [amount] on mytablespace;

要授予对对象的选择、插入、更新和删除权限，您必须为每个表运行单独的 grant 命令：

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

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;
....

回复收藏 0 原文

沐歌 2024-11-17 16:26:44

使用数据字典视图 dba_tables（如果无法访问 dba_tables，则使用 all_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;

如果您只想生成脚本，请注释掉立即执行并取消注释 dbms_output。

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.

回复收藏 0 原文

~没有更多了~