NOTE: The Technical Bulletin in this entry has been revised, and is published as Rev A. 

The results of the recent investigation into the Employee and Security Group structure in AMS360 have been published in Technical Bulletin 13, which is available in the QueryPlus Knowledge Base, and is reproduced below. All of the queries and reports mentioned in the Technical Bulletin are also available in the Knowledge Base.

 QueryPlus Technical Bulletin         

Number:           13                                                                   
Subject:            Security and Authorizations in AMS360 - Rev A
Applicability:   360QP
Revision:         3.2 and above
QP Database:   8/1/2008 or later  

This bulletin describes the structure of the Security Group Authorizations in the AMS360 database, and includes several queries for extracting information from the database.

The hierarchy of the data is as shown below: (refer to the AMS360 interface ‘Security Group Setup’ screen)

AMS360 SCREEN FIELD NAME               AMS360 DATABASE FIELD NAME

            Employee                                                        Employee

                        Security Group                                    Authorization Group

                                    Task                                        Authorization Item

                                                Feature                        Authorization Item Group

                                                Group                          Authorization Item Sub-Group

Note: Please be aware that the QueryPlus field names are based on the AMS360 Database field names.

The most important concept to understand is that permissions (the Access drop-box on the Secured Item List screen) are granted at the SECURITY GROUP/TASK level, and that Employees obtain permissions by virtue of their membership in a particular Security Group – there are no permissions granted directly to Employees.

In the Secured Item List, the fields called Feature and Group are attributes of the Task field. Only the Task field is ‘connected’ to the Security Group that the Task belongs to.

All of the fields required for Security Group Authorization queries in QueryPlus are in the Basic Agency Information module. On the Step 1 – General screen, click Basic Agency Information:

Click ‘Fields’ and then choose:

System:           Stand-Alone Tables

SubSystem:     Standard

Component:    Employee

To run a basic query, choose these fields:

Note: the ‘Item Item’ in the last field is not an error. This field name should be read as:

 (Authorization Group)    (Authorization Item)    (Item Permission Text)

In several AMS360 databases, this query returns one completely blank record along with the desired data. This appears to be a quirk of the AMS360 database, so add a filter:

            Authorization Group Description Short --- Is Not Null

This query will typically return thousands of records, so several filtering options are:

            Filter for one Authorization Group Description Short

            Filter for ‘not equals No Access’ (as a QueryPlus user said, ‘I need to see who DOES have access to what – the people who DON’T have access will let me know if they need it!’)

The dataset for this query can be sent to the QueryPlus Report Designer to be turned into a variety of reports. See the QueryPlus Knowledge Base for several templates, including cross-tab reports.

The query detailed above will produce a dataset/report which shows all of the permissions for all of the combinations of Authorization Group/Item/Item Group/Item Sub-Group. In order to show permissions for Employees, a separate query using a different modeling of the fields is required. Use these fields:

Note that ‘Employee’ has been added at the beginning of the field names. It is critical to use these fields rather than the ones shown in the example further up the page, because these fields have been linked to the Employee’s record.

Again, please be aware that this query, if un-filtered, will return a LOT of records. That isn’t necessarily bad, but the query can take a long time to run, and it’s often difficult to deal with a bewildering mass of data. Filter appropriately!

Another simple query idea is to show just the Employee and the Security Groups he/she is a member of:

I did say it was simple! Add 2 filters:

            Employee Full Name is not null

            Employee Authorization Group Description Short is not null

This query can be turned into a report grouped by Group, showing which Employees are members, or even a cross-tab report with Employees as rows and Groups as columns. See the QueryPlus Knowledge Base for templates.

There are other fields in the Authorization tables that are useful in queries, so feel free to experiment.

As always, for any questions, comments, suggestions, report help or anything else, PLEASE contact your QueryPlus Technical Support team, because we are here to help.