Solved

create a select role on application schema

Posted on 2012-03-16
4
566 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 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 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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

830 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