Use the grant execute command to grant execute permission on a stored procedure to all users who have been granted a specified role. revoke execute removes this permission. But grant execute permission does not prevent users who do not have the specified role from being granted execute permission on the stored procedure.
For further security, you can restrict the use of a stored procedure by using the proc_role system function within the procedure to guarantee that a procedure can be executed only by users who have a given role. proc_role returns 1 if the user has a specific role (sa_role, sso_role, oper_role, or any user-defined role) and returns 0 if the user does not have that role. For example, here is a procedure that uses proc_role to see if the user has the System Administrator role:
create proc test_proc
as
if (proc_role("sa_role") = 0)
begin
print "You don’t have the right role"
return -1
end
else
print "You have SA role"
return 0
See “System Functions” in the Reference Manual for more information about proc_role.
