Solved

read only user for another schema

Posted on 2002-04-24
11
4,953 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
ID: 6965916
"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
ID: 6965997
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
ID: 6966306
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
ID: 6966504
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
ID: 6966518
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
ID: 6966530
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
ID: 6967903
How could I address assigning rights to dynamically created objects?
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6968399
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
ID: 6977190
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
ID: 6977498
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
ID: 6977846
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle rollup query 3 50
Oracle Subquery bad Join 11 59
report returning null 21 79
Schema creation in Oracle12c 6 38
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

932 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

12 Experts available now in Live!

Get 1:1 Help Now