Using row restrictions in a large scale environment

[system]

I have a universe with 181 classes, 3040 objects, 46 conditions, 115 tables and 76 alias tables. Each one of my database tables has two common columns: “Entity Id” and “Area Id”. For example, I have users that I want them only to see data that has an Entity Id = “HBUS” and an Area Id = “US”. Then I have another set of users that I only want them to see data with an Entity Id = “HBCA” and an Area ID = “CA”. I know how to use row restrictions in Supervisor and to establish the necessary groups. I have done this already in Supervisor 5.1.6. However, my users are generating complicated SQL with 4 or more where clauses from the row restrictions and their queries are hanging. Please see the enclosed SQL as an example of a query that hangs.

SELECT
OPS905P.CDW_CUST.TOT_RELN_ID,
OPS905P.CDW_CUST.TRC_NME,
OPS905P.CDW_CUST.CUST_NME,
OPS905P.CDW_CUST.CIN_CUST_ID,
OPS905P.CDW_CUST.FNL_CUST_RISK_GR,
OPS905P.CDW_CUST.CTRY_CDE_OF_INC,
OPS905P.CDW_CUST.CTRY_CDE_PRIN_OPER,
OPS905P.CDW_CUST.CC_ID,
OPS905P.CDW_REFCDE_VT_RMGR.REFCDE_DESC,
OPS905P.CDW_CUST.SIC_CDE_1,
OPS905P.CDW_REFCDE_VT_SICD.REFCDE_DESC,
OPS905P.CDW_FCL_STUB.FCL_ID,
OPS905P.CDW_FCL.FCL_RM_CDE,
OPS905P.CDW_FCL.CC_ID,
REFCDE_VT_CCTR_FCL.REFCDE_DESC,
OPS905P.CDW_FCL.FCL_TYP_CDE,
OPS905P.CDW_FCL.FCL_STA_CDE,
OPS905P.CDW_FCL.FCL_GRD,
OPS905P.CDW_FCL.LOSS_GVN_DFLT_FAC,
OPS905P.CDW_FCL.ORIG_APPR_DTE,
OPS905P.CDW_FCL.NEXT_REVW_DTE,
OPS905P.CDW_FCL.FCL_EXPR_DTE,
REFCDE_VT_CCTR_FCL.CCTR_LGL_HIER_CDE,
OPS905P.CDW_CUST.PRNT_PER_LGL_HIER,
OPS905P.CDW_FCL.ULT_RISK_CTRY_CDE,
OPS905P.CDW_CUST_FCL_ASGN.BUS_ROLE_CDE,
REF_CTRY_CUST_INC.REFCDE_DESC,
REF_CTRY_CUST_OPER.REFCDE_DESC,
OPS905P.CDW_CUST.ENTY_ID,
OPS905P.CDW_CUST.AREA_ID,
sum(case when substr(OPS905P.CDW_FCL.FCL_ID, 8,3) = ‘000’ or left(OPS905P.CDW_FCL.FCL_ID,1) between ‘A’ and ‘Z’ then OPS905P.CDW_FCL_BAL.FCL_MAX_EXP / 100 else 0 end)

FROM
OPS905P.CDW_CUST_STUB LEFT OUTER JOIN OPS905P.CDW_CUST ON OPS905P.CDW_CUST_STUB.ENTY_ID=OPS905P.CDW_CUST.ENTY_ID and
OPS905P.CDW_CUST_STUB.AREA_ID=OPS905P.CDW_CUST.AREA_ID and
OPS905P.CDW_CUST_STUB.CUST_ID=OPS905P.CDW_CUST.CUST_ID and
((char(OPS905P.CDW_CUST.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = ‘’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(OPS905P.CDW_CUST.EFF_DTE) and
char(OPS905P.CDW_CUST.EXPR_DTE))
LEFT OUTER JOIN OPS905P.CDW_REFCDE_VT_CTRY REF_CTRY_CUST_INC ON REF_CTRY_CUST_INC.ENTY_ID=OPS905P.CDW_CUST.ENTY_ID and REF_CTRY_CUST_INC.AREA_ID=OPS905P.CDW_CUST.AREA_ID and REF_CTRY_CUST_INC.REFCDE_VAL=OPS905P.CDW_CUST.CTRY_CDE_OF_INC and ((char(REF_CTRY_CUST_INC.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = '’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(REF_CTRY_CUST_INC.EFF_DTE) and
char(REF_CTRY_CUST_INC.EXPR_DTE)) LEFT OUTER JOIN OPS905P.CDW_REFCDE_VT_RMGR ON OPS905P.CDW_REFCDE_VT_RMGR.ENTY_ID=OPS905P.CDW_CUST.ENTY_ID and OPS905P.CDW_REFCDE_VT_RMGR.AREA_ID=OPS905P.CDW_CUST.AREA_ID and OPS905P.CDW_REFCDE_VT_RMGR.REFCDE_VAL=OPS905P.CDW_CUST.RM_CDE and ((char(OPS905P.CDW_REFCDE_VT_RMGR.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = ‘’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(OPS905P.CDW_REFCDE_VT_RMGR.EFF_DTE) and
char(OPS905P.CDW_REFCDE_VT_RMGR.EXPR_DTE)) LEFT OUTER JOIN OPS905P.CDW_REFCDE_VT_CTRY REF_CTRY_CUST_OPER ON REF_CTRY_CUST_OPER.ENTY_ID=OPS905P.CDW_CUST.ENTY_ID and REF_CTRY_CUST_OPER.AREA_ID=OPS905P.CDW_CUST.AREA_ID and REF_CTRY_CUST_OPER.REFCDE_VAL=OPS905P.CDW_CUST.CTRY_CDE_PRIN_OPER and ((char(REF_CTRY_CUST_OPER.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = '’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(REF_CTRY_CUST_OPER.EFF_DTE) and
char(REF_CTRY_CUST_OPER.EXPR_DTE)) LEFT OUTER JOIN OPS905P.CDW_REFCDE_VT_SICD ON OPS905P.CDW_REFCDE_VT_SICD.ENTY_ID=OPS905P.CDW_CUST.ENTY_ID and OPS905P.CDW_REFCDE_VT_SICD.AREA_ID=OPS905P.CDW_CUST.AREA_ID and OPS905P.CDW_REFCDE_VT_SICD.REFCDE_VAL=OPS905P.CDW_CUST.SIC_CDE_1 and ((char(OPS905P.CDW_REFCDE_VT_SICD.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = ‘’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(OPS905P.CDW_REFCDE_VT_SICD.EFF_DTE) and
char(OPS905P.CDW_REFCDE_VT_SICD.EXPR_DTE)),
OPS905P.CDW_CUST_FCL_ASGN,
OPS905P.CDW_FCL_STUB LEFT OUTER JOIN OPS905P.CDW_FCL ON OPS905P.CDW_FCL.ENTY_ID=OPS905P.CDW_FCL_STUB.ENTY_ID and OPS905P.CDW_FCL.AREA_ID=OPS905P.CDW_FCL_STUB.AREA_ID and OPS905P.CDW_FCL.FCL_ID=OPS905P.CDW_FCL_STUB.FCL_ID and ((char(OPS905P.CDW_FCL.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = '’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(OPS905P.CDW_FCL.EFF_DTE) and
char(OPS905P.CDW_FCL.EXPR_DTE))

 LEFT OUTER JOIN OPS905P.CDW_REFCDE_VT_CCTR  REFCDE_VT_CCTR_FCL ON OPS905P.CDW_FCL.ENTY_ID=REFCDE_VT_CCTR_FCL.ENTY_ID and OPS905P.CDW_FCL.AREA_ID=REFCDE_VT_CCTR_FCL.AREA_ID and OPS905P.CDW_FCL.CC_ID=REFCDE_VT_CCTR_FCL.REFCDE_VAL and  ((char(REFCDE_VT_CCTR_FCL.EXPR_DTE) >= CASE WHEN @PROMPT('Enter Date (format YYYY-MM-DD) or * for Current Date','C',,,) = '*'

THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(REFCDE_VT_CCTR_FCL.EFF_DTE) and
char(REFCDE_VT_CCTR_FCL.EXPR_DTE)) LEFT OUTER JOIN OPS905P.CDW_FCL_BAL ON OPS905P.CDW_FCL_BAL.ENTY_ID=OPS905P.CDW_FCL_STUB.ENTY_ID and OPS905P.CDW_FCL_BAL.AREA_ID=OPS905P.CDW_FCL_STUB.AREA_ID and OPS905P.CDW_FCL_BAL.FCL_ID=OPS905P.CDW_FCL_STUB.FCL_ID and ((char(OPS905P.CDW_FCL_BAL.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = ‘’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(OPS905P.CDW_FCL_BAL.EFF_DTE) and
char(OPS905P.CDW_FCL_BAL.EXPR_DTE))
WHERE
( OPS905P.CDW_FCL_STUB.FCL_ID=OPS905P.CDW_CUST_FCL_ASGN.FCL_ID and OPS905P.CDW_FCL_STUB.ENTY_ID=OPS905P.CDW_CUST_FCL_ASGN.FCL_ENTY_ID and OPS905P.CDW_FCL_STUB.AREA_ID=OPS905P.CDW_CUST_FCL_ASGN.FCL_AREA_ID and ((char(OPS905P.CDW_CUST_FCL_ASGN.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = '’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(OPS905P.CDW_CUST_FCL_ASGN.EFF_DTE) and
char(OPS905P.CDW_CUST_FCL_ASGN.EXPR_DTE)) )
AND ( OPS905P.CDW_CUST_STUB.CUST_ID=OPS905P.CDW_CUST_FCL_ASGN.CUST_ID and OPS905P.CDW_CUST_STUB.ENTY_ID=OPS905P.CDW_CUST_FCL_ASGN.CUST_ENTY_ID and OPS905P.CDW_CUST_STUB.AREA_ID=OPS905P.CDW_CUST_FCL_ASGN.CUST_AREA_ID and ((char(OPS905P.CDW_CUST_FCL_ASGN.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = ‘’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(OPS905P.CDW_CUST_FCL_ASGN.EFF_DTE) and
char(OPS905P.CDW_CUST_FCL_ASGN.EXPR_DTE)) )
AND ( ((char(OPS905P.CDW_FCL_STUB.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = '’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(OPS905P.CDW_FCL_STUB.EFF_DTE) and
char(OPS905P.CDW_FCL_STUB.EXPR_DTE)) )
AND ( ((char(OPS905P.CDW_CUST_STUB.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = ‘’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(OPS905P.CDW_CUST_STUB.EFF_DTE) and
char(OPS905P.CDW_CUST_STUB.EXPR_DTE)) )
AND ( REF_CTRY_CUST_INC.ENTY_ID = ‘HBUS’ ) and ( REF_CTRY_CUST_INC.AREA_ID = ‘US’ ) and ( ((char(REF_CTRY_CUST_INC.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = '’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(REF_CTRY_CUST_INC.EFF_DTE) and
char(REF_CTRY_CUST_INC.EXPR_DTE)) )
AND ( REF_CTRY_CUST_OPER.ENTY_ID = ‘HBUS’ ) and ( REF_CTRY_CUST_OPER.AREA_ID = ‘US’ ) and ( ((char(REF_CTRY_CUST_OPER.EXPR_DTE) >= CASE WHEN @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) = ‘*’
THEN ‘9999-12-31’ END )
OR @PROMPT(‘Enter Date (format YYYY-MM-DD) or * for Current Date’,‘C’,) BETWEEN char(REF_CTRY_CUST_OPER.EFF_DTE) and
char(REF_CTRY_CUST_OPER.EXPR_DTE)) )
GROUP BY
OPS905P.CDW_CUST.TOT_RELN_ID,
OPS905P.CDW_CUST.TRC_NME,
OPS905P.CDW_CUST.CUST_NME,
OPS905P.CDW_CUST.CIN_CUST_ID,
OPS905P.CDW_CUST.FNL_CUST_RISK_GR,
OPS905P.CDW_CUST.CTRY_CDE_OF_INC,
OPS905P.CDW_CUST.CTRY_CDE_PRIN_OPER,
OPS905P.CDW_CUST.CC_ID,
OPS905P.CDW_REFCDE_VT_RMGR.REFCDE_DESC,
OPS905P.CDW_CUST.SIC_CDE_1,
OPS905P.CDW_REFCDE_VT_SICD.REFCDE_DESC,
OPS905P.CDW_FCL_STUB.FCL_ID,
OPS905P.CDW_FCL.FCL_RM_CDE,
OPS905P.CDW_FCL.CC_ID,
REFCDE_VT_CCTR_FCL.REFCDE_DESC,
OPS905P.CDW_FCL.FCL_TYP_CDE,
OPS905P.CDW_FCL.FCL_STA_CDE,
OPS905P.CDW_FCL.FCL_GRD,
OPS905P.CDW_FCL.LOSS_GVN_DFLT_FAC,
OPS905P.CDW_FCL.ORIG_APPR_DTE,
OPS905P.CDW_FCL.NEXT_REVW_DTE,
OPS905P.CDW_FCL.FCL_EXPR_DTE,
REFCDE_VT_CCTR_FCL.CCTR_LGL_HIER_CDE,
OPS905P.CDW_CUST.PRNT_PER_LGL_HIER,
OPS905P.CDW_FCL.ULT_RISK_CTRY_CDE,
OPS905P.CDW_CUST_FCL_ASGN.BUS_ROLE_CDE,
REF_CTRY_CUST_INC.REFCDE_DESC,
REF_CTRY_CUST_OPER.REFCDE_DESC,
OPS905P.CDW_CUST.ENTY_ID,
OPS905P.CDW_CUST.AREA_ID

I am told by my DBA that Entity Id and Area Id are indexed fields. My question is this: Is there anyone out there who is using row restrictions effectively and efficiently on a large-scale universe like this? Also, will these row restrictions work for WEBI 2.7 users as well? Please help. Thanks.Ken

---

k685828 (BOB member since 2004-12-15)

[system]

Have you considered using a security table, rather than row restrictions? A table that maps BusObj ID to Entity ID and Area ID, and joined to the data tables with a stub join (usersec.ID=@variable(‘BOUSER’)), might perform better.

---

Dwayne Hoffpauir (BOB member since 2002-09-19)

[system]

No I have not thought about doing this. Can you provide me with more information as to what exactly you mean. My phone number is 716-841-1096 if you wish to discuss over the phone. I could also send you my universe file if it would help you.

---

k685828 (BOB member since 2004-12-15)

[system]

@k685828

No I have not thought about doing this. Can you provide me with more information as to what exactly you mean. My phone number is 716-841-1096 if you wish to discuss over the phone. I could also send you my universe file if it would help you.

Build a table (I’ll call is UserSec) with three columns … UserID, EntityID, and AreaID … and populate it accordingly. Assume your fact table (I’ll call it FactTbl) has four columns … FactID, EntityID, AreaID, and Measure. Create a join something like this:

FactTbl.EntityID=UserSec.EntityID and FactTbl.AreaID=UserSec.AreaID and UserSec.UserID = @variable('BOUSER')

Now, for each of the objects from FactTbl, click the Tables… button and make the object is associated with BOTH FactTbl AND UserSec. This will force the join to occur, even if objects only from FactTbl are included in the query. As a test, create a data provider that includes FactID and Measure as result objects. Even though you did not choose an object from UserSec, the following SQL will be written, and the row-level security will be enforced.

SELECT
  FactTbl.FactID,
  FactTbl.Measure
FROM
  FactTbl,
  UserSec
WHERE
  (FactTbl.EntityID=UserSec.EntityID and FactTbl.AreaID=UserSec.AreaID and UserSec.UserID = @variable('BOUSER'))

With proper indexing, it should perform quite well.

---

Dwayne Hoffpauir (BOB member since 2002-09-19)

[system]

See this link for some examples:

Using Designer to Implement Row-Level Security

Author: Steve Krande Further discussion on this downloadable should take place in this topic. Author’s Notes: UsingDesignerToImplementRowLevelSecurity.zip (425.0 KB) BOB Downloads (BOB member since 2003-05-05)

---

Steve Krandel (BOB member since 2002-06-25)

[system]

Take a look here too

LOV's - potential for deadlock with repeated refresh?

Hi Michele My technique which I am going to call "What They See Is What They Get" is only practical because many of the LOV's that I want to alter will be derived from 3 tables. [list:3150e75fee]A Time Dimension Hierarchy, customers will buy a subsription for a period of time A Location Dimension Hierarchy, customers will subscribe for information on a specific physical area, all sub divisions of this area are held in additional columns of the Location Hierarchy table A Supplier Dimensi…

---

Paul Shovlar (BOB member since 2002-09-05)