Authentication and access control are two crucial factors in securing databases and their servers. One, authentication, controls who can access the data resource, and in what capacity, while the other, access control, specifies what a user can do once they have been authenticated.
Historically, authentication and access have been managed entirely by SQL Server, but Azure has enabled integrated password and multi-factor authentication courtesy of Azure Active Directory, along with built-in SQL DB roles.
This course looks at various ways to integrate Azure SQL with Azure Active Directory and how to best manage user privileges once logged into the database.
If you have any feedback relating to this course, please contact us at support@cloudacademy.com.
Learning Objectives
- Get a basic understanding of the history and context of SQL authentication
- Understand how to to use Azure Active Directory to authenticate users with a SQL database
- Learn how to use database roles to customize access
- Understand the principle of least privilege and how to apply it
- Learn how to fine-tune access to database objects
Intended Audience
This course is intended for database administrators using Azure, or anyone who wants to understand more about using Azure Active Directory to authenticate a user to access a database.
Prerequisites
To get the most out of this course, you should have a basic understanding of databases and the Azure platform.
If your database server is a managed instance or SQL Server running on a VM or on-premise, you are not limited to SQL statements to assign permissions to roles and roles to users. Within SQL Server management studio, there are graphical tools or a graphical interface that lets you perform the same tasks.
I’m going to start by creating a new login called whome. I’ll make this an SQL Server login and give it a very simple password. The password is so simple. In fact that I will uncheck in force password policy because it will not pass the validation.
Next, I’m going to make the default database for this user, the lorrylog database. I won’t assign any server roles, and in terms of user mapping, I will map them directly to the lorrylog database giving them the default scheme of dbo.
You can notice that the db_owner role has been automatically assigned when I assigned them to this database. I’ll just uncheck that. And give them the data reader and data writer roles and click okay. If I go into the lorrylog database and look under security and users, we can see the new user sitting in there.
That user was created from the login when I selected the lorrylog database and going into membership; we can see the data reader and data writer roles. Now I’m still logged in as the SA user so, what I’ll do is create a new table that will be imaginatively named NewTable. This is a very simple table is just an ID and a varchar field. I’ll also insert one record into the new table.
Now I’m going to go to the new table and set permissions to deny the whome user from inserting records into that table. So, right-click on the table, select properties, go to permissions, and first of all, search uses or roles for the whome user. Under permissions, I will find the insert permission and check the deny box.
As well as the grant and deny permissions, there is also the with grant permission. This means that a user not only has that permission, but they have the ability to delegate that permission to another user or role. Now I’m going to log into the server as the whome user, and we can see that I’m not permitted to view any of the other databases.
Now when I go into the lorrylog database, I’ll open a new query window. Because whome is part of the data reader role, I’ll do a select on the new table. Sure enough, there is the data that we previously inserted. Now I’ll try and insert another record into NewTable, and as we would expect, permission is denied on doing the insert. However, when I do an update, that is successful.
As I said earlier, we can set permissions on almost any object within a database, and that includes columns, so what I’ll do now is go and set select permissions on the new table by going back into permissions under properties select my who me user so I’m going to deny select on the ID column, thereby hiding it.
So, the first thing we have two deny select permission, then the column permissions button becomes enabled, and from there, we can specify which column, we want to deny or grant permission on, so we click okay and okay. We do a select *, and sure enough, we don’t have select permission on the ID column. So then we actually have to specify the columns that we do have permission on, and in this case, it’s just name.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.