ORACLE：删除对象时是否会删除授权？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2262966/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE :Are grants removed when an object is dropped?
提问by Tom
I currently have 2 schemas, A and B.
我目前有 2 个模式，A 和 B。
B has a table, and A executes selects inserts and updates on it.
B 有一个表，A 在它上面执行选择插入和更新。
In our sql scripts, we have granted permissions to A so it can complete its tasks.
在我们的 sql 脚本中，我们已授予 A 权限，以便它可以完成其任务。
grant select on B.thetable to A
etc,etc
Now, table 'thetable' is dropped and another table is renamed to B at least once a day.
现在，表 'thetable' 被删除，另一个表至少每天一次重命名为 B。
rename someothertable to thetable
After doing this, we get an error when A executes a select on B.thetable.
这样做之后，当 A 在 B.thetable 上执行选择时，我们会得到一个错误。
ORA-00942: table or view does not exist
Is it possible that after executing the drop + rename operations, grants are lost as well?
是否有可能在执行删除 + 重命名操作后，授权也会丢失？
Do we have to assign permissions once again ?
我们是否必须再次分配权限？
update
更新
someothertable has no grants.
someothertable 没有赠款。
update2
更新2
The daily process that inserts data into 'thetable' executes a commit every N insertions, so were not able to execute any rollback. That's why we use 2 tables.
将数据插入“thetable”的日常过程每 N 次插入执行一次提交，因此无法执行任何回滚。这就是我们使用 2 个表的原因。
Thanks in advance
提前致谢
回答by Peter Lang
Yes, once you drop the table, the grant is also dropped.
是的，一旦您删除该表，该授权也会被删除。
You could try to create a VIEWselecting from thetableand granting SELECTon that.
你可以尝试创建一个VIEW选择thetable并授予SELECT它。
Your strategy of dropping a table regularly does not sound quite right to me though. Why do you have to do this?
不过，您定期放下桌子的策略对我来说听起来不太正确。为什么你必须这样做？
EDIT
编辑
There are better ways than dropping the table every day.
有比每天丢桌子更好的方法。
Add another column to
thetablethat states if the row is valid.Put an index on that column (or extend your existing index that you use to select from that table).
Add another condition to your queries to only consider "valid" rows or create a view to handle that.
When importing data, set the new rows to "new". Once the import is done, you can delete all "valid" rows and set the "new" rows to "valid" in a single transaction.
thetable如果该行有效，则向该状态添加另一列。在该列上放置一个索引（或扩展用于从该表中进行选择的现有索引）。
向您的查询添加另一个条件以仅考虑“有效”行或创建一个视图来处理该问题。
导入数据时，将新行设置为“new”。导入完成后，您可以在单个事务中删除所有“有效”行并将“新”行设置为“有效”。
If the import fails, you can just rollback your transaction.
如果导入失败，您可以回滚您的交易。
回答by DCookie
Perhaps the process that renames the table should also execute a procedure that does your grants for you? You could even get fancy and query the dictionary for existing grants and apply those to the renamed table.
也许重命名表的过程也应该执行一个程序来为您提供授权？您甚至可以幻想并查询现有授权的字典并将其应用于重命名的表。
回答by Jon T
Another approach would be to use a temporary table for the work you're doing. After all, it sounds like it is just the data is transitory, at least in that table, and you wouldn't keep having to reapply the grants each time you had a new set of data/create the new table
另一种方法是使用临时表来处理您正在执行的工作。毕竟，这听起来只是数据是暂时的，至少在该表中，并且每次您拥有一组新数据/创建新表时，您都不必重新申请授权
回答by FabienM
No : "Oracle Database automatically transfers integrity constraints, indexes, and grants on the old object to the new object." http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9019.htm#SQLRF01608
否：“Oracle 数据库自动将旧对象上的完整性约束、索引和授权转移到新对象。” http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9019.htm#SQLRF01608
You must have another problem
你肯定有其他问题
