Code changes

1. Modify SQL Objects

SQL: Add WITH EXECUTE AS 'RLS_ALL'

Queries that need access to all data regardless user authorizations should be moved to SQL objects (stored procedures, functions, ..) and executed with impersonation AS USER 'RLS_ALL'

a) Sample: Find last acKey in tHE_Move:
When inserting records, new ackey is needed to insert a document.
User who is inserting the record does not have authorization on all the documents for certain document type as authorizations depend on department, warehouse and / or any other authorization, thus it is needed to execute such SQL without authorization restrictions.

please check stored procedure pHE_MoveGetNewKey

Note: WITH EXECUTE AS 'RLS_ALL' can be used in Views, triggers, functions (except inline table valued functions)

b) Check also:
gPA_SetDoctype_security
pMN_DocumentNewKey

Find RLS_ALL impersonated objects:
select SO.*, OBJECT_DEFINITION(SO.object_id)

from sys.objects SO

where OBJECT_DEFINITION(SO.object_id) like  '%RLS_ALL%'

2. ARES

a) Modify PL / SQL scripts
To impersonate complete PLS /SQL script use EXECUTE AS USER = 'RLS_ALL'.

Check sample on Dev Site.

Same code is generated when using Dataset property

Use prepared methods to achieve this functionalities.

Check ARES Compatibility mode

b) ARES and SQL: Remove joins to t%Security tables in SQL and PL/SQL code
Check vHE_Move changes

Joins to t%UserSecurity tables need to be removed. RLS cross applies t%UserSecurity tables as specified with security policy and RLS function for each table. For detailed information please check RLS Architecture.

3. Code changes – Sample 1:

Objects that need access without permissions need to be modified by adding:

with execute as 'RLS_ALL'

4. Code changes – Sample 2:

create procedure [dbo].[pHE_MoveGetNewKey]

   @cPoslDog   varchar(04),

   @dDatum     DATETIME,

   @cKljuc     varchar(13) = '' OUTPUT

WITH EXECUTE AS 'RLS_ALL'