RLS Questions & Answers

1. RLS_ALL user

RLS_ALL is a special database user without any server login mapping. That means you can't log into SQL Server with it.

The only way to use it is with EXECUTE AS USER = 'RLS_ALL'

It has access to all data that is separated by RLS functionality.

This user is used to, for example, create aggregates on data owned by multiple users.

For example a report for total sales for all employees for a year, but every employee can only see their own sales.

For a pantheon user to be able to use EXECUTE AS USER = 'RLS_ALL', it has to have GRANT IMPERSONATE permissions on RLS_ALL user.

ONLY users in Pantheon should have GRANT IMPERSONATE permissions.

Giving that permissions to built in SQL Server (like SA, etc) accounts is usually not needed.

• ONLY Pantheon users can read data from RLS protected tables

SA user (if not mapped to PA user) does not have access to protected data.
Any RLS protected data can only be read by PA user with appropriate authorizations.

In order to use external connections to database, connection to database must be done with PANTHEON user linked to Database user.

2. Adding users

Adding users to access other applications to the PANTHEON database.

We can add a user with PANTHEON:

See the following instructions: Adding a user and Setting authorizations for a user.

For more complex settings, where we want to add a user to multiple databases.

Adding User and Charging Autorizations without Rights. We set permissions of the user with PANTHEON:

declare
    @v_cUserId sysname = #UserName#
,    @p_lang_id varchar(2)
--
,    @p_subject varchar(30) = NULL
--
,    @p_user_id int = NULL  
,    @p_contact_id int = NULL  

BEGIN
 
  select * from  [dbo].[tHE_SetSubjContact]    WHERE acUserId = @v_cUserId;  

    SET @p_user_id = (SELECT anUserId FROM [dbo].[tHE_SetSubjContact] WHERE acUserId = @v_cUserId);

    set @p_contact_id = NULL;

    IF (@p_user_id IS NULL)
    BEGIN
        Exec ppa_user_create @v_cUserId, @v_cUserId, 1;
    
    select @p_subject = P.acOurCompany, @p_lang_id = PS.acLocalization from tPA_SysParam P, tPA_SysParamSys PS;
    
    SELECT @p_user_id = anUserId FROM [dbo].[tHE_SetSubjContact] WHERE acUserId = @v_cUserId;


      EXEC [dbo].[ppa_user_save]
          @p_user_id = @p_user_id OUTPUT -- @p_user_id int OUTPUT
      ,    @p_contact_id = @p_contact_id OUTPUT -- @p_contact_id int OUTPUT
      ,    @p_user_name = @v_cUserId -- sysname
      ,    @p_lang_id = @p_lang_id --  varchar(2)
      ,    @p_active = 'T' --  char(1)
      ,    @p_license_server = Null --  sysname = NULL
      ,    @p_license_database = Null --  sysname = NULL
      ,    @p_profile = Null --  varchar(10) = NULL
      ,    @p_additpersonmark = Null --  varchar(100) = NULL
      ,    @p_code = Null --  varchar(20)= NULL
      ,    @p_pin = Null --  varchar(11) = NULL
      ,    @p_foreigner = 'F'
      --
      ,    @p_subject = @p_subject --  varchar(30) = NULL
      ,    @p_name = @v_cUserId --  varchar(30) = NULL
      ,    @p_surName = @v_cUserId --  varchar(30) = NULL
      ;

    exec [dbo].[ppa_security_insert] @p_user_id, False, False;--no authorizations
    exec ppa_security_save @p_user_id, False, False;
    print @p_user_id
    exec [dbo].[ppa_authorization_security_save]
     NULL -- @p_authorization varchar(50)
    , @p_user_id -- @p_principal_id int
    , 0 -- @p_principal_type bit
    , NULL -- @p_parent_id varchar(50)
    , 0 -- @p_acquired bit
    , 0 -- @p_inherited bit
    , 0 -- @p_permission_select bit
    , 0 -- @p_permission_update bit
    , 0 -- @p_permission_delete bit
    , 32767 -- @p_permission_update_period int
    , 1 -- @p_propagate_entities bit = 0
    , Null -- @p_licence char(2) = 'MF'

    exec pPA_SysSQLLoginPswdChange @v_cUserId, Null, @v_cUserId;

    EXEC sp_addrolemember N'db_owner', @v_cUserId;
    EXEC sp_addrolemember N'db_datawriter', @v_cUserId;
    EXEC sp_addrolemember N'db_datareader', @v_cUserId;

  end;

  select * from  [dbo].[tHE_SetSubjContact]    WHERE acUserId = @v_cUserId;  
  select * from tPA_SetDoctypeUserSecurity where anUserId = @p_user_id;
end;

3. Droping users

declare
    @v_cUserId sysname = #USER#,
  @command nvarchar(max);
begin
    select * from  [dbo].[tHE_SetSubjContact]    WHERE acUserId = @v_cUserId;

    delete from  [dbo].[tHE_SetSubjContact]    WHERE acUserId = @v_cUserId;

    IF (DATABASE_PRINCIPAL_ID(@v_cUserId) IS NOT NULL)
    BEGIN
        SET @command = N'DROP USER ' + QUOTENAME(@v_cUserId) + N';'
        EXEC(@command);
    END;


    IF (SUSER_ID(@v_cUserId) IS NOT NULL)
    BEGIN
        SET @command = N'DROP LOGIN ' + QUOTENAME(@v_cUserId) + N';'
        EXEC(@command);
    END;

  select * from  [dbo].[tHE_SetSubjContact]    WHERE acUserId = @v_cUserId;
end;  

4. Tips and Tricks

With SQL Server's Row Level Security (RLS) in place this adds easy separation of user data, but adds a bit of query execution plan complexity to each query that uses the tables that have RLS enabled on them.

The goal is to minimize that query execution plan complexity.

- Usage in stored procedures

Avoid a lot of joins with RLS tables. If you need to join a lot of tables together, it is better to join a few (2-4 tables), put the result of that into a temporary table (that can even be properly indexed) and join that temporary table to other tables.

This will make the query optimizer's job easier since the query is broken into multiple pieces.

- Usage in triggers

In after triggers there's no need to join to the base table to get the data.

For update or insert statement, the inserted pseudo table already contains new values that are also in the base table.

So all you have to do is join to it.

If you have a scenario where you have to check for existence of some data in a trigger and update data based on that check, it's better to put the check results into a temp table, check if that data satisfies the desired condition and use it update the base table.

- SQL Server's Built-In Function support for parallelism

If you ever have to check a user name or a SQL login under which the current context is executing under, use the following functions in select statements.

They are the only ones that support parallelism in SQL Server execution plans:

USER_NAME, CURRENT_USER, SUSER_NAME, SUSER_SNAME, SUSER_ID, SUSER_SID.

Any other built-in function prevents consideration of parallel query execution plans.