ORACLE：当对象被删除时，授权是否会被删除？
我目前有 2 个模式，A 和 B。B
有一个表，A 在其上执行选择插入和更新。
在我们的 sql 脚本中，我们已向 A 授予权限，以便它可以完成其任务。
grant select on B.thetable to A
etc,etc
现在，每天至少一次删除表“thetable”并将另一个表重命名为 B。
rename someothertable to thetable
执行此操作后，当 A 对 B.thetable 执行选择时，我们会收到错误。
ORA-00942: table or view does not exist
执行删除+重命名操作后，授权是否也可能丢失？
我们是否必须再次分配权限？
update
someothertable 没有任何授权。
update2
将数据插入“thetable”的日常进程每 N 次插入执行一次提交，因此无法执行任何回滚。这就是我们使用 2 个表的原因。
提前致谢
I currently have 2 schemas, A and B.
B has a table, and A executes selects inserts and updates on it.
In our sql scripts, we have granted permissions to A so it can complete its tasks.
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.
rename someothertable to thetable
After doing this, we get an error when A executes a select on 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.
update2
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.
Thanks in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是的，一旦您删除该表，补助金也会被删除。
您可以尝试创建一个从
thetable中选择的VIEW并对其授予SELECT权限。不过，你定期丢桌子的策略对我来说听起来不太正确。为什么你必须这样做？
编辑
有比每天删除桌子更好的方法。
向
thetable添加另一列，说明该行是否有效。在该列上放置索引（或扩展用于从该表中选择的现有索引）。
在
向您的查询添加另一个条件以仅考虑“有效”行或创建一个视图来处理该情况。
向您的查询添加
导入数据时，将新行设置为“new”。导入完成后，您可以在单个事务中删除所有“有效”行并将“新”行设置为“有效”。
如果导入失败，您可以回滚您的事务。
Yes, once you drop the table, the grant is also dropped.
You could try to create a
VIEWselecting fromthetableand grantingSELECTon that.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.
If the import fails, you can just rollback your transaction.
也许重命名表的过程还应该执行一个为您授予权限的过程？您甚至可以在字典中查询现有的补助金并将其应用到重命名的表中。
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.
不 ：
“Oracle 数据库自动将旧对象上的完整性约束、索引和授权转移到新对象。”
http://download.oracle.com/ docs/cd/B19306_01/server.102/b14200/statements_9019.htm#SQLRF01608
您一定还有其他问题
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
You must have another problem
另一种方法是使用临时表来完成您正在做的工作。毕竟，听起来这只是数据是暂时的，至少在该表中是这样，并且您不必每次拥有一组新数据/创建新表时都必须重新申请拨款
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