?
Solved

Oracle user privileges

Posted on 2003-03-04
4
Medium Priority
?
686 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 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