Let’s say I am working on an Accounts Payable application.There are Vendors, Invoices, and AP clerks.I want to be able to assign a clerk a group of invoices belonging to a set of Vendors. Each vendor belongs to one of the following groups: Utilities, Raw Materials, and Finished Goods.
A clerk can be assigned zero, one, or more groups. Generally each clerk works one group, but sometimes when someone is out sick or when someone leaves we would want to be able assign the remaining clerk(s) that other group(s).Because a vendor belongs to a group their invoices, by extension, also belong to a group. It is those vendors and their invoices that the AP clerk should be able to see. Thus it is by Vendor Group / AP Clerk that I want to filter records. The Dynamic User Level Security only permits filtering based on a) membership in a Security Group (different from a Vendor Group) and b) user id. In my case all the AP clerks would belong to the same Security Group ~ they are all performing the same job and therefore need the same access. The difference is in their Vendor Group assignment. This should be something that is dynamic. An AP Manager should be able to create as many or as few groups as they want and assign as many or as few vendors to those groups and in turn assign the AP clerks to one or more of the groups and they should be able to overlap without requiring the AP Manager to deal with the PHPMaker Security Model.Does this help or in any way make sense?Thanks for your time and patience.
I think that would be better if you post your tables schema (CREATE TABLE …) including some records inside of each table (INSERT INTO …), so others could comprehend the subject more clearly.
This is a very normalized database so supplying even just a small part of the DDL would be more work than what I did here.
The database is PostgreSQL.
All the id’s are integers. All the codes (cd) are varchar(4). DT stands for date.
There are a lot of attributes missing and I have simplified some of the relationships in order to cut down on some of the “noise”.
The Vendor Group Name has an alternate key (AK) that forces uniqueness.Each Vendor belongs to one and only one Vendor Group.
An Employee is associated one or more Vendor Groups.
Based on the Vendor Group the Employee should only see Invoices for the Vendor Group(s) that they have been assigned to.If this is not sufficient to communicate what I am trying to do please let me know and I will take a stab at the DDL.Thanks.
Since many of us using MySQL database, could you please create it in MySQL database, and then you may post the tables schema including some records inside so that we could simulate straightforward?
Here is the DDL and INSERTS. My data modeling tool will convert between various DBMS platforms, but sometimes it does not get the conversion 100% correct…having said that this is pretty straight forward so I think it is pretty close.
Here is the DDL…and remember: you ask for it. 
CREATE TABLE EMPL
(
EMPL_PARTY_ROLE_ID INTEGER NOT NULL,
FRST_NM VARCHAR(50) NULL,
MIDDLE_NM VARCHAR(50) NULL,
LAST_NM VARCHAR(50) NULL,
BIRTH_DT DATE NULL,
DEATH_DT DATE NULL,
SEPERTN_DT DATE NULL,
STRT_DT DATE NULL
);
CREATE UNIQUE INDEX XPKEMPLOYEE ON EMPL
(
EMPL_PARTY_ROLE_ID ASC
);
ALTER TABLE EMPL
ADD PRIMARY KEY (EMPL_PARTY_ROLE_ID);
CREATE INDEX XIF1EMPLOYEE ON EMPL
(
EMPL_PARTY_ROLE_ID ASC
);
CREATE TABLE EMPL_VNDR_GRP
(
VNDR_GRP_CD VARCHAR(4) NOT NULL,
EMPL_PARTY_ROLE_ID INTEGER NOT NULL,
STRT_DT DATE NULL,
END_DT DATE NULL,
EMPL_VNDR_GRP_ID INTEGER NOT NULL
);
CREATE UNIQUE INDEX XPKEMPLOYEE_VENDOR_GROUP ON EMPL_VNDR_GRP
(
EMPL_VNDR_GRP_ID ASC
);
ALTER TABLE EMPL_VNDR_GRP
ADD PRIMARY KEY (EMPL_VNDR_GRP_ID);
CREATE INDEX XIF1EMPLOYEE_VENDOR_GROUP ON EMPL_VNDR_GRP
(
VNDR_GRP_CD ASC
);
CREATE INDEX XIF2EMPLOYEE_VENDOR_GROUP ON EMPL_VNDR_GRP
(
EMPL_PARTY_ROLE_ID ASC
);
CREATE TABLE INVCE
(
INVCE_ID INTEGER NOT NULL,
ORD_ID INTEGER NULL,
INVCE_DT DATE NULL,
RECVD_DT DATE NULL,
VNDR_PARTY_ROLE_ID INTEGER NOT NULL,
SHPG_AM NUMERIC(12,2) NULL
);
CREATE UNIQUE INDEX XPKINVOICE ON INVCE
(
INVCE_ID ASC
);
ALTER TABLE INVCE
ADD PRIMARY KEY (INVCE_ID);
CREATE INDEX XIF1INVOICE ON INVCE
(
ORD_ID ASC
);
CREATE INDEX XIF2INVOICE ON INVCE
(
VNDR_PARTY_ROLE_ID ASC
);
CREATE TABLE INVCE_ITM
(
ITM_ID INTEGER NULL,
INVCE_ID INTEGER NOT NULL,
INVCE_ITM_ID INTEGER NOT NULL,
ORD_ITM_ID INTEGER NULL,
ITM_INVCE_QY_CT INTEGER NULL,
ITM_INVCE_AM NUMERIC(12,2) NULL
);
CREATE UNIQUE INDEX XPKINVOICE_ITEM ON INVCE_ITM
(
INVCE_ITM_ID ASC
);
ALTER TABLE INVCE_ITM
ADD PRIMARY KEY (INVCE_ITM_ID);
CREATE INDEX XIF1INVOICE_ITEM ON INVCE_ITM
(
ITM_ID ASC
);
CREATE INDEX XIF2INVOICE_ITEM ON INVCE_ITM
(
INVCE_ID ASC
);
CREATE INDEX XIF3INVOICE_ITEM ON INVCE_ITM
(
ORD_ITM_ID ASC
);
CREATE TABLE VNDR
(
VNDR_PARTY_ROLE_ID INTEGER NOT NULL,
VNDR_NM VARCHAR(50) NULL,
STRT_DT DATE NULL,
END_DT DATE NULL,
VNDR_GRP_CD VARCHAR(4) NOT NULL
);
CREATE UNIQUE INDEX XPKVENDOR ON VNDR
(
VNDR_PARTY_ROLE_ID ASC
);
ALTER TABLE VNDR
ADD PRIMARY KEY (VNDR_PARTY_ROLE_ID);
CREATE INDEX XIF1VENDOR ON VNDR
(
VNDR_PARTY_ROLE_ID ASC
);
CREATE INDEX XIF2VENDOR ON VNDR
(
VNDR_GRP_CD ASC
);
CREATE TABLE VNDR_GRP
(
VNDR_GRP_CD VARCHAR(4) NOT NULL,
VNDR_GRP_NM VARCHAR(50) NULL,
STRT_DT DATE NULL,
END_DT DATE NULL
);
CREATE UNIQUE INDEX XPKVENDOR_GROUP ON VNDR_GRP
(
VNDR_GRP_CD ASC
);
ALTER TABLE VNDR_GRP
ADD PRIMARY KEY (VNDR_GRP_CD);
CREATE UNIQUE INDEX XAK1VENDOR_GROUP ON VNDR_GRP
(
VNDR_GRP_NM ASC
);
ALTER TABLE EMPL_VNDR_GRP
ADD FOREIGN KEY FK_VNDR_GRP_02 (VNDR_GRP_CD) REFERENCES VNDR_GRP (VNDR_GRP_CD);
ALTER TABLE EMPL_VNDR_GRP
ADD FOREIGN KEY FK_EMPL_02 (EMPL_PARTY_ROLE_ID) REFERENCES EMPL (EMPL_PARTY_ROLE_ID);
ALTER TABLE INVCE
ADD FOREIGN KEY FK_VNDR_03 (VNDR_PARTY_ROLE_ID) REFERENCES VNDR (VNDR_PARTY_ROLE_ID);
ALTER TABLE INVCE_ITM
ADD FOREIGN KEY FK_INVCE_01 (INVCE_ID) REFERENCES INVCE (INVCE_ID);
ALTER TABLE VNDR
ADD FOREIGN KEY FK_VNDR_GRP_01 (VNDR_GRP_CD) REFERENCES VNDR_GRP (VNDR_GRP_CD);
INSERT INTO empl (empl_party_role_id, frst_nm, middle_nm, last_nm, birth_dt, death_dt, sepertn_dt, strt_dt) VALUES (1, 'Bobby', 'Joe', 'Smith', '1990-01-15', null, null, '2015-02-16');
INSERT INTO empl (empl_party_role_id, frst_nm, middle_nm, last_nm, birth_dt, death_dt, sepertn_dt, strt_dt) VALUES (2, 'Betty', 'Sue', 'Clark', '1986-02-16', null, null, '2013-03-17');
INSERT INTO empl (empl_party_role_id, frst_nm, middle_nm, last_nm, birth_dt, death_dt, sepertn_dt, strt_dt) VALUES (3, 'Zaphod', '', 'Bebblebrox', '1984-03-17', null, null, '2011-04-18');
INSERT INTO empl (empl_party_role_id, frst_nm, middle_nm, last_nm, birth_dt, death_dt, sepertn_dt, strt_dt) VALUES (4, 'Fjord', '', 'Prefect', '1980-04-18', null, null, '2009-05-19');
INSERT INTO vndr_grp (vndr_grp_cd, vndr_grp_nm, strt_dt, end_dt) VALUES ('U', 'Utilities', '2001-01-01', null);
INSERT INTO vndr_grp (vndr_grp_cd, vndr_grp_nm, strt_dt, end_dt) VALUES ('RAW', 'Raw Materials', '2001-01-01', null);
INSERT INTO vndr_grp (vndr_grp_cd, vndr_grp_nm, strt_dt, end_dt) VALUES ('OFF', 'Office Supplies', '2001-01-01', null);
INSERT INTO vndr_grp (vndr_grp_cd, vndr_grp_nm, strt_dt, end_dt) VALUES ('FG', 'Finished Goods', '2001-01-01', null);
INSERT INTO vndr (vndr_party_role_id, vndr_nm, strt_dt, end_dt, vndr_grp_cd) VALUES (5, 'Acme', '2001-01-02', null, 'FG');
INSERT INTO vndr (vndr_party_role_id, vndr_nm, strt_dt, end_dt, vndr_grp_cd) VALUES (6, 'AT&T', '2001-01-02', null, 'U');
INSERT INTO vndr (vndr_party_role_id, vndr_nm, strt_dt, end_dt, vndr_grp_cd) VALUES (7, 'Electricity R Us', '2001-01-02', null, 'U');
INSERT INTO vndr (vndr_party_role_id, vndr_nm, strt_dt, end_dt, vndr_grp_cd) VALUES (8, 'Office Max', '2001-01-02', null, 'OFF');
INSERT INTO vndr (vndr_party_role_id, vndr_nm, strt_dt, end_dt, vndr_grp_cd) VALUES (9, 'Italian Marble Supply', '2001-01-02', null, 'RAW');
INSERT INTO vndr (vndr_party_role_id, vndr_nm, strt_dt, end_dt, vndr_grp_cd) VALUES (10, 'Hard Concrete Mfg.', '2001-01-02', null, 'RAW');
INSERT INTO vndr (vndr_party_role_id, vndr_nm, strt_dt, end_dt, vndr_grp_cd) VALUES (11, 'Wonka Candy Company', '2001-01-02', null, 'OFF');
INSERT INTO vndr (vndr_party_role_id, vndr_nm, strt_dt, end_dt, vndr_grp_cd) VALUES (12, 'Weyland-Yutani Corporation', '2001-01-02', null, 'FG');
INSERT INTO empl_vndr_grp (vndr_grp_cd, empl_party_role_id, strt_dt, end_dt, empl_vndr_grp_id) VALUES ('U', 1, '2015-02-17', null, 1);
INSERT INTO empl_vndr_grp (vndr_grp_cd, empl_party_role_id, strt_dt, end_dt, empl_vndr_grp_id) VALUES ('RAW', 2, '2013-03-18', null, 2);
INSERT INTO empl_vndr_grp (vndr_grp_cd, empl_party_role_id, strt_dt, end_dt, empl_vndr_grp_id) VALUES ('OFF', 3, '2011-04-19', null, 3);
INSERT INTO empl_vndr_grp (vndr_grp_cd, empl_party_role_id, strt_dt, end_dt, empl_vndr_grp_id) VALUES ('FG', 4, '2009-05-20', null, 4);
INSERT INTO invce (invce_id, ord_id, invce_dt, recvd_dt, vndr_party_role_id, shpg_am) VALUES (1, null, '2021-11-01', '2021-11-03', 5, 17.80);
INSERT INTO invce (invce_id, ord_id, invce_dt, recvd_dt, vndr_party_role_id, shpg_am) VALUES (2, null, '2021-11-02', '2021-11-04', 6, null);
INSERT INTO invce (invce_id, ord_id, invce_dt, recvd_dt, vndr_party_role_id, shpg_am) VALUES (3, null, '2021-11-03', '2021-11-05', 7, null);
INSERT INTO invce (invce_id, ord_id, invce_dt, recvd_dt, vndr_party_role_id, shpg_am) VALUES (4, null, '2021-11-04', '2021-11-06', 8, 27.55);
INSERT INTO invce (invce_id, ord_id, invce_dt, recvd_dt, vndr_party_role_id, shpg_am) VALUES (5, null, '2021-11-05', '2021-11-07', 9, 575.43);
INSERT INTO invce (invce_id, ord_id, invce_dt, recvd_dt, vndr_party_role_id, shpg_am) VALUES (6, null, '2021-11-06', '2021-11-08', 10, 768.10);
INSERT INTO invce (invce_id, ord_id, invce_dt, recvd_dt, vndr_party_role_id, shpg_am) VALUES (7, null, '2021-11-07', '2021-11-09', 11, 12.26);
INSERT INTO invce (invce_id, ord_id, invce_dt, recvd_dt, vndr_party_role_id, shpg_am) VALUES (8, null, '2021-11-08', '2021-11-10', 12, 15659.95);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (100, 1, 1000, null, 25, 26.15);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (255, 1, 1001, null, 10, 13.75);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (9999, 2, 1003, null, 1, 1523.99);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (9998, 3, 1004, null, 1, 2011.87);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (97, 4, 1005, null, 3, 1.75);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (23, 4, 1006, null, 6, 3.45);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (56, 4, 1007, null, 5, 10.05);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (501, 5, 1008, null, 2, 7500.00);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (533, 6, 1009, null, 100, 1203.78);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (303, 7, 1010, null, 55, 36.99);
INSERT INTO invce_itm (itm_id, invce_id, invce_itm_id, ord_itm_id, itm_invce_qy_ct, itm_invce_am) VALUES (10001, 8, 1011, null, 1, 150265.49);
Thanks. We will try to simulate it, and will let you know the results afterwards.
Thanks ~ I would have struggled to do this for anyone else, but you have always proven to be very helpful, thus, the DDL.
Thank you very much.
I have successfully created tables and populated its records.Unfortunately, I have difficulties to comprehend the relationship among tables, since you have not posted the related Fields setup, including the Master/Detail relationship and the corresponding fields of it.
Could you please post what you have done so far in Tables/Fields setup of your PHPMaker v2022 project? Also another customization you have already done, for example, from Security settings or from any Server Events/Client Scripts (if any).
All security is toggled off at the moment.
I have the following relationships defined both in the database and in PHPMaker, RI is checked, but not cascade delete or update:
empl = employee
vndr_grp = vendor group
empl_vndr_grp = employee vendor group (many-to-many resolution table)
vndr = vendor
invce = invoice
invce_itm = invoice item (child table to invce)cd = code
id = identifier
nm = name
dt = date
am = amount
ct = countempl.empl_party_role_id 1-to-many (1:M) empl_vndr_grp.empl_party_role_id
vndr_grp.vndr_grp_cd 1:M empl_vndr_grp.vndr_grp_cd
thus: empl_vndr_grp is a resolution table for the many-to-many relationship between Employee & Vendor Group
vndr.vndr_party_role_id 1:M invce.vndr_party_role_id
invce.invce_id 1:M invce_itm.invce_idempl_vndr_grp has two select fields: empl_party_role_id and vndr_grp_cd
invce is the Master and invce_itm is the Detail
invce has a select field for the vndr_party_role_id
vndr is the Master and invce is the Detail
vndr has a select field for vndr_grp_cdThese are all the settings I have explicitly defined. I am using the latest version of PHPMaker, 2022.8Please do let me know if there is any other relevant information I can get you.
THANKS!!!
Thank you. I have successfully adjusted the setting based on your latest post above.Now let’s move forward to your several points in the first post above.One by one, please. ;-)craigbert wrote:
I want to be able to assign a clerk a group of invoices belonging to a set of Vendors. Each vendor belongs to one of the following groups: Utilities, Raw Materials, and Finished Goods.
A clerk can be assigned zero, one, or more groups. Generally each clerk works one group, but sometimes when someone is out sick or when someone leaves we would want to be able assign the remaining clerk(s) that other group(s).
Have you successfully done this point?
Yes, I can assign an employee to zero, one, or more vendor groups. :-)Thanks.
All right, then.
craigbert wrote:
Because a vendor belongs to a group their invoices, by extension, also belong to a group. It is those vendors and their invoices that the AP clerk should be able to see. Thus it is by Vendor Group / AP Clerk that I want to filter records. >
How about this? Have you done this successfully?
Sorry for the delayed response.
No, that is the bit I can’t sort out.Thanks.
Okay, then.craigbert wrote:
The Dynamic User Level Security only permits filtering based on a) membership in a Security Group (different from a Vendor Group) and b) user id. In my case all the AP clerks would belong to the same Security Group ~ they are all performing the same job and therefore need the same access.
In my humble opinion, if you found the built-in Security cannot fulfil your requirement a hundred percent, then the closest approach is to use Recordset_Selecting server event to restrict access records to end-users. This will secure at record level.
In addition, if you want to restrict access for table level, then you may use UserLevel_Loaded, TablePermission_Loading, and/or TablePermission_Loaded server event.
Thanks.
I don’t believe that the security tables are usable for what I need to do so I will pursue the Recordset_Selecting function.
I looked at the example in the documentation. Are there other examples that I can learn from?
Is it all just PHP or are there restraints / boundaries about what can done within this context?
I know I am showing off my ignorance a bit, but these functions are new to me and my skill with PHP is definitely on the beginner side.Thanks.
craigbert wrote:
I looked at the example in the documentation. Are there other examples that I can learn from?
You may search for it in this forum, there are some example with a real case in there for each server event I mentioned before.craigbert wrote:
Is it all just PHP or are there restraints / boundaries about what can done within this context?
Yes, it is. Since the name is Server Events, then only PHP code should be put in there.It’s very easy actually. When you think the built-in Security cannot fulfil your needs, then you should define which tables that need to be connected to the users table (which is used as the main object for Security).
After defining your requirement, then just write your code in Recordset_Selecting server event in the tables other than users table, which should be connected between user_id field in users table with the related another tables.
craigberti posted some thoughts here, take a look see if that’s what you’re looking for. the nice thing about this is that it keeps the userid security intact, in our case this was great because we had intake admins, that created/edited the core records for a client, – meaning the user assigned to the account really didn’t need to be the owner of those records… so you get the best of both worlds, if you need the user to modify the record, just override the perm…
Hi mobhar and sticcino,Thanks to both of you for your help. mobhar, I ended up using the Record_Selecting function as suggested, but I had to do some SQL gymnastics to get things sorted out there. Still working thru some of the stuff, but I think I am going down the right road.sticcino thanks for you link. I am definitely keeping that one in my back pocket.Sincerely,Craigbert
You may post your code for more discussion.