Here is the scenario:
A user needs to create a query, but doesn't have security access to view a record. The user asks you for help.
Before running or writing queries, a user must have access to records through Query Security Trees. We assign records to trees, trees to permission lists, permission lists to roles, and roles to users. The solution is trivial: grant security access through a security tree. The difficult part is deciding which tree and which permission list. When I receive a request like this, I have two groups of questions:
- Is there already a query security tree that grants access to the target record? If so, which one and which permission lists? Would any of those permission lists be appropriate for this user?
- Is there already a query security tree available to this user that would be a good fit for this record?
Here are some SQL statements I created to help answer these questions.
Find existing trees that include the target record:
Finding Permission Lists that grant access to a specific record is more challenging. Locating permission lists that contain a tree that contains a record is trivial. Narrowing that list based on Access Group is more challenging because Access Groups represent node hierarchies and therefore require recursive logic:
Do you have SQL statements and tricks to help you manage Query Security? If so, share them in the comments!
At JSMpros, we teach PeopleTools tips like this every week. Check out our website to see what we are offering next!
