create a select role on application schema

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?
sk0227Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sk0227Author Commented:
thanks for quick response.

how do i grant execute on all appuser package/procedure/function ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.