Oracle 11g：如何使用测试用户的测试表覆盖默认模式表
我知道这是可以做到的，因为我在上一个工作场所见过它，但我不知道如何复制！
基本上，有一个 MASTER 用户对我们所有的表都有写权限。在我们应用程序的数据库适配器连接设置中，我们使用 DEFAULT_SCHEMA: MASTER
我为自己创建了一个新的测试用户（与主用户在同一数据库上，不使用数据库链接），以便我可以自由创建测试数据而不弄乱真实数据。然后为我的测试用户复制了一个表，以便我可以自由操作数据： create table SIMMBOT.real_data_table as select * from MASTER.real_data_table
问题是我不知道如何设置连接以便 Oracle 知道用我自己的 SIMMBOT.real_data_table 覆盖 MASTER.real_data_table。我有预感，您实际上无法在连接设置中执行此操作...所以从第一个方开始，我需要做什么才能设置这样的测试表？像共享模式之类的东西？
I know this can be done, because I've seen it at my last workplace, but I don't know how to replicate!
Basically, there is a MASTER user who has write privileges for all our tables. In our application's DB adapter connection settings, we use DEFAULT_SCHEMA: MASTER
I have created a new test user for myself (on the same database as the master user, not using a database link) so that I can freely create test data without messing with real data. Then copied a table for my test user so that I can freely manipulate data: create table SIMMBOT.real_data_table as select * from MASTER.real_data_table
The problem is that I don't know how to set up the connection so that Oracle knows to override MASTER.real_data_table with my own SIMMBOT.real_data_table. I have a hunch that you can't actually do that in the connection settings... so starting from square one, what would I have to do to set up test tables like this? Something like a shared schema?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您的代码使用完全限定的表名称（即
MASTER.real_data_table或SIMMBOT.real_data_table），那么从配置的角度来看，无法更改所引用的对象。但是，假设您的代码没有使用完全限定的表名称 - 如果它只是从
real_data_table中进行选择，那么 Oracle 将首先在当前模式中查找具有该名称的对象，然后查找该名称的公共同义词。如果您以
MASTER身份连接，则可以更改当前架构。完成此操作后，对表名称的所有非限定引用都将解析为
SIMMBOT架构中的表。请注意，MASTER用户需要单独获得对SIMMBOT架构中对象的适当访问权限 - 设置当前架构仅影响名称解析，而不影响权限。SIMMBOT架构还需要拥有代码想要引用的每个表 - 无法指定用于解析不合格名称的层次结构。您不能告诉 Oracle 首先解析SIMMBOT架构中的不合格名称，然后解析MASTER架构中的不合格名称。另一种方法是为每个表创建同义词并操作同义词来为部分或所有用户引用您的表。如果您的应用程序以不拥有任何对象的第三个用户身份登录（例如
APP_USER），您可以在APP_USER架构中创建指向不同对象的私有同义词。不同模式中的对象——或者您可以创建适用于所有用户（拥有该对象的用户除外）的公共同义词
If your code is using fully qualified table names (i.e.
MASTER.real_data_tableorSIMMBOT.real_data_table) then there is no way from a configuration standpoint to change what object is being referred to.Assuming, however, that your code is not using a fully qualified table name-- if it is just selecting from
real_data_table, then Oracle will first look for an object in the current schema with that name then look for a public synonym with that name.If you connect as
MASTER, you can change the current schemaOnce you've done that, all unqualified references to a table name will resolve to tables in the
SIMMBOTschema. Note that theMASTERuser would need to be granted appropriate access on the objects in theSIMMBOTschema separately-- setting the current schema only affects name resolution, not privileges. TheSIMMBOTschema would also need to have every table that the code wants to reference-- there is no way to specify a hierarchy for resolving unqualified names. You can't tell Oracle to first resolve unqualified names in theSIMMBOTschema and then theMASTERschema.An alternative would be to create synonyms for each table and manipulate the synonyms to reference your table for some or all users. If your application logged in as a third user that did not own any objects--
APP_USERfor example-- you could create either private synonyms in theAPP_USERschema that pointed to different objects in different schemas--or you could create public synonyms that would apply to all users (other than those that owned the objects)