September 21, 2009 at 8:16 am
We have a table that stores the user permissions to specific programs, USER_PGM_AUTHORITY. The table holds two items either R (Read-only) or N (No Access). If neither of these are in the table then the user has write access. We are trying to build a script that will list all users who have write access to a specific program (INV_OPT). This is challenging due to the fact that there is not a placeholder in the table when a user has write access. Any ideas how we can accomplish this?
September 21, 2009 at 8:26 am
Select * from USER_PGM_AUTHORITY where ACCESS is null and Program = 'INV_OPT'
If you can post the table and some sample data I can answer your question more specifically.
September 21, 2009 at 8:31 am
admin is on the right track;
i think you might need to start with a base table of all users, then join it to this table to generate your list;
something like:
Select USERNAME,* FROM
MASTERLISTOFUSERS
LEFT OUTER JOIN USER_PGM_AUTHORITY
ON MASTERLISTOFUSERS.USERID = USER_PGM_AUTHORITY.USERID
where USER_PGM_AUTHORITY.ACCESS is null and USER_PGM_AUTHORITY.Program = 'INV_OPT'
Lowell
September 21, 2009 at 8:41 am
If a user has permission to a program there is not a null value. There is no placeholder whatsoever. So, I can not use "permission is null" in a script. Here is the table layout:
[dbo].[USER_PGM_AUTHORITY](
[ROWID] [int] IDENTITY(1,1) NOT NULL,
[USER_ID] [varchar](20) NOT NULL,
[PROGRAM_ID] [varchar](8) NOT NULL,
[PROGRAM_COMPONENT] [varchar](8) NOT NULL,
[PERMISSION] [char](1) NOT NULL DEFAULT (''),
[PROFILE_STRING] [varchar](80) NULL,
CONSTRAINT [PK_USER_PGM_AUTHORITY] PRIMARY KEY CLUSTERED
September 21, 2009 at 8:56 am
bpowers (9/21/2009)
If a user has permission to a program there is not a null value. There is no placeholder whatsoever. So, I can not use "permission is null" in a script. Here is the table layout:
yes exactly, so your biz rule is "everyone has access UNLESS there is an entry in this table"
that's why you have to start with a master list of users, and join for "permission is null" ; really
WHERE [PERMISSION] IS NULL or [PERMISSION] ='' ,
either way, that field would be null if there is no row in the table when using a left outer join.
does that help?
Lowell
September 21, 2009 at 8:59 am
Here is the script I built on your suggestion. It returns no records.
SELECT DISTINCT NAME
FROM APPLICATION_USER
LEFT JOIN USER_PGM_AUTHORITY ON APPLICATION_USER.NAME = USER_PGM_AUTHORITY.USER_ID
WHERE PERMISSION IS NULL AND PROGRAM_ID = 'VMINVENT'
September 21, 2009 at 9:04 am
ahh... the WHERE statements are turning the left outer join into an inner join.
try this instead:
SELECT DISTINCT NAME
FROM APPLICATION_USER
LEFT JOIN USER_PGM_AUTHORITY
ON APPLICATION_USER.NAME = USER_PGM_AUTHORITY.USER_ID
AND USER_PGM_AUTHORITY.PROGRAM_ID = 'VMINVENT'
WHERE USER_PGM_AUTHORITY.PERMISSION IS NULL
Lowell
September 21, 2009 at 9:08 am
Tried flip flopping the tables but it still does not return any records. I believe it is due to the fact that I am testing for a program id of "VMINVENT". This too will not be stored in the table if a user has write acces. Something that seems so simple has been a pain to get to.
SELECT DISTINCT USER_ID
FROM USER_PGM_AUTHORITY
LEFT JOIN APPLICATION_USER ON USER_PGM_AUTHORITY.USER_ID = APPLICATION_USER.NAME
WHERE PERMISSION IS NULL AND PROGRAM_ID = 'VMINVENT'
September 21, 2009 at 9:09 am
That worked! How was it getting turned into an inner join?
September 21, 2009 at 9:11 am
Is what you did. Thanks a million for the help. It is greatly appreciated.
September 21, 2009 at 9:16 am
it's one of those "GOTCHAS" with the left outer join;
a left outer join should test for a column=null or not null on the joined table;
if it tests for anything else on the joined table in the WHERE statement, it changes it to an inner join..
AND PROGRAM_ID = 'VMINVENT' was what killed us in our first version:
it basically took the result set you would have seen, a list of names, and joined NULL = 'VMINVENT', which killed our result set.
hope that helps!
Lowell
September 21, 2009 at 9:19 am
Thanks for the lesson. It is appreciated.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply