User Permission on data level
The poster wanted row-level security in Informix: group A should only see rows of a table where name like 'A%', group B only 'B%'. Suggestions included roles (rejected, since roles grant access to the whole table) and column-level GRANTs (which restrict columns, not rows). The consensus answer was that Informix has no direct row-level privilege: you create views containing only the permitted rows and grant access to the views rather than the base table, e.g. a view filtered with WHERE USER IN (...) AND name LIKE 'A%' ... WITH CHECK OPTION. No reply from the original poster confirming the outcome.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi all, I have two group of informix users and I would like to control the permission of allowing query/insert and update on selected data of a table. eg. Group A user is only allowed to query Table A where name like 'A%' Group B user is only allowed to query Table A where name like 'B%' Kindly advise how do i achieve the above ? Thanks, Lee
Create Role A and give permission for Table 'A%' to Role A. Assign Role A to users in Group A. Thank you, Kannan Thirugnanam -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of KWONG HONG LEE Sent: Saturday, May 28, 2005 12:02 PM To: ids@iiug.org Subject: User Permission on data level [5047] hi all, I have two group of informix users and I would like to control the permission of allowing query/insert and update on selected data of a table. eg. Group A user is only allowed to query Table A where name like 'A%' Group B user is only allowed to query Table A where name like 'B%' Kindly advise how do i achieve the above ? Thanks, Lee
I don't want to grant full select access of the entired table to Group A. Group A is only allowed to query limited portion of the data eg. only allow Group A to select name from tableA where name like 'john%'
Hi, You'll need to create two views and grant the appropriate privileges to each one. There's no way to do it directly with the underlying table. Cheers, Dick Snoke Consulting IT Specialist dsnoke@us.ibm.com 404 487 1595 "KWONG HONG LEE" <kwonghong@yahoo.com> Sent by: forum.subscriber@iiug.org 05/31/2005 03:11 AM To ids@iiug.org cc Subject Re: RE: User Permission on data level [5059] I don't want to grant full select access of the entired table to Group A. Group A is only allowed to query limited portion of the data eg. only allow Group A to select name from tableA where name like 'john%'
Use column level priveleges on the columns that are to be restricted.
REVOKE all ON tablename FROM public, <group A members | group A role>;
GRANT SELECT (name) ON tablename TO <group A members | group A role>;
Art S. Kagel
----- Original Message -----
From: Kwong Hong Lee <kwonghong@yahoo.com>
At: 5/31 4:48
I don't want to grant full select access of the entired table to Group A. Group
A is only allowed to query limited portion of the data eg. only allow Group A
to
select name from tableA where name like 'john%'
Try creating a VIEW which only contains the data they are allowed to select and only grant permission to the view, not the table. -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of KWONG HONG LEE Sent: Tuesday, May 31, 2005 12:11 AM To: ids@iiug.org Subject: Re: RE: User Permission on data level [5059] I don't want to grant full select access of the entired table to Group A. Group A is only allowed to query limited portion of the data eg. only allow Group A to select name from tableA where name like 'john%'
I don't
think he means that - I think he wants a subset of the rows - not the
columns...
I'd look at using a view (something like) :
CREATE VIEW somename AS
SELECT * FROM sometable
WHERE
(USER in ('user1','user2') and name like 'A%')
OR
(USER in ('user3','user4') and name like 'B%')
WITH CHECK OPTION
On Tuesday 31 May 2005 14:50, ART KAGEL, .... wrote:
> Use column level priveleges on the columns that are to be restricted.
>
> REVOKE all ON tablename FROM public, <group A members | group A role>;
> GRANT SELECT (name) ON tablename TO <group A members | group A role>;>
> Art S. Kagel
>
> ----- Original Message -----
> From: Kwong Hong Lee <kwonghong@yahoo.com>
> At: 5/31 4:48
>
> I don't want to grant full select access of the entired table to Group A.
> Group A is only allowed to query limited portion of the data eg. only allow
> Group A to select name from tableA where name like 'john%'