Re: Newbie Role Q

Elliot Ross wrote:
>
> I posted this once before, I did not get a response, however my ISP did
> replace a server, so god only knows......
>
> I thought that I could define a role (ie Developer), grant create etc to
> role developer, then add user_ID to the role, and voila, user_ID has
> create etc rights.
>
> It did not work. What am I doing wrong ??
> --
> My EMail address contains a # to try to
> thwart mail spamming.

Yes, that is the way that it is supposed to work. For example:

create role developer;
grant create table to developer;
grant developer to fred;

If you log in as fred, and use 'select * from session_privs;', you will see that fred does have create table.

There are times when this will not work. Specifically, you cannot create procedures, views, snapshots, or funtions that need a privilege that you get through a role. Lets say that you want to create a stored procedure that does a create table in your schema based on a table that is in another schema. The procedure owner will need the CREATE TABLE privilege and SELECT on the original table granted explicitly to it. If those privileges are granted through roles, you will not be able to compile the procedure.

I hope this helps,

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com