Create and Granting a Role into a User
Jack created roles, granted table privileges to them and granted the roles to user1, but user1 still got "no SELECT/INSERT permission". Art Kagel explained that GRANT ROLE only gives the user the right to assume the role: the user must issue SET ROLE in the same session, and SET ROLE does not persist across sessions. Putting SET ROLE role_write after the DATABASE statement in the same dbaccess script worked. For a persistent setting, GRANT DEFAULT ROLE is needed, but Fernando Nunes noted default roles only appeared in IDS 10, so they're unavailable on Jack's older 9.x server.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
I'm trying to grant permission to "user1" using role. I have the script below:
database storedb;
drop table tab1;
drop table tab2;
create table tab1 ( cod integer , desc char(10) );
revoke all on tab1 from public;
create table tab2 ( cod integer , desc char(10) );
revoke all on tab2 from public;
drop role role_read;
drop role role_write;
create role role_read;
create role role_write;
grant select on tab1 to role_read;
grant select on tab2 to role_read;
grant select,insert,update,delete on tab1 to role_write;
grant select,insert,update,delete on tab2 to role_write;
grant role_read to user1;
grant role_write to user1;
After executing the above script, user1 still cannot insert nor read from tab1
or tab2. I am using IDS 9.4 on AIX 5.x
Thanks for the help.
Hello Jack,
Have you granted "user1" the "connect" priviledges?
database storedb;
grant connect to user1;
Regards,
Vikas
*******************************************************************************
Hi All,
I'm trying to grant permission to "user1" using role. I have the script below:
database storedb;
drop table tab1;
drop table tab2;
create table tab1 ( cod integer , desc char(10) );
revoke all on tab1 from public;
create table tab2 ( cod integer , desc char(10) );
revoke all on tab2 from public;
drop role role_read;
drop role role_write;
create role role_read;
create role role_write;
grant select on tab1 to role_read;
grant select on tab2 to role_read;
grant select,insert,update,delete on tab1 to role_write;
grant select,insert,update,delete on tab2 to role_write;
grant role_read to user1;
grant role_write to user1;
After executing the above script, user1 still cannot insert nor read from tab1
or tab2. I am using IDS 9.4 on AIX 5.x
Thanks for the help.
Hi, Yes, i can connect to storedb without any problem... But when i am performing a "select * from tab1", i'm getting error "no select permission". Also, I can't use the command "GRANT ROLE role_read TO userid", I can do only "GRANT role_read TO userid". I'm getting syntax error. On the examples i found, they have "GRANT ROLE rolename ..... ". I also tried "SET ROLE role_read" but no luck.
Regards. I think you´ll need to specify the "grant default role" statement for each user desired. Otherwise the database engine doesn´t know what´s the role you desire to use on the session... Att. Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix IIUG Member <http://www.iiug.org>; JACK PAPA escreveu: > Hi, > > Yes, i can connect to storedb without any problem... But when i am performing > a "select * from tab1", i'm getting error "no select permission". > > Also, I can't use the command "GRANT ROLE role_read TO userid", I can do only > "GRANT role_read TO userid". I'm getting syntax error. On the examples i > found, they have "GRANT ROLE rolename ..... ". > > I also tried "SET ROLE role_read" but no luck. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >
User1 has to:
SET ROLE read_role;
or
SET ROLE write_role;
in his/her session in order to acquire the role's privileges. The GRANT
ROLE just grants user1 permission to set that role. If you are using 11.xx
you can create a login procedure to set the role for each users on login or
you can use a default role, but the users would still need to use the SET
ROLE to switch between the read-only and write privileged roles if needed.
You can make one of these a DEFAULT ROLE for one or more users with:
GRANT DEFAULT ROLE read_role TO user1;
Then that user(s) has that role be default and doesn't have to SET ROLE
unless they want to change to a different role.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Thu, Nov 5, 2009 at 3:00 AM, JACK PAPA <informix2009@gmail.com> wrote:
> Hi All,
>
> I'm trying to grant permission to "user1" using role. I have the script
> below:
>
> database storedb;
> drop table tab1;
> drop table tab2;
> create table tab1 ( cod integer , desc char(10) );
> revoke all on tab1 from public;
> create table tab2 ( cod integer , desc char(10) );
> revoke all on tab2 from public;>
> drop role role_read;
> drop role role_write;
> create role role_read;
> create role role_write;
>
> grant select on tab1 to role_read;
> grant select on tab2 to role_read;>
> grant select,insert,update,delete on tab1 to role_write;
> grant select,insert,update,delete on tab2 to role_write;>
> grant role_read to user1;
> grant role_write to user1;>
> After executing the above script, user1 still cannot insert nor read from
> tab1
> or tab2. I am using IDS 9.4 on AIX 5.x
>
> Thanks for the help.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0023545bd84cbb8e2204779e53b9
Hi Art,
I was able to set the role using "set role role_write" using user1 account
with the output message "Role Set". However, if i try to load data into tab1,
i'm still getting error "275 No INSERT permission". I did a "dbschema -d
dbname dbname.sql" and below are the permission setup.
grant connect to "user1";
create role "role_write" ;
grant "role_write" to "user1" ;
grant select on "informix".tab1 to "role_write" as "informix";
grant update on "informix".tab1 to "role_write" as "informix";
grant insert on "informix".tab1 to "role_write" as "informix";
grant delete on "informix".tab1 to "role_write" as "informix";
grant select on "informix".tab2 to "role_write" as "informix";
grant update on "informix".tab2 to "role_write" as "informix";
grant insert on "informix".tab2 to "role_write" as "informix";
grant delete on "informix".tab2 to "role_write" as "informix";
Note that SET ROLE is only effective for that one session. Are you trying
to do the insert in the same session that you have run the SET ROLE
role_write; ?? What query/front-end tool are you using to test this?
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Thu, Nov 5, 2009 at 9:00 PM, JACK PAPA <informix2009@gmail.com> wrote:
> Hi Art,
>
> I was able to set the role using "set role role_write" using user1 account
> with the output message "Role Set". However, if i try to load data into
> tab1,
> i'm still getting error "275 No INSERT permission". I did a "dbschema -d
> dbname dbname.sql" and below are the permission setup.
>
> grant connect to "user1";>
> create role "role_write" ;
>
> grant "role_write" to "user1" ;
>
> grant select on "informix".tab1 to "role_write" as "informix";
> grant update on "informix".tab1 to "role_write" as "informix";
> grant insert on "informix".tab1 to "role_write" as "informix";
> grant delete on "informix".tab1 to "role_write" as "informix";
> grant select on "informix".tab2 to "role_write" as "informix";
> grant update on "informix".tab2 to "role_write" as "informix";
> grant insert on "informix".tab2 to "role_write" as "informix";
> grant delete on "informix".tab2 to "role_write" as "informix";>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0023545bd644f44c190477aa470b
Hi Art,
this is my insert statement:
database storedb;
insert into tab1 values ('00001',"One");
insert into tab1 values ('00002',"Two");
insert into tab1 values ('00003',"Three");
insert into tab1 values ('00004',"Four");
insert into tab1 values ('00005',"Five");
i'm trying to insert into tab1 using user1 on dbaccess - load.sql. I did
execute "SET ROLE role_write" using user1 prior to executing the dbaccess. I
did open a new window to do the insert.
Did i miss anything on my procedure? I can't use 11.5 version.
Hi Art, after removing the "database storedb", i was able to insert records into tab1. However, if i close the window and executing again (without the "set role role_write"), i wasnt able to insert the records. Is there any way my i can insert without re-executing the "set role command"?
Make the sequence:
database storedb;
set role role_write;
insert into tab1 values ('00001',"One");
insert into tab1 values ('00002',"Two");
insert into tab1 values ('00003',"Three");
insert into tab1 values ('00004',"Four");
insert into tab1 values ('00005',"Five");
The set role is ONLY effective for the current database session, it does NOT
persist. You would have to use a default role to make it permanent and I'm
not sure if your release of IDS supports default roles (try it and see - see
my earlier post about that).
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Thu, Nov 5, 2009 at 9:52 PM, JACK PAPA <informix2009@gmail.com> wrote:
> Hi Art,
>
> this is my insert statement:
>
> database storedb;>
> insert into tab1 values ('00001',"One");
> insert into tab1 values ('00002',"Two");
> insert into tab1 values ('00003',"Three");
> insert into tab1 values ('00004',"Four");
> insert into tab1 values ('00005',"Five");>
> i'm trying to insert into tab1 using user1 on dbaccess - load.sql. I did
> execute "SET ROLE role_write" using user1 prior to executing the dbaccess.
> I
> did open a new window to do the insert.
>
> Did i miss anything on my procedure? I can't use 11.5 version.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0ce0eea8480c6a0477ac89b3
Not without default role support, no. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Nov 5, 2009 at 10:08 PM, JACK PAPA <informix2009@gmail.com> wrote: > Hi Art, > > after removing the "database storedb", i was able to insert records into > tab1. > However, if i close the window and executing again (without the "set role > role_write"), i wasnt able to insert the records. > > Is there any way my i can insert without re-executing the "set role > command"? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747b104d72ca80477ac8be7
DEFAULT ROLE was introduced in IDS v10.00.
If I'm not mistaken we're discussing about IDS 9.3... so no default role...
Regards.
On Fri, Nov 6, 2009 at 4:47 AM, Art Kagel <art.kagel@gmail.com> wrote:
> Make the sequence:
>
> database storedb;>
> set role role_write;
>
> insert into tab1 values ('00001',"One");
> insert into tab1 values ('00002',"Two");
> insert into tab1 values ('00003',"Three");
> insert into tab1 values ('00004',"Four");
> insert into tab1 values ('00005',"Five");>
> The set role is ONLY effective for the current database session, it does
> NOT
> persist. You would have to use a default role to make it permanent and I'm
> not sure if your release of IDS supports default roles (try it and see -
> see
> my earlier post about that).
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity
> with which I am affiliated nor those of the entities themselves.
>
> On Thu, Nov 5, 2009 at 9:52 PM, JACK PAPA <informix2009@gmail.com> wrote:
>
> > Hi Art,
> >
> > this is my insert statement:
> >
> > database storedb;> >
> > insert into tab1 values ('00001',"One");
> > insert into tab1 values ('00002',"Two");
> > insert into tab1 values ('00003',"Three");
> > insert into tab1 values ('00004',"Four");
> > insert into tab1 values ('00005',"Five");> >
> > i'm trying to insert into tab1 using user1 on dbaccess - load.sql. I did
> > execute "SET ROLE role_write" using user1 prior to executing the
> dbaccess.
> > I
> > did open a new window to do the insert.
> >
> > Did i miss anything on my procedure? I can't use 11.5 version.
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --000e0ce0eea8480c6a0477ac89b3
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--000e0cd58f126810310477bbf297