NetNews Usenet Archive 1993 #3/NN_1993_3.iso/spool/comp/database/sybase/754

Internet Message Format | 1993-01-28 | 1.8 KB

Path: sparky!uunet!think.com!spool.mu.edu!sol.ctr.columbia.edu!destroyer!news.itd.umich.edu!spencer
From: [e-mail] (Spencer W. Thomas)
Newsgroups: comp.databases.sybase
Subject: Restricting access to rows?
Date: 27 Jan 93 15:33:37
Organization: University of Michigan HSITN
Lines: 61
Message-ID: <[e-mail]>
NNTP-Posting-Host: guraldi.itn.med.umich.edu

Is there some easy way to do this?

I want to restrict access to a table according to the value of a
certain field, by user, according to a second "authorization" table.
I.e.,

create table base (
    category    int,    /* The field to restrict access by. */
    data        int
)

create table auth (
    uid        smallint,
    category    int
)

Each user who is allowed to see rows with a given value for 'category'
has an entry in the 'auth' table with his/her uid and the category:

uid    category
----    --------
2    1        User 2 can see categories 1&2
2    2
3    3        User 3 can only see category 3
4    1        User 4 can see categories 1,2&3
4    2
4    3

My thought was to use a view.  Try 1:

create view stuff_1 as
    select * from base where category in
        (select category from auth where uid=user_id())

This works fine for select, but can't be updated because it has an
aggregate (the 'in' in the select clause, I assume).

Try #2:

create view stuff_2 as
    select base.category,data from base,auth where
        base.category=auth.category and auth.uid=user_id()

This works for select, insert, and update, but not for delete.

Try #3:

As user 2:

create view stuff_3 as
    select * from base where category in (1,2)

This works, but ONLY IF user 2 has permissions on the base table.
Clearly, that defeats the whole purpose.

Any ideas?

--
=Spencer W. Thomas         |  Info Tech and Networking, B1911 CFOB, 0704
   "Genome Informatician"    |  Univ of Michigan, Ann Arbor, MI 48109
[e-mail]    |  313-747-2778, FAX [phone]