Minimal permissions for Amazon RDS in database source control

Applies to

ApexSQL Source Control

Summary

In this article, all needed database user permissions for linking Amazon Relational Database Service (Amazon RDS) to a source control repository and working with it in the database source control using ApexSQL Source Control will be explained.

Description

When talking about the Amazon RDS environment it is applied several users are working on the same shared SQL Server/database at the same time. For that type of environment, the necessary set of permissions must be granted to those database users working on the database development.

In this type of environment usually is one admin user with permissions to create new logins/users and grant them all necessary permissions for the database development. Based on that, work in the database source control requires the specific permissions granted to the database user as well, to link a database to a source control repository and track changes on it.

The database source control done using ApexSQL Source Control applies that the additional objects will be created in the linked database, based on the chosen development model. For these objects, called the framework objects, the user must be granted with a specific set of permissions.

For this article, since the necessary database source control permissions depend on the chosen development model, for each of them will be created user (for dedicated and shared development model). The Deja user will cover the dedicated development model and the Mara user will cover the shared development model.

After these database users are created, the only granted default permissions they have are:

  • SQL Server permissions:

    • Connect SQL
    • View any database
  • Database permissions

    • Connect
    • View any column encryption key definition
    • View any master key definition

Dedicated development model

The dedicated development model applies there will be only one user working on the chosen database, only one user changing the database. The first step in the database source control is linking a database to a source control repository. The following framework objects created in the chosen database during the linking process are:

  • dbo.ApexSQL_SourceControl.ChangeLog table – will be created under the Tables node, the System Tables sub-node
  • ApexSQL_SourceControl_DDL Trigger – will be created under the Programmability node, the Database Triggers sub-node

Try to link the database to a source control repository with the Deja user with the default Server/database permissions will raise this message:

Message for the dedicated model when the permissions are missing

Since the database source control cannot be done without the listed framework objects, the following permission need to be granted to the Deja user, so it can create and update them as needed:

  • Connect
  • Alter any database DDL trigger
  • Select
  • Insert
  • Update
  • Execute
  • Delete
  • Create table
  • Alter dbo schema

Granting the listed permissions to the Deja user can be done in two ways, using SQL Server Management Studio UI or executing the SQL scripts.

To grant permission using SQL Server Management Studio, the Deja user is mapped to the specific database. In the Object Explorer panel, right-click on that database, and click the Properties context menu option:

Right-click context menu in the Object Explorer panel for Azure SQL Database

When the Database Properties window is opened, go under the Permission tab, select the Deja user and in the Grant column check for all needed (above listed) permissions and click the OK button:

Granting necessary permissions in the Database Properties window for the newly created user for the dedicated model

Granted database permissions to the Deja user can be checked under the Effective tab in the same window:

All granted permissions for the dedicated model listed under the Effective tab in the Database Properties window

Quick tip icon

Quick tip:

For granting the Alter dbo schema permission will be used a SQL script.

Granting the same set of database source control permissions to the Deja user can be done when the following SQL script is executed:

GRANT
        ALTER ANY DATABASE DDL TRIGGER, 
        SELECT,
        INSERT,
        UPDATE,
        EXECUTE,
        DELETE,
        CREATE TABLE TO [Deja]
GRANT
        ALTER ON SCHEMA::[dbo] TO [Deja]

No matter which way is chosen for granting the database source control necessary permissions to the Deja user, after this linking a database in the dedicated development model and working with it using the Deja user will go perfectly.

Shared development model

When more than one database developer works on the same shared database at the same time, the best practice for the database source control is to choose the shared development model during the linking process. The shared development model will require additional permissions than the dedicated development model since different framework objects are created in the linked database.

The following framework objects created in the chosen database during the linking process under the ApexSQL_SourceControl schema (for the dedicated development model the dbo schema is being used for framework objects are:

  • Under the Table node the following tables will be created:

    • Objects
    • Polices
    • ChangeLog
    • UserMappings
    • ExcludedObjects
  • Under the Procedures node the following procedures will be created:

    • UpdateObjects
    • UpdateExcludedObjects

  • Under the Programmability node, the Database Triggers sub-node, the following trigger will be created:

    • ApexSQL_SourceControl_DDLTrigger

If the Mara user, with the default database permissions (Connect, View any column encryption key definition, View any master key definition) tries to link a database to a source control repository, the following message will appear when the Next button is clicked in the Development model tab when the Shared development model is chosen:

Message for the shared model when the permissions are missing

With all listed database permissions in the message, database user, in our case the Mara user, must have the following permissions to link a database to a source control repository and work on it in the database source control:

  • Connect
  • Alter any database DDL trigger
  • Select
  • Insert
  • Update
  • Execute
  • Delete
  • Create table
  • Create procedure
  • Create schema
  • Alter any schema
  • View definition

Quick tip icon

Quick tip:

The View definition database permissions must be granted in order to all database users “see” all database users and their changes made against the database objects in the shared development model.

To grant all necessary database permissions to the Mara user, right-click on the database in the Object Explorer panel and from the context menu, click the Properties option, to open the Database Properties window:

The Database Properties window for the newly created user with only default permissions

Check the Grant column under the Explicit tab for all above listed necessary permissions and click the OK button:

Granting necessary permissions in the Database Properties window for the newly created user for the shared model

The granted database permissions to the Mara user can be found under the Effective tab in the same window:

All granted permissions for the shared model listed under the Effective tab in the Database Properties window

Granting the same set of database permissions to the Mara user can be done when the following SQL script is executed:

GRANT
	ALTER ANY DATABASE DDL TRIGGER, 
	SELECT,
	INSERT,
	UPDATE,
	EXECUTE,
	DELETE,
	CREATE TABLE,
	CREATE PROCEDURE,
	CREATE SCHEMA,
	ALTER ANY SCHEMA,
	VIEW DEFINITION TO [Mara]

With this set of database permissions, the Mara user can link a database to a source control repository and work with a database in the database source control perfectly.

These database permissions are required only for the first user who will link the database in the shared development model to a source control repository. All other users should have only these database source control permissions to link and work on the same shared database:

  • Connect
  • Alter any database DDL trigger
  • Select
  • Insert
  • Update
  • Execute
  • Delete
  • Alter any schema
  • View definition