Tables with Row Level Security not restored


#1

I just tested restoring an Azure SQL database. Only the contents of the tables without Row Level Security was restored. My security policy was restored. If I disable it and query the tables it is applied to I can see that the tables are empty.

This is the script we used to configure RLS:

--SELECT @@Version
CREATE SCHEMA Security
go

ALTER TABLE dbo.Workspaces ADD Tenant_Id UniqueIdentifier 
    DEFAULT CAST(SESSION_CONTEXT(N'Tenant_Id') AS UniqueIdentifier)
go


CREATE FUNCTION Security.tenantAccessPredicate(@UserId UniqueIdentifier)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS accessResult
    WHERE @UserId = CAST(SESSION_CONTEXT(N'Tenant_Id') AS UniqueIdentifier)
go

CREATE SECURITY POLICY Security.tenantSecurityPolicy
	ADD FILTER PREDICATE Security.tenantAccessPredicate(Tenant_Id) ON dbo.Workspaces,
	ADD BLOCK PREDICATE Security.tenantAccessPredicate(Tenant_Id) ON dbo.Workspaces
go

#2

Hi Gabriel,

We have checked the issue. SQLBackupAndFTP performs Azure backups via export in .bacpac file. It is not a low-level database’s backup, and it cannot get data that is not available for the user on whose behalf the backups are run.

We recommend you to create a separate user (for example backupAdmin) to perform backups, with providing all necessary rights, then update your predicate function and adding there condition “OR USER_NAME() = ‘backupAdmin’”

Sorry for the inconvenience.


#3

I changed my predicate function as follows:

ALTER FUNCTION Security.tenantAccessPredicate(@UserId UniqueIdentifier)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS accessResult
    WHERE @UserId = CAST(SESSION_CONTEXT(N'Tenant_Id') AS UniqueIdentifier) OR SYSTEM_USER = 'backupAdmin'
go

Now the backup includes all records. I can also read from the restored database using the backupAdmin account that has the db_owner role. I found that db_owner is needed for SQLBackupAndFTP to be able to create, backup and then drop a snapshot.

Other SQL accounts than backupAdmin does not seem to be restored since I cannot login with other users. Is this also a limitation of bacpac exports?

/* These users need to be dropped and recreated after restore */
CREATE USER read FROM LOGIN read;
EXEC sp_addrolemember 'db_datareader', 'read';

CREATE USER app FROM LOGIN app;
EXEC sp_addrolemember 'db_datareader', 'app';
EXEC sp_addrolemember 'db_datawriter', 'app';

CREATE USER migrator FROM LOGIN migrator;
EXEC sp_addrolemember 'db_datareader', 'migrator';
EXEC sp_addrolemember 'db_datawriter', 'migrator';
EXEC sp_addrolemember 'db_ddladmin', 'migrator';

CREATE ROLE migrator
GO

GRANT
	SELECT,
	INSERT,
	UPDATE,
	ALTER,
	ALTER ANY SECURITY POLICY
TO migrator
GO

EXEC sp_addrolemember 'migrator', 'migrator'
GO

/* This user runs the backup and restore and seems to work after a restore */
CREATE USER backupAdmin FROM LOGIN backupAdmin;
EXEC sp_addrolemember 'db_owner', 'backupAdmin';

#4

Hi Gabriel_Smoljar,

Thanks for the details. Please give us some time to check the issue.

Sorry for the inconvenience.


#5

I would avoid using the server admin if possible. I tried adding the loginmanager server role to the backupAdmin account. Unfortunately restore fails. Looks like it is trying to create the user that is already running the restore.

Failed to restore "dm365-test-2019-07-07-1848" database on "Restoring" step. > Could not import package.
Error SQL72014: .Net SqlClient Data Provider: Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name.
Error SQL72045: Script execution error.  The executed script:
CREATE USER [backupAdmin] FOR LOGIN [backupAdmin];

#6

Hi Gabriel,

Thank you for the details. Please give us some time to check this issue.

Sorry for the inconvenience.


#7

Hello Gabriel,

Thank you for waiting. We have checked this issue and found out that it occurs because of database user [dbo] is mapping to login [backupadmin] (because of database recreation by backpadmin) and during the next attempt to mapp login [backupadmin] to database user [backupadmin] the issue occurs.

We recommend you to perform backups via backupAdmin login but restore them from a behalf login, who created a database, as a rule, it is a server admin login.

Sorry for the inconvenience.