Went looking for an answer but not really sure what phrases to look
for. Just decided to post my question.

I have a collection of groups which contain items. I also have a
collection of users which can be assigned permissions to both groups
and individual items. If a user has permission to a group then the user
has that permission to each of the items in the group. I need a query
which will return all the items and permission for a particular user.

Here is the code for creating the tables and populating them.

CREATE TABLE [Account] (
[Name] VARCHAR(10)
)

INSERT INTO [Account] VALUES ('210')
INSERT INTO [Account] VALUES ('928')
INSERT INTO [Account] VALUES ('ABC')


CREATE TABLE [AccountGroup] (
[Name] VARCHAR(10)
)

INSERT INTO [AccountGroup] VALUES ('Group1')
INSERT INTO [AccountGroup] VALUES ('Group2')


CREATE TABLE [AccountGroupMembership] (
[GroupName] VARCHAR(10)
, [AccountName] VARCHAR(10)
)

INSERT INTO [AccountGroupMembership] VALUES ('Group1', '210')
INSERT INTO [AccountGroupMembership] VALUES ('Group1', 'ABC')
INSERT INTO [AccountGroupMembership] VALUES ('Group2', '928')
INSERT INTO [AccountGroupMembership] VALUES ('Group2', 'ABC')


CREATE TABLE [Permission] (
[User] VARCHAR(10)
, [Item] VARCHAR(10)
, [ItemType] VARCHAR(1) -- 'A' for account, 'G' for account group
, [ReadPerm] INT
, [WritePerm] INT
)

INSERT INTO [Permission] VALUES ('john', '210', 'A', 1, 0) -- read
access to 210 account
INSERT INTO [Permission] VALUES ('john', 'Group1', 'G', 1, 1) --
read/write access to Group1 group
INSERT INTO [Permission] VALUES ('mary', '928', 'A', 0, 1) -- write
access to 928 account

The simple query

SELECT * FROM [Permission] WHERE [User] = 'john'

returns

User Item ItemType ReadPerm WritePerm
---------- ---------- -------- ----------- -----------
john 210 A 1 0
john Group1 G 1 1

but what I really want is (notice that Group1 has been replaced with
the two members of Group1)

User Item ReadPerm WritePerm
---------- ---------- ----------- -----------
john 210 1 0
john 210 1 1
john ABC 1 1

(Forget for the moment that 210 is listed twice with different
permissions. I could take the result and do some sort of union to least
(or most) restrictive permissions.)

You have no keys in the tables and cannot ever have them -- too many
NULL-able columns, no DRI, etc. Let's put the accounts and groups into
one table and add some more details:

CREATE TABLE AccountGroups
(acct_group_name CHAR(10) NOT NULL,
acct_name CHAR(10) DEFAULT '*' NOT NULL, *= all accts in group
PRIMARY KEY (acct_id, acct_group_name));

Now set up permissions:

CREATE TABLE Permissions
(user_id VARCHAR(10) NOT NULL
REFERENCES Users(user_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
acct_group_name CHAR(10) NOT NULL, -- compound key
acct_name CHAR(10) NOT NULL,
FOREIGN KEY (acct_id, acct_group_name)
REFERENCES AccountGroups(acct_id, acct_group_name)
ON UPDATE CASCADE
ON DELETE CASCADE,
read_perm INTEGER DEFAULT 1 NOT NULL
CHECK (write_perm IN (0,1)),
write_perm INTEGER DEFAULT 0 NOT NULL
CHECK (write_perm IN (0,1)),
PRIMARY KEY (user_id, acct_id, acct_group_name));

This is untested:

SELECT DISTINCT P1.user_id, P1.acct_group_name, P1.acct_name,
P1.read_perm, P1.write_perm
FROM Permissions AS P1
WHERE (P1.user_id = @my_guy AND acct_name <> '*') -- regular acct
permission
OR (EXISTS -- group level permissions
(SELECT *
FROM Permissions AS P2
WHERE P2.user_id = @my_guy
AND acct_name = '*'));

Thank you for replying. To keep it simple I left out all the
constraints and primary keys.

I do not like the idea of having one table to hold both the accounts
and the account groups. For one thing I use the lists to do other uses
besides permissions. They are also used to populate dropdown lists. (Or
for when I just need a list of all the known accounts). You also have
the problem of defining a group that does not (at this point in time)
contain any accounts. Or how about an account that doe snot belong to a
group.

>From what I can read are you saying that I should have two different
columns in the permissions table? One to represent a group and another
to represent the account. You have them both as not null so how can I
assign permissions to a single account (that may or may not be part of
any particular group)? When I assign permissions to a group I would
just want to specify the group and any of the accounts in the group.
But again, having both as not null would limit me from doing that.

On 24 Mar 2005 07:27:48 -0800, JayCallas@hotmail.com wrote:

(snip)
>CREATE TABLE [Permission] (
> [User] VARCHAR(10)
> , [Item] VARCHAR(10)
> , [ItemType] VARCHAR(1) -- 'A' for account, 'G' for account group
> , [ReadPerm] INT
> , [WritePerm] INT
>)

Hi Jay,

You're needlessly complicating your design here. You use two columns to
store a relation to either Account or AccountGroup, but in such a way
that you can never use a foreign key constraint to enforce the
relationship. Why not use two nullable tables, like below:

CREATE TABLE Permission (
"User" VARCHAR(10) NOT NULL
, Account VARCHAR(10)
, AccountGroup VARCHAR(10)
, ReadPerm INT NOT NULL
, WritePerm INT NOT NULL
-- , PRIMARY KEY (???)
, CHECK ((Account IS NULL AND AccountGroup IS NOT NULL)
OR (Account IS NOT NULL AND AccountGroup IS NULL))
, FOREIGN KEY (Account) REFERENCES Account(Name)
, FOREIGN KEY (AccountGroup) REFERENCES AccountGroup(Name)
)

With this table design, your sample data has to be inserted as

INSERT INTO [Permission] VALUES ('john', '210', null, 1, 0) -- read
access to 210 account
INSERT INTO [Permission] VALUES ('john', null, 'Group1', 1, 1) --
read/write access to Group1 group
INSERT INTO [Permission] VALUES ('mary', '928', null, 0, 1) -- write
access to 928 account

And your query will look like this:

SELECT "User", Item,
MAX(ReadPerm) AS ReadPerm,
MAX(WritePerm) AS WritePerm
FROM (SELECT p."User", p.Account AS Item,
p.ReadPerm, p.WritePerm
FROM Permission AS p
WHERE p.Account IS NOT NULL
AND p."User" = 'john'
UNION ALL
SELECT p."User", a.AccountName AS Item,
p.ReadPerm, p.WritePerm
FROM Permission AS p
INNER JOIN AccountGroupMembership AS a
ON a.GroupName = p.AccountGroup
WHERE p.AccountGroup IS NOT NULL
AND p."User" = 'john') AS x
GROUP BY "User", Item

>(Forget for the moment that 210 is listed twice with different
>permissions. I could take the result and do some sort of union to least
>(or most) restrictive permissions.)

The derived table creates the output you requested, with two rows for
210. The group by and the use of MAX is to combine the permissions;
change MAX to MIN if that is more appropriate in your security model.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

I had to make alot of assumptions about the specs. The first one is
that groups have to be made up of accounts; this is implied by your
original question that expected to drill down to accounts and gave no
hint about what to do with a group that has no accounts. But if you
actually have such a situation, then use a '{{no accounts}}' token or
something as a place holder.

Do not worry about drop down lists and the front end stuff here. Get
the data model right first. You can also use a VIEW to get the names
of the accounts; they do not have to be in a base table. You are
thinking like a procedural programmer whose language mixes data and
application code. Think SQL and not files, not screens.

By putting the hierarchy in one table, you can also add constraints to
prevent conflicting permissions on accounts. Right now, your own
example shows that you lack data integrity thanks to this design =
('John', 210, 1, 1) versus ('John', 210, 1, 0).