Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

create a select role on application schema

Posted on 2012-03-16
4
Medium Priority
?
570 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
[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 23

Assisted Solution

by:David
David earned 1000 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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Fine Tune your automatic Updates for Ubuntu / Debian
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

704 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