Check Roles in Procedures

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.