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 应用程序中收到此错误:

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

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

我的方法有什么问题?

--更新--

似乎在 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;

从那时起(在会话的生命周期内)将在 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屋！