3

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;

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;

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

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

What is wrong with my approach?

--UPDATE--

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...

--UPDATE 2--

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?

Community
  • 1
  • 1
user3111525
  • 5,013
  • 9
  • 39
  • 64
  • 1
    Why not get a read only Connection to your database? (Connection.setReadOnly(true);) – wumpz Feb 11 '14 at 15:49
  • I'd like to not touch the java application. We have a several layers with frameworks and we do not use connections explicitly. I would avoid doing that and am wondering if it is possible to achieve on the database level. – user3111525 Feb 11 '14 at 20:00
  • @wumpz: According to the JavaDocs `setReadOnly()` is only a *hint* to the driver to "*enable database optimizations*" and a quick test shows that this does no prevent running DML statements. –  Feb 11 '14 at 20:30
  • @a_horse_with_no_name Could you create an answer with detailed trigger description/code? Thanks. – user3111525 Feb 12 '14 at 07:26

2 Answers2

2

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.

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;
/

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.

0

I am not sure you can create a synonym for schema.

But you can create a synonym for every object in the remote schema, e.g.

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;

Don't be confused with table_name, it handles all types of objects there.

smnbbrv
  • 23,502
  • 9
  • 78
  • 109