Hi!

I have a list of objekts

CREATE TABLE TAB_Allyksus (
UID DOM_PK PRIMARY KEY,
Nimi DOM_NIMI UNIQUE,
ExtID DOM_EXTERNALID
);

and I have to limit users access to configurable set of them. So I
have a table

CREATE TABLE SYS_TAP_Users (
UID DOM_PK PRIMARY KEY,
UserName VARCHAR(70) NOT NULL UNIQUE, /* login name */
AYksLPK INTEGER CONSTRAINT CC_STU_AYLPK CHECK((AYksLPK >=
0)AND(AYksLPK < 4))
);

where AYksLPK means:
0 - user doesn't have access to objekts in TAB_Allyksus;
1 - user has access to objects listed in SYS_UsrR_Allyksus table
(see below);
2 - same as 1, I have to have this ID because of other requirements;
3 - user should see all rows in TAB_Allyksus;

CREATE TABLE SYS_UsrR_Allyksus (
UID DOM_PK PRIMARY KEY,
UserID DOM_FK CONSTRAINT FK_URAY_User REFERENCES
SYS_TAP_Users(UID)ON UPDATE CASCADE ON DELETE CASCADE,
Allyksus DOM_FK CONSTRAINT FK_URAY_Yksus REFERENCES
TAB_Allyksus(UID)ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT CU_UsrR_Allyksus UNIQUE(UserID,Allyksus)
);

So in case SYS_TAP_Users.AYksLPK is 1 or 2 SYS_UsrR_Allyksus lists
rows user can see.
Now I want to create a view which shows those rows from TAB_Allyksus
which current user can see. I come up with

CREATE VIEW VIEW_Usr_Allyksus (
UID ,
Nimi ,
ExtID
)AS SELECT
TAY.UID ,
TAY.Nimi ,
TAY.ExtID
FROM TAB_Allyksus TAY
JOIN SYS_TAP_Users STU ON(UPPER(STU.UserName) = UPPER(CURRENT_USER))
WHERE
(STU.AYksLPK = 3)OR
(((STU.AYksLPK = 1)OR(STU.AYksLPK = 2))AND(EXISTS(SELECT 1 FROM
SYS_UsrR_Allyksus WHERE(UserID =
STU.UID)AND(SYS_UsrR_Allyksus.Allyksus = TAY.UID))))
;

but I have feeling that this is not the best way to do it... wht do
you think? any suggestions?


TIA
ain