Database permissions to enable users to either manage data
and/or data definitions object like tables, views and other is a key security
concern on a database. In some cases, there is the need to allow a user to
access the data only. To do that, we need to create a read-only user on the
database. With this article, we look at the steps that are needed to create a
read-only user on an Azure SQL Server database.
Note: To follow this article, an Azure subscription with
a database already deployed is recommended.
Find the Server Information:
We should start this process by first getting the SQL Server
instance URL address from the Azure Console. Login the Azure console and select
or search for the SQL Server resources. Look
at the results and select the server hosting the database that needs the
additional user profile.
After selecting the server, make sure a server is selected
not a database instance, we should see the server information overview. From
this view, we can find the server URL name and the administration login
account. The password is not visible from the view, so we should get that
information from our security software like Key Vault or wherever the passwords
are kept. We need this information to be able to login into the database
remotely, so we can add the new user profile.
But before we login to the server, we also need to add the
client IP address to the server firewall configuration. This enables remote
client application to reach the database. From the server information page, search or
select “Firewall and virtual networks”.
We can then click the “+ Add Client IP” button. This reads the client IP
address from browser. This information is available because we are using a
browser to access the console. This adds
our current IP address information to the firewall rules to enable the access.
Once that looks correct, we should now press save to make sure the update is
made.
Ready to Connect
We should have all the required information to connect to
our database remotely using tools like Visual Studio Code (VSCode) or SQL
Server Management Studio (SSMS). VSCode
is a development tool that can target many platforms and languages. SSMS is
designed specifically to work on data platforms like SQL Server.
Once your preferred tool is open, we are ready to login, we
should connect to the target environment and database by using the server URL
and admin credentials. Once the connection is set, we can open a query window,
so we can add the following code:
Note: Make sure to
change the login, username and target database to match your environment.
|
--
select master database to create the login profile USE master; GO --
creates the login account CREATE LOGIN [rptLogin] WITH password='add-pw-here'; -- DROP LOGIN rptLogin; --
enable the access to login to the database CREATE USER [rptUser] FROM LOGIN [rptLogin] WITH DEFAULT_SCHEMA=[dbo]; -- DROP USER [rptUser]; -- move
to the target database context USE mydb; GO --
create the user on the target database CREATE USER [rptUser] FROM LOGIN [rptLogin] WITH DEFAULT_SCHEMA=[dbo]; -- add
the data reader role to the user EXEC sys.sp_addrolemember
@rolename = N'db_datareader', @membername = N'rptUser' |
Before we run the script, let us break down the code and
understand what is going on.
Create Login Account
To create an account to access the database, we must first
create a “Login” profile on the master database which host the system database
configuration and security for all the databases. We do this by first switching
to the master database context using the “USE” command. We then create the
login profile “rptLogin” with the default schema information “dbo”. We also add a user profile “rptUser” to the
master database from the login recently created to enable the user to login to
the server even if not database permission has been granted yet to this user
profile. This step is optional. It is more important to create the user in the
target database.
Create Database Read-Only User
At this point, there is a login account, but the user cannot
access any database. This leads us to the next step. We need to switch context
to the target database by using the “USE” command. We can then create the same
user “rptUser” under this database security context. This is the step to grant
the user access to a database. To make this user read-only, we need to assign
a role. This is done by granting this user the “db_datareader” role under the
selected database context.
Now that we have understanding on what the script would do for us,
we can execute the script and create the read-only user account. if there are
no error messages, we should be able to use the
login credentials to access the database. To login with the new account, open another
database connection and use the login credentials “rptLogin”. Please notice that we need to use the login account
as this is the profile with an assigned password.
Conclusion
In cases when users required
to access the database for read-only purposes, we can create user accounts with
the “db_datareader” role on a specific database. With this role, the user would only be able to
read data by running Select statements. Any attempt to insert, update or delete
operations are not allowed as this requires the “db_datawriter” role. By only
granting the “db_datareader” role, we limit the access thus making our database
more secure from possible wrong operations.
Thanks for reading
