Informix，在所有表上授予选择权限

发布于 2024-08-01 19:56:19 字数 150 浏览 2 评论 0原文

使用 Informix，我可以在表上授予选择，例如：

grant select on 'dba'.mytable to someuser as dba;

如何对数据库中的所有表执行此操作？

原文

With Informix I can grant select on a table like;

grant select on 'dba'.mytable to someuser as dba;

How can I perform this on all tables in the database?

分享到QQ

分享到微博

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

发布评论

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

鲜血染红嫁衣 2024-08-08 19:56:19

没有一种简单的方法可以做到这一点（无论是在 SQL 标准还是在 Informix 中），但通常没有必要，因为通常会默认授予该特定权限。但是，我想实际上您也想做更常规的操作，并且想知道有哪些选项。

根据您的目标以及系统的设置方式，您可以通过多种方式实现您的目标。

蛮力 - 直接回答问题

 SELECT 'GRANT SELECT ON "' || TRIM(owner) || '".' || TRIM(tabname) ||
        ' TO someuser AS dba;'
     FROM "informix".systables
     WHERE tabid >= 100;

从中获取输出并将其输入程序中。我使用 SQLCMD 来完成这项工作，尤其是因为它不会生成不需要的输出：

sqlcmd -d dbname -e "
 SELECT 'GRANT SELECT ON \"' || TRIM(owner) || '\".' || TRIM(tabname) ||
        ' TO someuser AS dba;'
     FROM "informix".systables
     WHERE tabid >= 100;" |
sqlcmd -d dbname -f -

额外的反斜杠是需要围绕外壳工作。 '-f -' 选项并不是严格必要的，但明确表明第二个 SQLCMD 将从标准输入读取。如果您使用 DB-Access，则必须担心它生成的冗词——这就是我最初编写 SQLCMD 的原因。

显然，这个解决方案可以很容易地编写脚本并进行推广 - 您只需小心使用引号即可。

使用角色

另一种选择是创建一个具有所需所有权限的角色，然后根据需要将该角色授予用户。这样做的缺点是您的软件需要设置角色，以便连接的人拥有该特权。一种可能性是将仅选择角色设置为每个用户的默认角色。

使用 Server Studio Java 版

SSJE 具有权限管理工具来简化此任务。

使用开放管理

OAT 也有一些工具可以帮助进行权限管理。

There isn't a simple way to do that (in either the SQL standard or in Informix), but it usually isn't necessary since that specific privilege is normally granted by default. However, I imagine that in fact you want to do more general operations too, and want to know what options there are.

Depending on what you are aiming to do and how you have your system set up, you can achieve your goals in various ways.

Brute force - direct answer to question

 SELECT 'GRANT SELECT ON "' || TRIM(owner) || '".' || TRIM(tabname) ||
        ' TO someuser AS dba;'
     FROM "informix".systables
     WHERE tabid >= 100;

Take the output from this and feed it into a program. I use SQLCMD for the job, not least because it doesn't generate unwanted output:

sqlcmd -d dbname -e "
 SELECT 'GRANT SELECT ON \"' || TRIM(owner) || '\".' || TRIM(tabname) ||
        ' TO someuser AS dba;'
     FROM "informix".systables
     WHERE tabid >= 100;" |
sqlcmd -d dbname -f -

The extra backslashes are needed to work around the shell. The '-f -' options aren't strictly necessary, but make it explicit that the second SQLCMD will read from standard input. If you use DB-Access, you have to worry about the verbiage it generates - which is the reason I wrote SQLCMD in the first place.

Clearly, this solution can fairly readily be scripted and generalized - you just have to be careful with your use of quotes.

Using roles

Another option is to create a role that has all the permissions that you want, and then grant that role to users as required. The downside to this is that your software needs to set the role so that people connecting have that privilege. One possibility is to set the select-only role as the default role for each user.