Solved

read only user for another schema

Posted on 2002-04-24
11
4,908 Views
Last Modified: 2008-03-17
I want to create a read only user that has read only access to all the objects in another users schema.  Can I do this easily?  Is there a way to set this up so that if a new object is created in the other users schema then the read only user also gets read only access.

thanks in advance
0
Comment
Question by:Lucret
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 5

Expert Comment

by:Bajwa
Comment Utility
"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



0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
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!
0
 
LVL 1

Author Comment

by:Lucret
Comment Utility
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.
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
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!
0
 
LVL 5

Expert Comment

by:Bajwa
Comment Utility
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.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Accepted Solution

by:
Bajwa earned 300 total points
Comment Utility
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.

0
 
LVL 1

Author Comment

by:Lucret
Comment Utility
How could I address assigning rights to dynamically created objects?
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
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).
0
 
LVL 1

Author Comment

by:Lucret
Comment Utility
Hi,

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

Expert Comment

by:Jankovsky
Comment Utility
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
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
Lucret,
   No, views require their own security.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now