Re: row level security
mud-flap wrote:
>
> Give each customer their own VIEW, with a WHERE clause that limits them to
> looking at rows that have their particular customer id. Of course you have
> "customize" your SELECT statements, so that they use the right VIEW based on
> the current user - and make sure you give GRANTS at the VIEW level, not the
> TABLE level. This works for us.
>
> jsmith wrote in message <35CA7FF1.1ACC@hotmail.com>...
> >Hi:
> >
> >We have an inventory-database of our customers, and an application that
> >will help them view their inventory. Intially, security was not an
> >issue, and therefore one customer could look up the inventory of
> >another. Now we would like to restrict access to data based on the
> >userid.
> >
> >How can I accomplish row-level security? Unfortunately I do not have a
> >whole lotta flexibility in terms of database design or the front-end.
> >
> >Any information helps. Thanks in advance.
> >
> >John
More efficiently, create a table that contains a mapping from username
(as used to login) to customerid. Create your (one) view using this. The
code would go something like this (keys etc omitted):
CREATE TABLE usermap
(
username char(8),
custid integer
);
CREATE VIEW myview ASSELECT m.* from mytable m
WHERE m.custid = (SELECT u.custid from usermap u WHERE u.username =
USER);
I haven't tested this but you get the idea... The view returns different
rows depending on who you are. You may want the WITH CHECK OPTION. The
method can be adapted to a more complex mapping of usernames and
custids.
This is the only secure way to do it. As mud-flap wrote, grant at the
view level.
--
Peter Lancashire
Information Systems Specialist, Bayer plc
Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
Tel: +44-1635-562258, Fax: +44-1635-562281
---
If all else fails, read the instructions.
All opinions are my own and not those of Bayer plc.
My Internet plumbing does not allow me to mail and post news together.
Sorry.
---
Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/