How to grant a role to public


How to grant a role to public


I have created a new SP. I have a role created to which i have given execute permission on this stored procedure.

Now, this SP is a generic one and i want to give execute permission on this SP to all. One way is- i grant this role to all the users explicitly. Another option is i grant this role to a user - Public. I did the 2nd way, but users still get "execute permission error" on this SP. As per my understanding, if you give any access to user - public, it goes to all the users. Could someone point me out what wrong am i doing here.

Thanks !


Re: How to grant a role to public

If I dont miss out: 

create role role1;


grant role1 to xxxxxxxxxxx;

modify user xxx as default role=all;

You can double-check here:


FROM dbc.AllRights

WHERE DatabaseName = 'yourdb' AND TableName = 'xxx'

AND AccessRight = 'PE'



Re: How to grant a role to public

What exact error is returned?

For SPs there's a SECURITY option either CREATOR/DEFINER/OWNER or INVOKER.

You should check the DDL manual which one you actually need.

Re: How to grant a role to public

Hi Diether,

from the DDL manual for roles the restriction is :

Grantees can be users or roles; however, a role cannot be granted to

itself or to PUBLIC.


- But when you execute sql , grant my rolename to public - it works fine and you can see that the role is granted to public by running

sel * from dbc.rolemembers

where rolename ='my rolename'

And on the role , i have given access to execute the SP.

- The the problem is , the users dont get access to the SP that i have given permission on to the role. The error says - the user dont have exeute priv to the SP

- Instead if i give access on the SP at the object level to Public, it works

- So the question is - can't we give a role to a public ?


Re: How to grant a role to public

Trying to grant a role to public should return an error message.

Public is a role and it's always active for every user. 

So simply grant the right directly to public.

Re: How to grant a role to public

thanks diether !!