All Forums Database
teradatauser2 236 posts Joined 04/12
15 Jan 2014
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 !

Raja_KT 1246 posts Joined 07/09
15 Jan 2014

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'




Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
16 Jan 2014

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.


teradatauser2 236 posts Joined 04/12
17 Jan 2014

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 ?

dnoeth 4628 posts Joined 11/04
17 Jan 2014

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.


teradatauser2 236 posts Joined 04/12
17 Jan 2014

thanks diether !!

You must sign in to leave a comment.