oracle 如何为正在删除和重新创建的表保留 GRANT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2900637/
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
How to make a GRANT persist for a table that's being dropped and re-created?
提问by Eli Courtwright
I'm on a fairly new project where we're still modifying the design of our Oracle 11g database tables. As such, we drop and re-create our tables fairly often to make sure that our table creation scripts work as expected whenever we make a change.
我在一个相当新的项目中，我们仍在修改我们的 Oracle 11g 数据库表的设计。因此，我们经常删除并重新创建我们的表，以确保每当我们进行更改时，我们的表创建脚本都能按预期工作。
Our database consists of 2 schemas. One schema has some tables with INSERTtriggers which cause the data to sometimes be copied into tables in our second schema. This requires us to log into the database with an admin account such as sysdbaand GRANTaccess to the first schema to the necessary tables on the second schema, e.g.
我们的数据库由 2 个模式组成。一个模式有一些带有INSERT触发器的表，这导致数据有时会被复制到我们的第二个模式中的表中。这要求我们使用管理员帐户登录数据库，例如sysdba，GRANT访问第一个模式到第二个模式上的必要表，例如
GRANT ALL ON schema_two.SomeTable TO schema_one;
Our problem is that every time we make a change to our database design and want to drop and re-create our database tables, the access we GRANT-ed to schema_onewent away when the table was dropped. Thus, this creates another annoying step wherein we must log in with an admin account to re-GRANTthe access every time one of these tables is dropped and re-created.
我们的问题是，每次我们作出改变到我们的数据库设计，并希望删除并重新创建数据库表中，我们访问GRANT-ed来schema_one就走了，当表被删除。因此，这会产生另一个烦人的步骤，GRANT每次删除和重新创建这些表中的一个时，我们必须使用管理员帐户登录以重新访问。
This isn't a huge deal, but I'd love to eliminate as many steps as possible from our development and testing procedures. Is there any way to GRANTaccess to a table in such a way that the GRANT-ed permissions survive a table being dropped and then re-created? And if this isn't possible, then is there a better way to go about this?
这没什么大不了的，但我很想从我们的开发和测试过程中尽可能多地消除步骤。有没有办法以GRANT这样的方式访问表，即GRANT-ed 权限在表被删除然后重新创建后仍然存在？如果这是不可能的，那么有没有更好的方法来解决这个问题？
回答by APC
So the reason why the grants get revoked is that the new table is a different object.
所以授权被撤销的原因是新表是一个不同的对象。
SQL> select object_id from user_objects
2 where object_name = 'T72'
3 /
OBJECT_ID
----------
659195
SQL> drop table t72
2 /
Table dropped.
SQL> create table t72 (id number)
2 /
Table created.
SQL> select object_id from user_objects
2 where object_name = 'T72'
3 /
OBJECT_ID
----------
659212
SQL>
The grants are on the object, not the object's name.
授予的是对象，而不是对象的名称。
What I don't understand about your problem is this: you have a process which drops and re-creates the tables in schema_two. Why doesn't that process also grant grant privileges on those tables to schema_one? Why do you have an admin account do it instead? I presume you are connecting as schema_twoto run the DROP and CREATE statements. Why not just add the GRANT statements to that step?
关于您的问题，我不明白的是：您有一个删除并重新创建schema_two. 为什么该进程不将这些表的授予权限授予schema_one？为什么你有一个管理员帐户来代替？我假设您正在连接schema_two以运行 DROP 和 CREATE 语句。为什么不直接将 GRANT 语句添加到该步骤？
Because granting privileges on objects is as much a part of the installation as creating the tables. So you ought to have a process which does everything.
因为授予对象特权与创建表一样是安装的一部分。所以你应该有一个可以做所有事情的过程。
回答by Alex Poole
You could grant select any table, insert any table, etc to schema_one, but that seems like overkill and won't reflect what you do in production (hopefully). Why can't you issue the grant at the same time as you create the table, while logged in as schema_two? I've always done that in the creation scripts, and only ever had to use an admin account to grant third-party or system privs. I suspect I'm missing something...
你可以授予select any table，insert any table等来schema_one，但似乎有点小题大做，也不会反映在生产中（希望）做什么。为什么不能在创建表的同时发出授权，同时以 schema_two 身份登录？我一直在创建脚本中这样做，并且只需要使用管理员帐户来授予第三方或系统权限。我怀疑我错过了什么......
回答by OMG Ponies
What are you doing that couldn't be handled by ALTER TABLE statements?
你在做什么 ALTER TABLE 语句无法处理的？
The next best option might be to create a view that references the table that occaisionally disappears - the view won't disappear, you'd just get an error if the table doesn't exist in such a situation. IE:
下一个最佳选择可能是创建一个视图来引用偶尔消失的表 - 视图不会消失，如果在这种情况下表不存在，您只会收到错误消息。IE：
CREATE VIEW table_vw AS
SELECT t.*
FROM DISAPPEARING_TABLE t
Using * notation would also mean you don't have to continually update the view to expose columns in the table.
使用 * 表示法还意味着您不必不断更新视图来公开表中的列。
回答by Gary Myers
You could have a DDL trigger or a batch job that runs every few minutes that grants privileges automatically. But that is a bit of a security hole and won't represent production.
你可以有一个 DDL 触发器或一个批处理作业，每隔几分钟运行一次，自动授予权限。但这有点安全漏洞，并不代表生产。
回答by Bob Jarvis - Reinstate Monica
I suggest that you might give the account which you use to create the tables the ability to run the grants as well.
我建议您可以让用于创建表的帐户也能够运行授权。
Share and enjoy.
分享和享受。
