Oracle：对另一个用户的架构的只读访问权限？ [英] Oracle: Read-only access to the schema for another user?

问题描述

我有一个架构和一个名称相同的用户： products 。对于开发，我想在Java应用程序中以只读模式使用它。因此，我为只读应用程序创建了一个新用户。

I have a schema and a user with the same name: products. For development I would like to use it in a read-only mode from a java application. Therefore, I created a new user for the read-only application.

CREATE USER PRODUCTS_READONLY IDENTIFIED BY PRODUCTS_READONLY;
GRANT CREATE SESSION to PRODUCTS_READONLY;
BEGIN
  FOR tab IN (SELECT table_name FROM   all_tables WHERE  owner = 'PRODUCTS') LOOP
     EXECUTE IMMEDIATE 'GRANT SELECT ON PRODUCTS.'||tab.table_name||' TO PRODUCTS_READONLY';
  END LOOP;
END;

运行应用程序我得到了表不存在的错误。在互联网上搜索解决方案，我遇到了 SYNONYM 。所以我添加了模式的同义词：

Running the application I got the error that the table did not exist. Searching on the internet for solution, I came across SYNONYM. So I added synonym to the schema:

CREATE SYNONYM PRODUCTS_READONLY FOR PRODUCTS;

现在，我在 java application：

Now, I am getting this error in my java application:

ERROR org.hibernate.util.JDBCExceptionReporter - ORA-17074无效的名称模式：PRODUCTS_READONLY.PRODUCT_TYPE

我的方法出了什么问题？

What is wrong with my approach?

- 更新 -

似乎在10g中删除了创建模式的同义词（ Oracle：是吗？可以为模式创建同义词吗？）。如果我为目标模式中的每个对象创建模式，那么每次将表添加到目标模式或对目标模式中的任何其他对象进行任何其他更改时，我都必须执行相同的操作。这听起来很麻烦...

Seems like creating synonyms for schema was removed in 10g (Oracle: is it possible to create a synonym for a schema?). If I create schema for each object in the target schema, I would have to do the same every time a table is added to the target schema or any other changes to the any other objects in the target schema? That sounds cumbersome...

- 更新2 -

似乎像 alter session 的触发器是一个可能的解决方案，但它会使表只读，只要用户只有 SELECT 特权？

Seems like a trigger with alter session is a possible solution, but will it make the tables read-only so long the user has only SELECT privilege?

推荐答案

如果您可以控制应用程序的连接方式（例如连接池的初始化语句） ，你需要做的就是运行：

If you have control over the way your application connects (e.g. an initialization statement for your connection pool), all you need to do is run:

ALTER SESSION SET CURRENT_SCHEMA = PRODUCTS;

从那时起（在会话的生命周期内），将搜索任何不合格的对象名称。 产品架构。

From that point onward (during the lifetime of the session) any unqualified object name will be searched for in the PRODUCTS schema.

所有给予 PRODUCTS_READONLY 的拨款都将生效。会话将在用于登录的原始用户的凭据（和安全限制）下运行。

All grants given to PRODUCTS_READONLY will be in effect. The session will run under the credentials (and security restrictions) of the original user used to log in.

如果您无法更改建立或初始化连接的方式登录触发器也应该完成这个：

If you can not change the way the connection is established or initialized a logon trigger should also accomplish this:

create or replace trigger logon_trg
  after logon on database
begin
    if (user = 'PRODUCTS_READONLY') then
      execute immediate 'alter session set current_schema = products';
    end if;
exception
  when others then null; -- prevent a login failure due to an exception
end logon_trg;
/

请注意，捕获任何异常至关重要，因为否则执行的SQL中的潜在错误将有效地将所有人从数据库中注销掉。因此，在将其投入生产之前，请小心使用并进行测试。

Note that it's crucial to trap any exception, because otherwise a potential error in the executed SQL will effectively log everyone out off the database. So use with care and test it well before putting that into production.

这篇关于Oracle：对另一个用户的架构的只读访问权限？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！