Help for bug in Informix 5.0
I am using Informix version 5.00.UC2 on a RS6000. I am constrained to
using a 5.0 version because I need to use Recognition Int'l Corporation's
Plexus optical StorageManager software. The bug is that I cannot use
the USER function in a select statement from within a create view
statement. I am using DBACCESS and the sql code is at the end of this
message. The goal is to restrict users on what they can access
in table tbdddistribsum based upon who they are. This works fine on
an HP using version 5.03.UC4 of Informix. I've tried lots of
work-arounds and can't find a fix. Any help would be appreciated!
Thanks in advance,
Karla Nohalty
klnohalty@tasc.com
**SQL CODE:
create table tbdddistribsum
( dcreate_date datetime year to second not null,
psdivision char(2) not null,
psunit char(3) not null,
psdepartment char(2) not null,
psdistrict char(3) not null,
uidndoc integer not null
)extent size 64 next size 64
lock mode row;
create index ixtbdddistribsum_1 on tbdddistribsum(psdivision);
create index ixtbdddistribsum_2 on tbdddistribsum(psunit);
create index ixtbdddistribsum_3 on tbdddistribsum(psdepartment);
create index ixtbdddistribsum_4 on tbdddistribsum(psdistrict);
create index ixtbdddistribsum_5 on tbdddistribsum(uidndoc);
{
*=* Create restricted user list of rights
}
Create table tbaccessrights
(
userid char(20) not null,
psdivision integer,
psunit integer,
psdepartment integer,psdistrict integer
) extent size 64 next size 64
lock mode row;
create index ixtbaccessrights_1 on tbaccessrights(userid);
create index ixtbaccessrights_2 on tbaccessrights(psdivision);
create index ixtbaccessrights_3 on tbaccessrights(psunit);
create index ixtbaccessrights_4 on tbaccessrights(psdepartment);
create index ixtbaccessrights_5 on tbaccessrights(psdistrict);
{
*=* Create Restricted Views
}
Create view vdddistribsum
(psdivision, psunit, psdepartment,psdistrict,uidndoc,dcreate_date) as select
dd.psdivision, dd.psunit, dd.psdepartment, dd.psdistrict,
uidndoc,dcreate_date fromtbdddistribsum dd,tbaccessrights r
where
r.userid=USER AND
((r.psdivision is NULL) OR (r.psdivision = dd.psdivision))
AND ((r.psunit is NULL) OR (r.psunit = dd.psunit))
AND ((r.psdepartment is NULL) OR (r.psdepartment = dd.psdepartment))
AND ((r.psdistrict is NULL) OR (r.psdistrict = dd.psdistrict));