Sql-server – Create a view in a user database that can access selected system database tables

permissionsrolesql server

Using the principle of least privilege, what access should I give to a Role "Checklist_role" that has read permission to a view "MaintenanceJobs_view" in a user database "ChecklistDB", that can access certain columns in a system table within the msdb database?

The following is an example similar to what I would like to do:

USE ChecklistDB;
GO
/* Create View */
CREATE VIEW [MaintenanceJobs_view]
AS
SELECT [name],
       [description],
       [enabled],
       [category_id]
FROM [msdb].[dbo].[sysjobs];
GO
/* Create Role based on principle of least privilege */
CREATE ROLE [CheckList_role];
GO
/* What permissions are required? */

Can this be done by granting certain privileges to the the role(Checklist_role) without giving privileges to any objects in the "msdb" database?

At this stage it is mainly to show a proof of concept in a very limited amount of time. In the long term I would definitely like to use a stored procedure.

Best Answer

Can this be done by granting certain privileges to the the role(Checklist_role) without giving privileges to any objects in the "msdb" database?

Rather than a view, you could instead use a multi-statement table-valued function or stored procedure containing the query and sign the module with a certificate to confer additional permissions. This allows you to follow the principle of least privilege since only permissions on the function or stored procedure are needed.

Below is an example script that creates the certificate in both databases along with the needed user and permissions.

USE ChecklistDB;
GO
CREATE FUNCTION MaintenanceJobs_function()
RETURNS @table TABLE(
     name sysname
    ,description nvarchar(1024)
    ,enabled tinyint
    ,category_id int
)
AS
BEGIN
    INSERT @table
    SELECT [name],
           [description],
           [enabled],
           [category_id]
    FROM [msdb].[dbo].[sysjobs];
    RETURN;
END
GO
CREATE ROLE Checklist_role;
GRANT SELECT ON dbo.MaintenanceJobs_function TO Checklist_role;
GO

--Create self-signed cert in ChecklistDB and copy to msdb
USE ChecklistDB;
CREATE CERTIFICATE CrossDatabasePermissionsCertificate
    ENCRYPTION BY PASSWORD = 'temporary password'
    WITH SUBJECT = 'Allow cross-database access';

--Copy cert to another database.
DECLARE @cert_id int = CERT_ID('CrossDatabasePermissionsCertificate');
DECLARE @public_key  varbinary(8000) = CERTENCODED(@cert_id),
        @private_key varbinary(8000) = CERTPRIVATEKEY(@cert_id , 'temporary password', 'temporary password');

--these values should not be NULL
IF @cert_id IS NULL THROW 50000, 'Assertion failed: @cert_id is NULL', 0;
IF @public_key IS NULL THROW 50000, 'Assertion failed: @public_key is NULL', 0;
IF @private_key IS NULL THROW 50000, 'Assertion failed: @private_key is NULL', 0;

--copy certificate in target database
DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE CrossDatabasePermissionsCertificate
       FROM  BINARY = ' + CONVERT(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          CONVERT(varchar(MAX), @private_key, 1)
        + ', DECRYPTION BY PASSWORD = ''temporary password'''
        + ', ENCRYPTION BY PASSWORD = ''temporary password'');'
PRINT @sql; --show CREATE CERTIFICATE DDL

EXEC msdb.sys.sp_executesql @sql;
GO

--create certificate user in msdb and grant permissions needed by the module
USE msdb;
CREATE USER CrossDatabasePermissionsCertificateUser FROM CERTIFICATE CrossDatabasePermissionsCertificate;
GRANT SELECT ON dbo.sysjobs TO CrossDatabasePermissionsCertificateUser;
GO

--add signature to module by the same certificate that exists in both databases
USE ChecklistDB;
ADD SIGNATURE TO dbo.MaintenanceJobs_function BY CERTIFICATE CrossDatabasePermissionsCertificate WITH PASSWORD = 'temporary password';
ALTER CERTIFICATE CrossDatabasePermissionsCertificate REMOVE PRIVATE KEY;
GRANT SELECT ON dbo.MaintenanceJobs_function TO Checklist_role;
GO

--test with minimally priviliged login/user
CREATE LOGIN TestUser1 WITH PASSWORD = 'Test-Permissi0ns';
CREATE USER TestUser1;
ALTER ROLE Checklist_role
    ADD MEMBER TestUser1;
EXECUTE AS LOGIN = 'TestUser1';
SELECT * FROM dbo.MaintenanceJobs_function();
GO
REVERT;
GO
Related Question