Solved

create a select role on application schema

Posted on 2012-03-16
4
567 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
RHEL6 + dockers - No route to host 7 99
Query - Duplicate dates with different activities counts 10 57
how to use l_instance in the host command 7 34
oracle query 4 30
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

737 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