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 = 'SALES001'
,    @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;
  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;