Query Restrictions - Converting to a single Table

[Joe Wolken]

I am working on an OnDemand  v.10.5 implementation that has over 400 user ids that each have Query Restrictions defined that limit them to accessing between 1 and 100 different Vendor Numbers.   There are about 30 different Application Groups that are indexed and restricted in this way.

The issue is that there is a constant flow of change requests to add or remove Vendor Numbers from different User IDs.  This requires the OnDemand System Administrator to update 30 Application Groups for every change request.

The solution for this is to consolidate all of the Query Restrictions to a generic table or file with two columns, UserID and VendorNumber to contain all of the entries, then update the *PUBLIC Query Restriction to reference this table. as referenced in the IBM link:

https://www.ibm.com/support/pages/content-manager-ondemand-defined-macros-sql

HOWEVER, does anyone have an idea of how to automate the conversion of thousands of query restrictions to a single generic file.  I can use OnDemand Admin client to create a report of App Group Permissions that generates a TXT file with the information, but now to translate that information to a single table??  Manual data entry of SQL Insert Into commands in too much.

Has anyone solved this?  Are there any suggestion on the best way to approach automating this one-time conversion?

Thanks,
Joe

[Justin Derrick]

Doesn't it make more sense to define these as groups, and add and remove users from the groups?  There's no discussion about how Folders fit into this situation - are you accessing AGs directly through the ODWEK API or one of the command line clients?

-JD.