Solved

create a select role on application schema

Posted on 2012-03-16
4
562 Views
Last Modified: 2012-08-13
hello Experts,

i have to grant select access for user  on all tables in a application schema. i need suggestion while creating role.

here is my script :

select 'grant select on '||owner||'.'||table_name||' to sel_role;' from dba_tables where owner='APPUSER';

Here are my considerations.

i have to provide only read only access on application tables.

what are the system privileges i need to grant to the users?
object privileges (i am creating role on application objects) i can assign the role to user.
do i need to give execute access on function/procedures/packages to the user?
0
Comment
Question by:sk0227
  • 2
4 Comments
 
LVL 23

Assisted Solution

by:David
David earned 250 total points
ID: 37731529
You're planning to execute the script as the object owner APPSUSER, correct?  But while EXECUTE is required on PL/SQL objects, I wouldn't grant it wholesale;  find the specific objects needed.

Users of APPSUSER objects will require synonyms on the objects, either private or public depending upon your security needs.

No SYSTEM privileges are required.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37731665
Not sure of your starting point but the minimum 'system' priv is: create session.  Since you hard-coded it, you need to grant sel_role to them as well.

Anything over that is just a bonus.
0
 

Author Comment

by:sk0227
ID: 37743936
thanks for quick response.

how do i grant execute on all appuser package/procedure/function ?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 37744237
I would not just grant permission on ALL owned objects to a user.  This can open up major security holes.  For example: Say the appuser has a procedure used by the app to drop objects.  If you grant a user access to it, now tht user can drop objects.

>>how do i grant execute on all appuser package/procedure/function ?

I'm not following the real question here.  Are you asking how to find the list of those objects?

The list of functions is found with:
select object_name from dba_objects where object_type in ('FUNCTION','PROCEDURE','PACKAGE') and owner = 'APPUSER';
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
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.
This video shows how to recover a database from a user managed backup

747 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

13 Experts available now in Live!

Get 1:1 Help Now