Use has_role within a stored procedure to guarantee that only users with a specific role can execute the procedure. Only has_role provides a fail-safe way to prevent inappropriate access to a particular stored procedure.
You can use grant execute to grant execute permission on a stored procedure to all users who have been granted a specified role. Similarly, revoke execute removes this permission.
However, grant execute permission does not prevent users who do not have the specified role from being granted execute permission on a stored procedure. To ensure, for example, that all users who are not system administrators can never be granted permission to execute a stored procedure, use has_role within the stored procedure itself to check whether the invoking user has the correct role to execute the procedure.
has_role takes a string for the required role and returns 1 if the invoker possesses it. Otherwise, it returns 0.
For example, here is a procedure that uses has_role to see if the user has the sa_role role:
create proc test_proc
as
if (has_role("sa_role") = 0)
begin
print "You don’t have the right role"
return -1
end
else
print "You have System Administrator role"
return 0
