KB11351: How to implement different security rules for different facts using the user Login System Prompt in MicroStrategy

• Strategy

This article describes how to create different security for different facts in MicroStrategy.

Different Security Rules For Different Facts
A common security requirement is to restrict users' data access based on more than one unrelated attribute. For example, restricting a user to data about a particular store but also restricting the user to data about a particular product.
In such a scenario, the desired behavior is to apply a different security filter for different facts:

(Store = New York) should apply for facts that are keyed by Store but not Item
(Item = Camera) should apply for facts that are keyed by Item but not Store
(Store = New York OR Item = Camera) should apply for facts that are keyed by both Store and Item

Note that the filter condition is different depending on the fact being queried.

Although it is tempting to define a single security filter in the form of (Store = New York OR Item = Camera), this is not an accurate filter for what the user wants to achieve. If this security filter is used with facts that only exist at the Store level, it will return data about all Stores, since the Item = Camera qualification includes data about all Stores. Similarly, if used with facts that only exist at the Item level, it will return data about all Items. There is however a workaround to get the desired behavior.

Using the User Login System Prompt and Logical Views

To address this requirement, use logical views (or database views) to apply a different security rule for each fact table. Each resulting fact view should have a USER column. Build a User attribute that maps to this column in each fact table and define a security filter User = ? so that each query will qualify on User to restrict access to data.

Example

Consider an example based on a hypothetical services organization, such as a law firm or a consulting firm. In this model, there are two dimensions: Employees are consultants who work on projects and Engagements are the projects on which they work. Employees bill their hours to each Engagement. Each manager who uses the system is allowed to see hours billed by the Employees who report to them and hours billed to the Engagements which they manage. Note that a user can see data about other managers' Engagements, but only if it involves that manager's Employees; a user can also see data about other managers' Employees, but only if they are involved with that manager's Engagements.

Tables

The following tables are available to support the analysis.

LU_USERUSERSEC_EMPLOYEESUSEREMPLOYEE_IDSEC_ENGAGEMENTSUSERENGAGEMENT_IDLU_EMPLOYEESEMPLOYEE_IDEMPLOYEE_NAMEHIRE_DATEMANAGER_ID,SALARYLU_ENGAGEMENTSENGAGEMENT_IDENGAGEMENT_NAMESTART_DATEEND_DATEMANAGER_IDBID_DAYSF_HOURS_BILLEDEMPLOYEE_IDENGAGEMENT_IDHOURS_BILLED
LVW_EMPLOYEESUSEREMPLOYEE_IDEMPLOYEE_NAMEHIRE_DATEMANAGER_ID,SALARY

LU_USER contains a distinct list of users who will log into the application. The USER column contains the Strategy user ID for each user. This table will serve as a lookup table for the USER attribute:

The SEC_* tables are security tables that define the Employees and Engagements that each user is allowed to see. These tables will be joined to the lookup tables below to enforce security rules:

LU_EMPLOYEES is a lookup table for all employees and also contains some facts that are specific to Employees, i.e., independent of Engagements:

LU_ENGAGEMENTS is a lookup table for all engagements and also contains some facts that are specific to Engagements, i.e. independent of Employees:

F_HOURS_BILLED is a fact table that contains the hours billed by each Employee on each Engagement.

Expected Behavior

The following security rules should be enforced:

When viewing Employee facts, such as Salary or Days Since Hire, the user should only see data for their Employees.
When viewing Engagement facts, such as Estimated Duration or Actual Duration, the user should only see data for their Engagements.
When viewing Hours Billed, the user should only see hours billed by their Employees or hours billed to one of their Engagements.

Logical Views
Create the following logical views. Alternatively, these tables could also be created as database views using the same SQL syntax:

select S.USER,
L.EMPLOYEE_ID,
L.EMPLOYEE_NAME,
L.HIRE_DATE,
L.MANAGER_ID,
L.SALARY
from LU_EMPLOYEES L
join SEC_EMPLOYEES S
on (L.EMPLOYEE_ID = S.EMPLOYEE_ID

LVW_ENGAGEMENTSUSERENGAGEMENT_IDENGAGEMENT_NAMESTART_DATEEND_DATEMANAGER_IDBID_DAYS

select S.USER,
L.ENGAGEMENT_ID,
L.ENGAGEMENT_NAME,
L.START_DATE,
L.END_DATE,
L.MANAGER_ID,
L.BID_DAYS
from LU_ENGAGEMENTS L
join SEC_ENGAGEMENTS S
on L.ENGAGEMENT_ID = S.ENGAGEMENT_ID

LVW_HOURS_BILLEDUSEREMPLOYEE_IDENGAGEMENT_IDHOURS_BILLED

select U.USER,
F.EMPLOYEE_ID,
F.ENGAGEMENT_ID,
F.HOURS_BILLED
from F_HOURS_BILLED F
join SEC_EMPLOYEES S1
on F.EMPLOYEE_ID = S1.EMPLOYEE_ID
join SEC_ENGAGEMENTS S2
on F.ENGAGEMENT_ID = S2.ENGAGEMENT_ID,
L_USER U
where U.USER = S1.USER
or U.USER = S2.USER

Schema Model

Model the following attributes using Architect:

EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?

Model the following facts using Architect:

EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?

Define the following Metrics:

EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?

Security Filter

Define the following security filter:

EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?

where ? is the built in system prompt that returns the current user's login ID.
SQL Generation
Define a report with Employee, Salary. Only those Employees the user is allowed to see are returned:

with lv0 as
(select S.USER,
L.EMPLOYEE_ID,
L.EMPLOYEE_NAME,
L.HIRE_DATE,
L.MANAGER_ID,
L.SALARY
from LU_EMPLOYEES L
join SEC_EMPLOYEES S
on (L.EMPLOYEE_ID = S.EMPLOYEE_ID)
)select a11.EMPLOYEE_ID EMPLOYEE_ID,
max(a11.EMPLOYEE_NAME) EMPLOYEE_NAME,
sum(a11.SALARY) WJXBFS1
from lv0 a11
where a11.USER = 'Joe'
group by a11.EMPLOYEE_ID
Define a report with Engagement, Duration. Only those Engagements the user is allowed to see are returned:

with lv0 as
(select S.USER,
L.ENGAGEMENT_ID,
L.ENGAGEMENT_NAME,
L.START_DATE,
L.END_DATE,
L.MANAGER_ID,
L.BID_DAYS
from LU_ENGAGEMENTS L
join SEC_ENGAGEMENTS S
on L.ENGAGEMENT_ID = S.ENGAGEMENT_ID
)select a11.ENGAGEMENT_ID ENGAGEMENT_ID,
max(a11.ENGAGEMENT_NAME) ENGAGEMENT_NAME,
sum(DAYS(a11.END_DATE) - DAYS(a11.START_DATE)) WJXBFS1
from lv0 a11
where a11.USER = 'Joe'
group by a11.ENGAGEMENT_ID
Define a report with Employee, Engagement, Hours Billed. Data is returned for the Employees the user is allowed to see and for the Engagements the user is allowed to see:

with lv0 as
(select U.USER,
F.EMPLOYEE_ID,
F.ENGAGEMENT_ID,
F.HOURS_BILLED
from F_HOURS_BILLED F
join SEC_EMPLOYEES S1
on F.EMPLOYEE_ID = S1.EMPLOYEE_ID
join SEC_ENGAGEMENTS S2
on F.ENGAGEMENT_ID = S2.ENGAGEMENT_ID,
L_USER U
where U.USER = S1.USER
or U.USER = S1.USER
)select a11.ENGAGEMENT_ID ENGAGEMENT_ID,
max(a13.ENGAGEMENT_NAME) ENGAGEMENT_NAME,
a11.EMPLOYEE_ID EMPLOYEE_ID,
max(a12.EMPLOYEE_NAME) EMPLOYEE_NAME,
sum(a11.HOURS) WJXBFS1
from lv0 a11
join LU_EMPLOYEES a12
on (a11.EMPLOYEE_ID = a12.EMPLOYEE_ID)
join LU_ENGAGEMENTS a13
on (a11.ENGAGEMENT_ID = a13.ENGAGEMENT_ID)
where a11.USER = 'Joe'
group by a11.ENGAGEMENT_ID,
a11.EMPLOYEE_ID
Conclusions
The example above can be generalized to any scenario in which multiple security filters must apply to different facts, e.g., Stores and Items, or Buying Organization and Selling Organization, etc.

Comment

0 comments

Details

Knowledge Article

Published:

April 11, 2017

Last Updated:

April 11, 2017