Minimum ALTER Permissions to scale Azure SQL Server

rqenqsqc  于 2023-04-04  发布在  SQL Server
关注(0)|答案(2)|浏览(130)

I'm using the following query to scale up/down Azure SQL server programatically:
ALTER DATABASE [DB] MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S3')

And I used this StackExchange Answer to give the DB user enough permissions to run the query:

GRANT ALTER ON DATABASE:: [DB] TO [username]

However I don't want to open up this user to have more database permissions than necessary.

Is there a way to only give ALTER access to modify the SERVICE_OBJECTIVE and not have any other heightened permissions?

plicqrtu

plicqrtu1#

You can encapsulate the ALTER DATABASE command in a stored procedure and sign it with a certificate that confers ALTER DATABASE permissions. This will permit minimally privileged users, with only execute permissions on the procs, to scale the database up/down even without ALTER DATABASE permission.

Example T-SQL script below. TL;DR, see Packaging Permissions in Stored Procedures .

--create certificate and cert user with necessary permission
CREATE CERTIFICATE AlterDatabaseCert
   ENCRYPTION BY PASSWORD = 'p%FD$4bb925DGvbd24^9587y'  
   WITH SUBJECT = 'For Alter Database User';   
GO  

CREATE USER AlterDatabaseUser
    FROM CERTIFICATE AlterDatabaseCert;
GO

GRANT ALTER ON DATABASE::[DB] TO AlterDatabaseUser;
GO

--create and sign stored procedures
CREATE OR ALTER PROC dbo.usp_ScaleUpDatabase
AS
ALTER DATABASE [DB]
    MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S12');
GO

ADD SIGNATURE to dbo.usp_ScaleUpDatabase
    BY CERTIFICATE AlterDatabaseCert
    WITH PASSWORD = 'p%FD$4bb925DGvbd24^9587y';
GO

CREATE OR ALTER PROC dbo.usp_ScaleDownDatabase
AS
ALTER DATABASE [DB] 
    MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S3');
GO

ADD SIGNATURE to dbo.usp_ScaleDownDatabase
    BY CERTIFICATE AlterDatabaseCert
    WITH PASSWORD = 'p%FD$4bb925DGvbd24^9587y';
GO

--grant execute permissions to minimally privileged users/roles
GRANT EXECUTE ON dbo.usp_ScaleDownDatabase TO [username];
GRANT EXECUTE ON dbo.usp_ScaleUpDatabase TO [username];
GO
unftdfkk

unftdfkk2#

Is there a way to only give ALTER access to modify the SERVICE_OBJECTIVE and not have any other heightened permissions?

As siggemannen suggested when you want to give a minimum permission to user on database create a stored procedure and give only execute permission on that stored procedure.

In your case you need to grant two permissions to user:

  1. As you want to alter database you need to grant Alter permission on database.
  2. create stored procedure and give execute permissions for that respective stored procedure only.

First create a stored procedure to modify database.

create procedure scaleupdown(@Edition VARCHAR(50), @Service_Objective VARCHAR(50))
AS
BEGIN
DECLARE @SQL  NVARCHAR(MAX)
SET @SQL = CONCAT('ALTER DATABASE database1 MODIFY(EDITION = ''',@Edition,''', SERVICE_OBJECTIVE = ''',@Service_Objective,''')')
EXEC(@SQL)
End

Then grant permissions to user

GRANT EXECUTE ON dbo.scaleupdown TO demouser1;
GRANT ALTER ON DATABASE:: database1 TO demouser1;

The store procedure where user has permission to execute other than that it will throw an error:

相关问题