Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle user privileges

Posted on 2003-03-04
4
Medium Priority
?
696 Views
Last Modified: 2012-06-27
Hello...  I have a database with 2 schema's on it (one is for testing). Is there a way to grant privileges to a user with read only access to only ONE of the schema's so they can access reports runtime? If so, how?

THANKS!!!!
0
Comment
Question by:barbara6555
  • 2
4 Comments
 
LVL 8

Expert Comment

by:heskyttberg
ID: 8068316
Hi!

Short answer no you cannot.
You can't grant such a privilege.

What you can do is give a role or user, in your case role might be better so you can remove/add role to user(s) at will.

What you need to do is create a role that has select rights on all views and tables in that schema.

Then the user can do:
select * from other_schem.table

Easiest to do is:
login as user1:

select 'grant select on ||'table_name||' to user2' from user_tables;

Now you can cut and paste this into a sql script or directly into sqlplus.

This can be done for all objects and privileges that needs to be set.

Hope this helps.

Regards
/Hans - Erik Skyttberg
0
 
LVL 8

Expert Comment

by:heskyttberg
ID: 8068323
Hi!

Don't forget the ';' behind user2, :)

Regards
/Hans - Erik Skyttberg
0
 
LVL 7

Accepted Solution

by:
Bruce Cadiz earned 200 total points
ID: 8072749
barbara6555

We use the following method for what I believe you are asking:
1) create public synonym(s) for the schema objects that are required to run the reports (i.e. scott.emp, scott.dept, etc)
CREATE PUBLIC SYNONYM EMP FOR SCOTT.EMP;
....

2) create a role (i.e. REPORT_READER_ROLE) for report access
CREATE ROLE REPORT_READER_ROLE;

3) grant select on schema objects to the role
GRANT SELECT ON EMP TO REPORT_READER_ROLE;
....

4) grant roles to applicable users we want to have access to the reports.

GRANT REPORT_READER_ROLE TO REPORT_USER;

This allows you to add and remove users from report access by just granting/revoking priviliges to the role. You can also add passwords to the role for increased security.

0
 

Author Comment

by:barbara6555
ID: 8106412
Everyone was very helpful. Thanks for spelling it out for me.  This worked great!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

571 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