If a stored procedure performs system administration or security-related tasks, you may want to ensure that only users who have been granted a specific role can execute it. The proc_role function allows you to check roles when the procedure is executed; it returns 1 if the user possesses the specified role. The role names are sa_role, sso_role, and oper_role.
Here is an example using proc_role in the stored procedure test_proc to require the invoker to be a system administrator:
create proc test_proc
as
if (proc_role("sa_role") = 0)
begin
print "You do not have the right role."
return -1
end
else
print "You have SA role."
return 0
For example:
test_proc
You have SA role.
