How to apply Row Level Security within BO XI R3

Row level security within Business objects allows you to filter the returned data based on a users login from within the Universe.
This can be particulary useful for limiting the returned data to a user or set of country users rather than creating views or storing in seperate tables etc.

In this example i’ll apply a Business Unit key restriction to the SQL WHERE clause for the given table.

1. Identify all the tables to filter. I.e. all tables using bu key is some way (see bottom for details).

2. Open up Business Objects Universe Designer, here i’m using version 12.3, then select the universe to edit.

3. With the universe loaded, from the top menu bar select Tools->Manage Security->Manage Access Restrictions

Screenshot 1 showing the selection of Tools->Manage Security: Row_Level_Security_1_Designer.PNG

 

 

 

 

4. Under Available restrictions select ‘New’ to create a new restriction.

Screenshot 2 showing the screen of Manage Access Restrictions: Row_Level_Security_2_Manage_Access_Restrictions.PNG

 

 

5. The first tab on the edit restriction screen is Control. Here we give our restriction a suitable name and we also see the Connection parameter autoset.

Screenshot 3 showing the screen of Edit Restrictions Connections: Row_Level_Security_3_Edit_Restriction_Connection.PNG

 

 

 

 

6. Second tab displays the Control parameters that can be pre-set; Limit size of result set to, limit execution time to, warn if cost estimate exceeds, limit size of long text objects to. I’ve selected the default options here, 10min timout on execution time, and to limit long objects to a set size.

Screenshot 4 showing the screen of Edit restrictions Controls: Row_Level_Security_4_Edit_Restriction_Controls.PNG

 

 

 

 

7.Third tab shows the SQL options available to set: query options, Mutiple path selection, Cartesian products. I selected the default parameteres here too.

Screenshot 5 showing the SQL options Row_Level_Security_5_Edit_Restriction_SQL.PNG

 

 

 

 

8. Fourth tab shows the Objects page where you can exclude certain tables from the restriction.

Screenshot 6 shows the Objects page: Row_Level_Security_6_E_R_Objects.PNG

 

 

 

 

9. Fifth tab shows the Rows page where you may specify specific rules for the rows returned. This is the meat and potatoes of this exercise, click Add.. to create a new row.

Screenshot 7 shows the rows screen. Row_Level_Security_7_E_R_Rows.PNG

 

 

 

10. Once the Add button is clicked, the new row restriction screen is displayed. Either manually add a table name and WHERE clause, or use the ‘>>’ button to use the generator.

Screenshot 8 shows the table row restriction screen: Row_Level_Security_8_E_R_New_Row_Restriction.PNG

 

 

 

Screenshot 9 shows the query WHERE clause creator: Row_Level_Security_9_E_R_New_Row_Restriction_Where_Clause_Def.PNG

 

 

 

11. Having entered the required number of tables and WHERE restrictions save the changes back to the repository and then promote where appropriate.

List of tables and where clauses applied:

Table -> WHERE clause
WAFE_SALES_ORDER_DELIVERED -> WAFE_SALES_ORDER_DELIVERED.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_SALES_ORDER_DELIVERED_PFY -> WAFE_SALES_ORDER_DELIVERED_PFY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_SALES_ORDER_DELIVERED_PY -> WAFE_SALES_ORDER_DELIVERED_PY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_CUSTOMER_INVOICE -> WADA_CUSTOMER_INVOICE.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_CUSTOMER_INVOICE_GROUP -> WADA_CUSTOMER_INVOICE_GROUP.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_CUSTOMER_INVOICE_MASTER -> WADA_CUSTOMER_INVOICE_MASTER.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_CUSTOMER_SALES -> WADA_CUSTOMER_SALES.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_CUSTOMER_SALES_GROUP -> WADA_CUSTOMER_SALES_GROUP.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_CUSTOMER_SALES_MASTER -> WADA_CUSTOMER_SALES_MASTER.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADH_CUSTOMER_INVOICE_GROUP_H -> WADH_CUSTOMER_INVOICE_GROUP_H.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADH_CUSTOMER_INVOICE_H -> WADH_CUSTOMER_INVOICE_H.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADH_CUSTOMER_INVOICE_MASTER_H -> WADH_CUSTOMER_INVOICE_MASTER_H.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADH_CUSTOMER_SALES_GROUP_H -> WADH_CUSTOMER_SALES_GROUP_H.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADH_CUSTOMER_SALES_H -> WADH_CUSTOMER_SALES_H.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADH_CUSTOMER_SALES_MASTER_H -> WADH_CUSTOMER_SALES_MASTER_H.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_SUPPLIER -> WADA_SUPPLIER.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_SUPPLIER_ECS -> WADA_SUPPLIER_ECS.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_SUPPLIER_PRODUCT -> WADA_SUPPLIER_PRODUCT.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADH_SUPPLIER -> WADH_SUPPLIER.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADH_SUPPLIER_PRODUCT -> WADH_SUPPLIER_PRODUCT.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_PRODUCT -> WADA_PRODUCT.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_PRODUCT_ALT -> WADA_PRODUCT_ALT.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_PRODUCT_ALT_ECS_REV -> WADA_PRODUCT_ALT_ECS_REV.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_PRODUCT_CONTRACT_ECS -> WADA_PRODUCT_CONTRACT_ECS.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_PRODUCT_ECS -> WADA_PRODUCT_ECS.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_PRODUCT_MASTER_REV -> WADA_PRODUCT_MASTER_REV.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADH_PRODUCT -> WADH_PRODUCT.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_ORGANIZATION -> WADA_ORGANIZATION.COUNTRY_CODE IN ('PL')
WADA_ORGANIZATION_CUSTOMER -> WADA_ORGANIZATION_CUSTOMER.COUNTRY_CODE IN ('PL')
WADA_ORGANIZATION_PRODUCT -> WADA_ORGANIZATION_PRODUCT.COUNTRY_CODE IN ('PL')
WADA_ORGANIZATION_PRODUCT_H -> WADA_ORGANIZATION_PRODUCT_H.COUNTRY_CODE IN ('PL')
WADA_ORGANIZATION_SUPPLIER -> WADA_ORGANIZATION_SUPPLIER.COUNTRY_CODE IN ('PL')
WADA_ORGANIZATION_SUPPLIER_H -> WADA_ORGANIZATION_SUPPLIER_H.COUNTRY_CODE IN ('PL')
WADA_BUSINESS_UNIT -> WADA_BUSINESS_UNIT.BUSINESS_UNIT_CODE in ('96780')
WADA_BUSINESS_UNIT_PRODUCT -> WADA_BUSINESS_UNIT_PRODUCT.BUSINESS_UNIT_CODE in ('96780')
WADA_BUSINESS_UNIT_SUPPLIER -> WADA_BUSINESS_UNIT_SUPPLIER.BUSINESS_UNIT_CODE in ('96780')
WADA_BUSINESS_UNIT_CUSTOMER -> WADA_BUSINESS_UNIT_CUSTOMER.BUSINESS_UNIT_CODE in ('96780')
WADA_CUSTOMER -> WADA_CUSTOMER.OPCO_KEY IN (SELECT OPCO_KEY FROM WTDA_ORGANIZATION WHERE COUNTRY_CODE IN ('PL'))
WADA_CUSTOMER_ECS -> WADA_CUSTOMER_ECS.OPCO_KEY IN (SELECT OPCO_KEY FROM WTDA_ORGANIZATION WHERE COUNTRY_CODE IN ('PL'))
WAFA_SALES_ORDER -> WAFA_SALES_ORDER.OPCO_KEY IN (SELECT OPCO_KEY FROM WTDA_ORGANIZATION WHERE COUNTRY_CODE IN ('PL'))
WAFA_SALES_ORDER_PY -> WAFA_SALES_ORDER_PY.OPCO_KEY IN (SELECT OPCO_KEY FROM WTDA_ORGANIZATION WHERE COUNTRY_CODE IN ('PL'))
WAFE_CALENDAR -> WAFE_CALENDAR.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_CALENDAR_PY -> WAFE_CALENDAR_PY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_CONTRACT_PURCHASE_PRICE -> WAFE_CONTRACT_PURCHASE_PRICE.OPCO_KEY IN (SELECT OPCO_KEY FROM WTDA_ORGANIZATION WHERE COUNTRY_CODE IN ('PL'))
WAFE_PURCHASE_ORDER_GR -> WAFE_PURCHASE_ORDER_GR.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_PURCHASE_ORDER_GR_PFY -> WAFE_PURCHASE_ORDER_GR_PFY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_PURCHASE_ORDER_GR_PY -> WAFE_PURCHASE_ORDER_GR_PY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFV_STOCK -> WAFV_STOCK.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFV_STOCK_PFY -> WAFV_STOCK_PFY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFV_STOCK_PY -> WAFV_STOCK_PY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_PREFIX_EUDW -> WADA_PREFIX_EUDW.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_SELLING_REFERENCE_EUDW -> WADA_SELLING_REFERENCE_EUDW.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_VOUCHER_EUDW -> WADA_VOUCHER_EUDW.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_VOUCHER_EUDW -> WAFE_VOUCHER_EUDW.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WADA_WAREHOUSE -> WADA_WAREHOUSE.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780' -> 'n/a'))
WAFE_PROD_CATALOG_DIRECT_EUDW -> WAFE_PROD_CATALOG_DIRECT_EUDW.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_VOUCHER_EUDW_PFY -> WAFE_VOUCHER_EUDW_PFY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_VOUCHER_EUDW_PY -> WAFE_VOUCHER_EUDW_PY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_SALES_ORDER_DLVRD_CS -> WAFE_SALES_ORDER_DLVRD_CS.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))
WAFE_SALES_ORDER_DLVRD_CS_PY -> WAFE_SALES_ORDER_DLVRD_CS_PY.BUSINESS_UNIT_KEY in (select BUSINESS_UNIT_KEY from WTDA_BUSINESS_UNIT_EUDW where BUSINESS_UNIT_CODE in ('96780'))