Re: Help with a query
>From: uunet!thalatta.com!bossert (John Bossert)
>Subject: Help with a query
>Date: 20 Mar 93 21:09:43 GMT
>X-Informix-List-Id: <news.2865>
>I'm trying to write a security routine that satisfies the following rules:
>Given the tables:
> user(user_nr)
> report(report_nr)
> user_report(user_nr, report_nr)
>If, for a specific report, rows containing that report_nr appear in
>"user_report", then only allow the corresponding users to run the report.
>Else, allow anyone to run the report.
>Can a single query identify the users eligible to run a particular report?
>(The problem becomes trivial if I use multiple queries - I'm trying to be
>elegant...)
It depends on the context in which you ask the question. The security
routine (probably) does not need to know the list of users which can run
the report, only whether the current can run the report. If so, you could
probably use:
Query Q1:
SELECT user_nr
FROM user_report
WHERE report_nr = $value
AND user_nr = USER
UNION
SELECT USER
FROM user_report
WHERE NOT EXISTS
(SELECT user_nr FROM user_report WHERE report_nr = $value)
In this, I've used $value to denote the report number. How you deal with
it depends on the language you're using. This will return a single row if
the user is allowed to use the report. (The solution assumes that user_nr
is the username or login name as used in the main Infomrix security tables.
If it isn't then (a) why not, and (b) you will have to be more inventive
with the use of a value in place of the keyword USER.)
If, on the other hand, you want to write a report which details which users
can use report N, then life is more difficult, but only marginally so,
because you can use:
Query Q2:
SELECT user_nr
FROM user_report
WHERE report_nr = $value
UNION
SELECT user_nr
FROM user
WHERE NOT EXISTS
(SELECT user_nr FROM user_report WHERE report_nr = $value);
I tested this on the tables:
create table user(user_nr char(8) not null);
insert into user values ("abc");
insert into user values ("bcd");
insert into user values ("cde");
create table user_report(user_nr CHAR(8) not null,report_nr integer not null);
insert into user_report values ("abc", 1);
insert into user_report values ("bcd", 2);
I used literal values in place of USER and $value:
Query USER Value Rows List
Q1 "bbc" 1 0 <none> OK
Q1 "abc" 1 1 "abc" OK
Q1 "abc" 3 1 "abc" OK
Q2 <none> 3 3 "abc", "bcd", "cde" OK
Q2 <none> 1 1 "abc" OK
You might want to be a little more careful in the choice of table for the
second half of Q1 -- it will return one row for each row in the table, but
the duplicates will be eliminated by the UNION phase. A better choice than
user_report would be "FROM Systables WHERE Tabid = 1 AND NOT EXISTS (...)".
Yours,
Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>