Locking Resources in Azure with Read Only or Delete Locks

By:   |   Updated: 2019-09-18   |   Comments (2)   |   Related: More > Azure

Problem

I have a number of critical Azure SQL Server resources to manage and I want to ensure that users don’t accidently delete or update the resources. Is there any easy way this can be accomplished in Azure?

Solution

This tip assumes that you already have resources in your Azure environment. If you are new to Azure, you can subscribe to a trial account here which will give you access to Azure services for an year and a credit of USD $200. In this tip, we will learn about the concept of locks that can be used to prevent users from modifying or deleting the resources.

What are Azure locks?

Azure has basically two kinds of locks known as read-only and delete lock.

  • Read-only lock is something similar to assigning a reader role for your users. The authorized users will not be able to modify the resource, but they can only read from the resource.
  • With delete lock, authorized users will be able to read and modify the resource, but will not be allowed to delete the resource.

Demo of a Read-only Lock Using the Azure Portal

This tip assumes you already have an Azure SQL database configured. You can refer this tip which has a detailed description on how to create a SQL database in Azure. On the screenshot below, you can see that there is an Azure SQL database already configured.

azure resource locks

There is also an option on the left-hand pane called "Locks". Click on the "Locks" option. On the "Locks" option page, you have the option to add a new lock.

add an azure resource lock

Click on the option to add a new Lock

Once you click on the option to "Add" a new lock, you will see this option.

add a read-only azure lock

Here, you can see two lock types: Read-only and Delete

You can select the option "Read-only" and provide a suitable name as shown. Once done, click on "OK".

add a read-only azure lock

Once you have clicked "OK", you will be able to view the saved lock.

add a read-only azure lock

Test the Read-only Lock

Now, that the read-only lock is created, you can test the Azure SQL database to see if the read-only database lock is working as expected. Click on the "Tags" option that you can see on the left-hand pane. Here, you can view details of the tags that are already assigned to the SQL database resource.

test read-only azure lock

Now, let us try to create a new tag name\value pair assignment and then click on save. You will notice that the operation to add the new tag assignment will fail with an error message. Here in the screenshot, you can see the error message which states that the "Resource refused tag assignment (possibly locked))" thereby confirming that the resource is indeed locked. In our case it is a read-only lock and the resource cannot be modified in anyway.

test read-only azure lock

Operations within the SQL database

You will notice that the read lock was basically at the resource level. However, the operations within the SQL database wouldn’t be impacted by this read lock. You can consider an example of creating a sample table in the SQL database and the operation will succeed as shown. You can click on the option "Query Edition (preview)" and then run a command to create a table as shown below.

test read-only azure lock

This command will complete successfully and has no impact on the read-only lock that was set at the SQL database resource level. Resource operations within the database will continue to perform as normal.

Demo of the Delete Lock Using the Azure Portal

You can go ahead and delete the read-only lock that was created on the Azure SQL database. For deleting the lock, click on the "Lock" option on the resource and click on the right-hand corner ellipsis and select "Delete". This will delete the read-only lock that was assigned to the resource.

create azure delete resource lock

Next, create a new "Delete" lock as shown. Click on "OK" once done.

create azure delete resource lock

With this, a new "Delete" lock is assigned to the Azure SQL database.

create azure delete resource lock

Test the Delete Lock by adding few tag assignments to the Azure SQL database

With the delete lock type, modifications are allowed to the resource, but the resource itself can’t be deleted by authorized users. Let us try to add a new tag assignment like how we tested for the read-only lock type.

create azure delete resource lock

Enter the details for the tag name\value pair and click on the "Save" option. You will see that the new tag name\value pair gets saved successfully for the Azure SQL database.

create azure delete resource lock

In the demo for the "Read-only" lock type, you noticed that the new tag assignment failed as changes to resources were not allowed.

Check delete operations within an Azure SQL database that has a delete lock assigned

For testing the read-only lock type within the Azure SQL database, you created a sample table named test. In this demo, let us delete the test table from the database.

check azure delete lock

Here, you can see that the test table is successfully deleted from the Azure SQL database and that the delete lock type has no impact on the delete operations within the Azure SQL database.

Test the delete lock by attempting to delete the Azure SQL database

Now, that you have tested modifications to the resource and also internal operations within the resource, in this section let us try deleting the Azure SQL database itself from the portal.  Click on the "Overview" option that is visible on the left-hand pane.

azure resources

Once done, click on the option to "Delete" the Azure SQL database.

try to delete azure resource

Provide the name of the database and then click on the "Delete" option to remove the Azure SQL database.

azure delete lock failure message

You can see an error message confirming failure of the delete operation.

Next Steps
  • In this tip, you learned about the concept of Azure Locks, there are two types of locks: read-only locks and delete locks
  • Read-only lock is similar to assigning a user reader access which prevents any modifications to the Azure resource
  • Delete lock has higher privileges as modifications are allowed on the resource, but the resources themselves cannot be deleted once delete locks are assigned
  • In the demo, you learned that there will be no impact on the internal resource operations when these locks are applied, either read-only or delete
  • You need owner privileges in order to assign these locks
  • The locks can be assigned at the resource level, resource group level and also at the subscription level
  • Try out this demo on your own test Azure environment before applying in production
  • Refer other articles available on MSSQLTips related to Azure


Last Updated: 2019-09-18


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, September 18, 2019 - 3:20:38 PM - Mohammed Back To Top

Thank you Sri


Wednesday, September 18, 2019 - 12:00:55 PM - Srinath Back To Top

Good stuff and great work as always. Thanks for sharing, Moinu ji.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools