GP Security: Identifying Security Roles, Tasks, & Operations

A common request we receive is: "We want to tie down security of our users but have no clue as to who has access to what".  To be frank, the reporting for this in GP is not exactly easy to come by.  While there are a number of ISVs available that will provide you with tools and analytics for this information, we have a quick fan-favorite solution via SQL to gather this data.

This information can also be found on the Microsoft Developer Blog here.  

First thing's first, in order to get the following script to populate data, the Clear Data Procedure on the Security Resource Descriptions table within GP.  This can be done from Microsoft Dynamics GP>>Maintenance>>Clear Data

Select 'Display' in the toolbar change to 'Physical', Series = 'System' & Table = Security Resource Descriptions.

After inserting the table and running the clear data procedure, the SQL query will now populate the users>>roles>>tasks>>operations which can be used as a starting point to identify and cleanup security/internal control concerns.

SELECT DISTINCT

    S.USERID UserID, S.CMPANYID CompanyID,

    C.CMPNYNAM CompanyName, S.SecurityRoleID,

    coalesce(T.SECURITYTASKID,'') SecurityTaskID,

    coalesce(TM.SECURITYTASKNAME,'') SecurityTaskName,

    coalesce(TM.SECURITYTASKDESC,'') SecurityTaskDescription,

    coalesce(R.DICTID,'') DictionaryID,

    coalesce(R.PRODNAME,'') ProductName,

    coalesce(R.TYPESTR,'') ResourceType,

    coalesce(R.DSPLNAME,'') ResourceDisplayName,

    coalesce(R.RESTECHNAME,'') ResourceTechnicalName,

    coalesce(R.Series_Name,'') ResourceSeries

FROM    SY10500 S   -- security assignment user role

LEFT OUTER JOIN

    SY01500 C   -- company master

    ON S.CMPANYID = C.CMPANYID

LEFT OUTER JOIN

    SY10600 T  -- tasks in roles

    ON S.SECURITYROLEID = T.SECURITYROLEID 

LEFT OUTER JOIN

    SY09000 TM  -- tasks master

    ON T.SECURITYTASKID = TM.SECURITYTASKID 

LEFT OUTER JOIN

    SY10700 O  -- operations in tasks

    ON T.SECURITYTASKID = O.SECURITYTASKID 

LEFT OUTER JOIN

    SY09400 R  -- resource descriptions 

    ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE

    AND O.SECURITYID = R.SECURITYID

Without the Clear Data procedure ahead of time, the resource descriptions table would be empty and provide no detail on the security operations.

Security is never fun and sometimes hard to grasp your arms around.  We hope this report helps.  Please leave us any questions or feedback below & be sure to our out website @ ALaCarteGP.com.

Until Next Time!