read only user for another schema

"I want to create a read only user that has read only access to all the objects in another users schema. "

Create a role called "Username_readonly_access_role".
by "create role username_readonly_access_role"

and login as the owner  and grant select to all the objects  to the role. i.e. "grant select on TABLE_NAME to username_readonly_access_role"

Then grant that role to the user who will only have readonly privileges. i.e.

grant username_readonly_access_role to readonlyuser

Lucret,
   No, you can't grant "permanent" access to a schema to an individual user or role.  Each select-only permission is declarative and requires an entry in the appropriate (system) tables.  Some (complex) methods of approximating what you want are:

1) Have an automated routine regularly (monthly, daily, hourly) scan the target schema's user_objects view to see if there are any new tables or views (CREATED is since last run of the automated routine) and grant access.

2) Give them 'select any table' privs.  I do NOT recommend this, it's just an answer to your question.  If you granted it via a password-protected role, then you could make it available only via an application, thus limiting the impact.

3) Create a schema-independent user (this is a VERY special case).  Here's a link to explain it:

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/users.htm#10649


That should give you some ideas.  Post any questions you have on additional variations or thoughts this inspres.

Good luck!

How could I have this user so that they could access a table under another users schema without applying username.table but just table?
There are alot of tables in the user schema that I want to give read only access to the readonly user.  Is there a script I can use to grant select on all objects in the user schema.  What about another script to create a synonm for each table in the schema so that the readonly user can just select table and get the same as if user 1 selected table in its schema.

Lucret,
  It wouild depend on the solution you choose.

1,2) Automated routine and SELECT-ANY-TABLE privs: this could only be accomplished via synonyms (or views).  Synonyms let you refer to an object in another schema as if it was in your local schema.  You would either create public synonyms for each table/view, or (in the readonly schema) create synonyms for each object:

create public synonym emp for scott.emp;

would let EVERY user use just EMP to get at SCOTT.EMP (unless they had an EMP table in their own schema).

create synonym emp for scott.emp;

would allow a user with select access to have a local object named emp that actually pointed to scott.emp.

while there are no PUBLIC views, you could have local views that acted much like the local synonyms:

create view emp as select * from scott.emp;

Since the SELECT-ANY-TABLE privilege approach doesn't have a mechanism for creating these, it is easier to do this (creation of views/synonym) if you use the automated routine.

2) schema-independent user: What you want to do is part of the reason that these types of accounts were created.  You would assign the schema to the user when they connect (after OS SSL authentication).  Please read the link I gave and see if you can make it work for you.

Good luck!

How could I have this user so that they could access a table under another users schema without applying
username.table but just table?


Create a synonym for that table. i.e.
if a table is under schema scott named employee i.e. scott.employee then you can login as sys and create a  synonym

i.e.
"create synonym employee for scott.employee;


There are alot of tables in the user schema that I want to give read only access to the readonly user.
 Is there a script I can use to grant select on all objects in the user schema.  What about another
script to create a synonm for each table in the schema so that the readonly user can just select table
and get the same as if user 1 selected table in its schema.

You can build a dynamic sql script

login as owner and run

spool select_grants.sql

select 'grant select on '||table_name||' to name_of_role_or_user ;' from user_tables
/

spool off

then you can edit/view/run the select_grants.sql to grant the privileges to the role or user.

You can also dynamically build a script for all synanoms. i.e.

spool all_synonyms_for_user_X.sql
select 'create synonym '||table_name||' for owner.'||table_name from user_tables
/

spool off

now by running all_synonyms_for_user_X.sql will create synonyms.

How could I address assigning rights to dynamically created objects?

Lucret,
   I assume that last question was really for Bajwa.  That's the reason I suggested the automated routine or the schema-independent users.  It takes care of new objects (dynamically created or otherwise).

Hi,

If a user has select access on a table can they automatically see / select from a view created using data in that table?

Hi,
there is possibility, except methods, described above.

run this statement after each connection of Read-only user:

ALTER SESSION SET CURRENT_SCHEMA = <tables owner>;

To get fully transparent read only access to another schema objects, you can:

1. use SELECT ANY TABLE privilege, but it's not recommended because of open access (read-only) to any table (view) in database instance. In this case You should organize database instance, regarding this fact.

2. create procedure, who reconcile dynamicaly all object select rights on schema to r-o user. Then You should create DDL trigger after create to call it. This way you can combine with access via synonyms (see above).

Bob

Lucret,
   No, views require their own security.