使用Oracle PL/SQL存储过程授予其他用户表的权限 [英] Using an Oracle PL/SQL stored procedure to grant rights on another user's tables
问题描述
我的应用程序执行以下操作时遇到问题:
I'm having an issue with an application that does the following:
- PL/SQL程序包'A'包含应用程序的所有功能/过程
- "A"归"USER_A"所有
- 'A'在Oracle中创建用户帐户，并在这些用户下创建表
- "A"还必须能够将用户表截断/插入
注意-所有创建的表都位于安全性较低的表空间中，我们将其称为"MY_TS". 尽管此程序包在每个新用户的架构中创建用户和表，但"A"无权插入或删除这些表. (Oracle中的默认值?)
Note - all created tables are within a low security tablespace, let's call it 'MY_TS'. Although this package creates the users and tables within the each new user's schemas, 'A' doesn't have the rights to INSERT to these tables, or truncate them. (default in Oracle?)
解决此问题的两种方法是:
Two approaches to solve this I am trying are:
- 在命令行调用sqlplus中回显GRANT语句，以新用户身份登录并向USER_A发出GRANT ALL ON [table]，或者
- 创建一个单独的过程(称为"B")，该过程由具有DBA权限的帐户创建并拥有.此过程使用"AUTHID DEFINER"运行，并在对USER_A语句运行GRANT ALL ON [table]之前先检查表以确保该表位于"MY TS"中
我遇到了方法1的障碍.尽管我对命令行字符串进行了测试，但是在Oracle中封装命令显然不那么容易(我不建议使用命令外壳，但我更喜欢使用SQL Server，但不建议调用该命令外壳！).我使用的作品非常完美.
I ran into a roadblock with approach #1. It's apparently not that easy in Oracle to shell out a command (I have much more exposure to SQL Server where calling to the command shell isn't advised, but is easy if you want to!), although my testing of the command line string I'm using works perfectly.
对于方法2，我编写了一个过程"B"，该过程用"AUTHID DEFINER"编译指示定义，并由具有DBA权限的帐户编译.此proc通过表空间检查(边缘安全性)，但是在执行GRANT语句时返回错误"ORA-01929:没有授予GRANT的权限"错误，该语句是使用传递到"EXECUTE IMMEDIATE"命令中的动态SQL构建的.
For approach #2, I wrote a proc 'B' that is defined with the "AUTHID DEFINER" pragma, and is compiled by an account with DBA-rights. This proc passes the tablespace check (limp security), but returns the error "ORA-01929: no privileges to GRANT" when executing the GRANT statement which is built using dynamic SQL passed into the 'EXECUTE IMMEDIATE' command.
任何人都有其他建议能更好地使用另一种方法，或者有一种方法可行(并与DBA一起使用)的解决方案吗?赋予USER_A更大的整体权限似乎并不可行.
Anyone have any tips for another approach that will work better or a solution to either approach that would work (and pass with the DBA)? Giving greater blanket rights to USER_A doesn't sound like it's going to be an option.
感谢您的提示/反馈！
丹
推荐答案
如果我提到在执行动态SQL(execute immediate)时不考虑通过角色获得的特权，可能会有所帮助.如果具有DBA权利的帐户"具有某些角色的这些权利，则这些权利将不会用于您的动态授权声明；您将需要显式授予该帐户用户适当的特权.
May be it will help if I mention that privileges acquired via roles are not considered when executing dynamic SQL (execute immediate). If the "account with DBA rights" has these rights from some roles, they won't be used for your dynamic grant statements; you will need to grant appropriate privileges to that account user explicitly.
这篇关于使用Oracle PL/SQL存储过程授予其他用户表的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
