How to use one schema by multiple users without pre-fixing schema name before the objects?

Question

I have searched for many posts to achieve the below problem. But could not get the solution. Can some one resolve this please.

Problem. I have created a user/Schema with name RAMFULL with connect,resource privileges granted. and I want to access this user with oracle user called RAMREAD as alias having read only privilege. I need not mention the Adminitrator/full privilege user RAMFULL to others during the accessing/using the objects in RAMFULL.

1. I have created a user/Schema with name RAMFULL

2. I am using this user/schema with same username for all CRUD operations.

3. The problem is I have to give this schema to my development center to analyze an issue. but I do not want to give them this db as it is. Since it has all the privileges and it can be tampered or experimented by them.

4. NOW, I am looking for a solution to create another user which will have only read only privileges and the moment he logs into that account/schema, a person should feel like working on Original schema except the privileges.

5. I have seen many articles and I could able to get creating synonyms or creating role with Ro by assigning to another user. But in all these the problem is when I do select I have to pre-fix the schema name. For example if I need to search for a table I have to give Select count(*) from SCHEMA1.TABLE1;

   I want to know if Schema RAMFULL can be access using RAMREAD user with Read Only privileges. I should not give/mention RAMFULL user anywhere while I am working on RAMREAD user. Please help me out.

Answer 1

You can alter the session to do this:

alter session set current_schema = RAMREAD;

To quote:

Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema.

It doesn't give you any additional privileges so you would still need to grant SELECT on all the tables in RAMFULL that you wanted the user logged into RAMREAD to be able to see

Answer 2

You can create public synonyms for all the schema objects, which will be a bit painful and modifies your original schema (sort of) so might not be desirable. Or you can create private synonyms owned by RAMREAD, which is still painful to do. For what you are describing the simplest approach might be a logon trigger that sets that user's default schema:

create or replace trigger ramread_logon_trigger
after logon on database
begin
  if user = 'RAMREAD' then
    execute immediate 'alter session set current_schema = RAMFULL';
  end if;
end;
/

You can alter the session manually, but this means the person logging doesn't need to know the RAMFULL name, which you sort of suggested you wanted; though they can still figure it out from looking at all_objects.

As Ben pointed out, you still need to grant privileges (select only, presumably, for a read-only account; probably via a role) to the objects you want RAMREAD to be able to see.

If you want it to be more generic and have a read-only role, you can test if the logged-in user has that role enabled, and alter session if it is - then the trigger will apply to anyone with that role, not named users, so you won't have to modify the trigger when you add another read-only user.

create role readonly_role;
grant select on ramfull.some_table to readonly_role;
...

grant readonly_role to ramread;

create or replace trigger readonly_logon_trigger
after logon on database
begin
    if (dbms_session.is_role_enabled('READONLY_ROLE')) then
        execute immediate 'alter session set current_schema = RAMFULL';
    end if;
end;
/