Re: Cannot find the correct SQL statement

From: Alex Filonov <afilonov_at_pro-ns.net>
Date: Mon, 08 Jan 2001 17:39:03 GMT
Message-ID: <93ctvl$t76$1@nnrp1.deja.com>

In article <936jfd$roj$03$1_at_news.t-online.com>,   "Michael G. Schneider" <mgs_at_mgs-software.de> wrote:
> Suppose we have the following tables
>
> T_Item
> T_User
> T_Group
> T_Membership
> T_Security
>
> T_Item, T_User and T_Group are basic tables. T_Membership defines,
 which
> users are members of which groups. A user can be the member of several
> groups. T_Security defines which group may access a certain item.
>
> Now I would like to have the cartesian product of T_Item and T_User,
 telling
> me whether the user has access to the item or not. If there are
 several
> definitions for one pair <item,user> the definition "is allowed"
 should win.
>
> Can this be done with SQL?
>
> Michael G. Schneider
> mailto:mgs_at_mgs-software.de
>
> CREATE TABLE T_Item (i CHAR(10));
> CREATE TABLE T_User (u CHAR(10));
> CREATE TABLE T_Group (g CHAR(10));
> CREATE TABLE T_Membership (mg CHAR(10), mu CHAR(10));
> CREATE TABLE T_Security (si CHAR(10), sg CHAR(10), sa NUMBER(1,0));
>
> INSERT INTO T_Item VALUES('I1');
> INSERT INTO T_Item VALUES('I2');
> INSERT INTO T_Item VALUES('I3');
>
> INSERT INTO T_User VALUES('U1');
> INSERT INTO T_User VALUES('U2');
> INSERT INTO T_User VALUES('U3');
>
> INSERT INTO T_Group VALUES('G1');
> INSERT INTO T_Group VALUES('G2');
> INSERT INTO T_Group VALUES('G3');
>
> INSERT INTO T_Membership VALUES('G1','U1');
> INSERT INTO T_Membership VALUES('G1','U2');
> INSERT INTO T_Membership VALUES('G2','U2');
> INSERT INTO T_Membership VALUES('G3','U2');
> INSERT INTO T_Membership VALUES('G3','U3');
>
> INSERT INTO T_Security VALUES('I1','G1',0);
> INSERT INTO T_Security VALUES('I1','G2',1);
> INSERT INTO T_Security VALUES('I2','G1',1);
> INSERT INTO T_Security VALUES('I2','G3',1);
> INSERT INTO T_Security VALUES('I3','G1',0);
> INSERT INTO T_Security VALUES('I3','G3',1);
>
> SELECT * FROM T_Item;
> SELECT * FROM T_User;
> SELECT * FROM T_Group;
> SELECT * FROM T_Membership;
> SELECT * FROM T_Security;
>
> T_Item
> ------------------------
> I1
> I2
> I3
>
> T_User
> ------------------------
> U1
> U2
> U3
>
> T_Group
> ------------------------
> G1
> G2
> G3
>
> T_Membership
> ------------------------
> G1 U1
> G1 U2
> G2 U2
> G3 U2
> G3 U3
>
> T_Security
> ------------------------
> I1 G1 0
> I1 G2 1
> I2 G1 1
> I2 G3 1
> I3 G1 0
> I3 G3 1
>
>

select t_user.u, t_item.i,

    decode(max(t_security.sa), 0, 'Not Allowed', 1, 'Allowed') allowed from t_user, t_item, t_membership, t_group, t_security

where t_user.u = t_membership.mu
  and t_group.g = t_membership.mg
  and t_security.si = t_item.i
  and t_security.sg = t_group.g

group by t_user.u, t_item.i

I hope that 0 means not allowed, 1 means allowed.

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 08 2001 - 11:39:03 CST