You can set execute permissions for a stored procedure that you own to allow access to the stored procedure by specific users or groups. In many databases, if you are not the database owner, then you must explicitly grant permissions for your stored procedure to other users. You can grant permissions to all users, or to just a single user or group. The syntax for both is similar.
Note When you create a stored procedure in an Oracle database, you must explicitly grant permissions for your stored procedure to other users. If you do not grant permissions to a user, that user must have the EXECUTE ANY PROCEDURE system privilege in order to execute the procedure.
To grant permissions to all users
sp_name
with the name of your stored procedure:Grant Execute On sp_name To Public
To grant permissions to a user or group
sp_name
with the name of your stored procedure, and replacing user
with the name of the user or group to grant permissions to:Grant Execute On sp_name To user
For example, if you want to grant permissions on a stored procedure to a group named Managers, you can create an SQL script file and add the following SQL statement:
Grant Execute On MyPublicProcedure To Managers
Note Microsoft® Visual Basic™ does not support using SQL scripts to change permissions on a stored procedure. You'll need to use a utility such as SQL Enterprise Manager.