Re: Restricting access to Source of procedures

From: Bonyata <jalexander_at_summitsoftwaredesign.com>
Date: 2000/05/31
Message-ID: <VA6Z4.7699$m91.45458@typhoon.tampabay.rr.com>#1/1

You also have a third option.

1. Create a role called exec_procedures.
2. Grant execute on all procedures to that role.
3. Grant exec_procedures to all users, enabling password protection.
4. Alter the users so that exec_procedures is NOT a default role.
5. Go into your first login screen, and add code that will enable that role.
6. Revoke execute privileges that were granted to the users.

What this does:
Since the role is not a default role, it is not enabled automatically when a user logs into SQLPlus. Since it is password protected, they cannot turn it on in SQLPlus. Therefore, within SQLPlus they will not have any privileges to roles.
However, within Forms you are going to enable the role, so within their forms sessions, users will have the ability to execute procedures. I think you can search Forms HELP (try looking under 'session'). It gives a pretty good write-up of how to implement. Also, if your reports call procedures, then you'll need to pass the enabled role with password as a parameter to the report (there is a specific 'run as role' parameter that you use for this).
I have used both this method and the encryption method for security. Both worked very well, although the encryption makes things difficult for developers since they can't use dba_source to check what code is in production. You may want to only encrypt the more sensitive code.

Allan Morris <allan.morris_at_actfs.com.au> wrote in message
news:3934984F.3BD6F7D8_at_actfs.com.au...

> I am running 7.1.6 of the database.
>
> I am trying to restrict the users from viewing the source of objects ie
> the source of procedures/triggers when in Sqlplus, as I see it I have
> two options :
>
> 1/ Remove any public grants/synonyms on all the system objects. Or
> simply on the views all_source and all_triggers.
>
> 2/ Encrypt the procedures/triggers in the database. Can someone point me
> in the right direction on how to do this ?
>
> Which is the best way to go ?
>
> Thanks
> Allan
>
>
>
Received on Wed May 31 2000 - 00:00:00 CDT