D365FO - SQL get security details (such as Security roles, duties and privileges)

 SQL Query to get security details (such as Security roles, duties and privileges)

 Today, I will sharing out the list of queries which can used to get the security details.

It will cover following combinations of data.

Security roles

Security roles to duties

Security roles with privileges

Security role and duty combination along with privileges

Following list of tables are used in it.

SECURITYROLES

SECURITYOBJECTCHILDREREFERENCES

SECURITYDUTY

SECURITYPRIVILEGE

-- Get the list  of all security roles 

Select Name as SecurityRoleName FROM SecurityRole;

-- Get the list of all security roles to duties

SELECT T2.Name as SecurityRole, T3.NAME as Duty 

FROM SECURITYOBJECTCHILDREREFERENCES T1 

JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 

JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER

WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 ;

-- Get the ;ist of all security roles with privileges 

SELECT T2.Name as SecurityRole, T3.NAME as Privileges

FROM SECURITYOBJECTCHILDREREFERENCES T1 

JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 

JOIN SECURITYPRIVILEGE T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER

WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 2 ;

-- Get the list of all role-duty combination with privilege 

SELECT T2.Name as SecurityRole, T2.AOTNAME as RoleSystemName,  T3.NAME AS Duty, T3.IDENTIFIER as DutySystemName, T5.NAME as Privilege, T5.IDENTIFIER as PrivilegeSystemName

FROM SECURITYOBJECTCHILDREREFERENCES T1 

JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 

JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER

JOIN SECURITYOBJECTCHILDREREFERENCES T4 on T4.IDENTIFIER = T3.IDENTIFIER

JOIN SECURITYPRIVILEGE T5 on T4.CHILDIDENTIFIER = T5.IDENTIFIER

WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 

AND T4.OBJECTTYPE = 1 AND T4.CHILDOBJECTTYPE = 2;

Ref

D365 FO - X++ Join multiples tables with same table using query build data source

Join multiples tables with same table using query build data source

Today, I will be discussing about one of the common issue related to query creation using  query build data source. 

Let's suppose we have following tables.

 - Leave Assignment  table - (Base Table) - Namely Parent for demo purpose

 - HcmEmployment table   - Namely Child 1 for demo purpose

 - Leave Plan table    - Namely Child 2 for demo purpose

We want to link base table with both hcm employment (child1) and leave plan table (child2)  using query build data source. Below is the code used for performing this operation.

public QueryRun   fetchMultipleTableQueryDemo()

{

    QueryBuildDataSource   hcmEmploymentds;

    QueryBuildDataSource   qbds;

    Query query = new Query();

       

    // Adding parent  as a datasource

    QueryBuildDataSource qbds = query.addDataSource(tableNum(Parent));

    // Adding child 1 as a datasource

    hcmEmploymentds= qbds.addDataSource(tableNum(Child1));

    hcmEmploymentds.joinMode(JoinMode::InnerJoin);

    hcmEmploymentds.addLink(fieldNum(Parent, Worker),fieldNum(Child1, Worker));

    hcmEmploymentds.fetchMode(QueryFetchMode::One2One);

   // Adding child 2 as a datasource

   qbds = qbds.addDataSource(tableNum(Child2));

   qbds.joinMode(JoinMode::InnerJoin);

   qbds.addLink(fieldNum(Parent, LeaveDetail), fieldNum(Child1, RecId));

   qbds.fetchmode(QueryFetchMode::One2One);

       

    return new QueryRun(query);

} 

Few of the common methods related to query and dates.

Date  related methods 

 -  Year(date  _date) : Method will be used to get year from specified date.

-   mkdate(int _day, int _month, int _year) : Method will be used to make and return a new date

-   mthOfYr(date _date) : Method will be used to get month from the the specified date parameter.

-   dayOfMth(date _date) : Method will be used to get day of the month from the specified date parameter.

Query  related methods 

QueryBuildRange         startAndEndDateRange ;

startAndEndDateRange = this.query().dataSourceTable(tableNum(EmplSickLeaveEncashment)).addRange(FieldNum(EmplSickLeaveEncashment, NextEncashDate));

startAndEndDateRange.value(SysQueryRangeUtil::dateRange(startDate,toDate));

startAndEndDateRange.status(RangeStatus::Hidden);

Here for adding date range we can using dateRange method from SysQueryRangeUtil class.

In order to hide range we can set its status to Hidden by using method of status.

Ref

Ref2

Power BI with D365FO

 Power BI with D365FO

D365 FO - Extensible Data Security (XDS)

 D365 FO - Extensible Data Security (XDS)

https://www.youtube.com/watch?v=rS1TKzyyV1E