Oracle：如何为另一个用户提供只读模式下的schema访问权限？

Question

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

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。因此，我将同义词添加到模式中：

CREATE SYNONYM PRODUCTS_READONLY FOR PRODUCTS;

现在，在我的java应用程序中出现了以下错误：

ERROR org.hibernate.util.JDBCExceptionReporter - ORA-17074 invalid name pattern.: PRODUCTS_READONLY.PRODUCT_TYPE

我的方法有什么问题吗？

--更新--

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

--更新2--

似乎使用带有alter session的触发器是一种可能的解决方案，但它会使表只读，只要用户只具有SELECT权限吗？

- user3111525

---

为什么不给你的数据库获取一个只读连接？（Connection.setReadOnly(true);） - wumpz

我不想动Java应用程序。我们有几个带有框架的层，而且我们不会明确使用连接。我想避免这样做，并且想知道是否可能在数据库层面上实现。 - user3111525

根据JavaDocs所述，setReadOnly()仅是驱动程序优化数据库的“提示”，而快速测试表明这并不会阻止运行DML语句。 - user330315

@a_horse_with_no_name 您能创建一个详细的触发器描述/代码的答案吗？谢谢。 - user3111525

Answer 1

如果您可以控制应用程序连接的方式（例如，连接池的初始化语句），您只需要运行以下命令：

ALTER SESSION SET CURRENT_SCHEMA = PRODUCTS;

从那时起（在会话的生命周期内），任何未经限定的对象名称都将在PRODUCTS模式中搜索。

授予PRODUCTS_READONLY的所有授权将生效。会话将在使用登录的原始用户的凭据（和安全限制）下运行。

如果您无法更改连接建立或初始化方式，则登录触发器也可以实现此目的：

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 中可能存在潜在错误，会导致所有人从数据库中注销。因此，请谨慎使用并在投入生产之前进行充分测试。

- user330315

Answer 2

我不确定你能否为模式创建同义词。

但你可以为远程模式中的每个对象创建同义词，例如：

begin
  for c in (select t.table_name from table_privileges t where grantee = 'PRODUCTS_READONLY') loop
    execute immediate 'create synonym '||c.table_name||' for PRODUCTS.'||c.table_name;
  end loop;
end;

不要被table_name搞混了，它处理那里的所有类型的对象。

- smnbbrv

---

当我以products_readonly身份登录并执行语句时，出现错误“表或视图不存在”。 - user3111525