1

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.

0
1

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
0

Within database, you don't need to grant permission to wrapped object inside a view or stored procedure and it will work however when it comes to different database, you need to grant permission explicitly.

Having said above, if your role is doing select on any user defined table inside view - it will work. In the above scenario, either you need to grant permission on sysjobs at msdb - it depends on whether you will grant permission to a new role or the existing one. You shall need to add this role first of all in the msdb and grant permission on sysjobs.

Steps will be as follows:

use msdb
go
CREATE ROLE [CheckList_role];
GO
grant select on sysjobs to [CheckList_role]
go

Now any login who is having both the roles assigned i.e. one in msbd and other one in ChecklistDB would be able to perform a select from view MaintenanceJobs_view

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.