RLS Questions & Answers

- Adding users
- Droping users
|
1. 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;
2. 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;