RLS Questions & Answers

1. RLS_ALL korisnik

RLS_ALL je poseban korisnik baze podataka bez ikakvog mapiranja prijavljivanja na server. To znači da se sa njim ne možete prijaviti na SQL Server.

Jedini način da koristite ovog korisnika je EXECUTE AS USER = 'RLS_ALL'

Ima pristup svim podacima koji su odvojeni RLS funkcionalnošću.

Na primer, izveštaj o ukupnoj prodaji za sve zaposlene za godinu dana, ali svaki zaposleni može da vidi samo svoju prodaju.

SAMO korisnici Pantheon-a mogu da imaju dozvole GRANT IMPERSONATE.​

Davanje tih dozvola ugrađenim nalozima SKL Servera (poput SA, itd.) obično nije potrebno.

• SAMO korisnici Pantheon-a mogu čitati podatke iz RLS zaštićenih tabela

2. Dodavanje korisnika

Možemo dodati korisnika uz pomoć PANTHEON-a:

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.