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.
I’ve mentioned permissions and privileges quite a bit so far, and if you’re new to databases, it may seem a little vague. Almost every element or object within a database, ranging from tables to stored procedures through to roles, can have actions performed on them.
Data stored within a database usually within tables, but this can include views, can be read, inserted, updated, and deleted. Objects and entities within a database like tables, views, stored procedures and functions, users, and roles, in fact everything can be created and deleted, or in the case of objects, dropped.
Stored procedures and functions, that is, objects that perform tasks, can be executed, so the ability to execute is also a permission. In terms of managing permissions on objects, or the ability for users or roles to perform actions, permissions can be granted, denied, or revoked.
In this first example, I have created the hallamad user within the ADAuthenticate database and have given them the data reader and writer roles. With these permissions, this user can insert a record into the TestTab table.
As the database admin, I will deny hallamad permission to update the TestTab object with the deny update on object statement. When hallamad tries to change Dr. Spock to Dr. Who, he is denied. As well as denying someone with the data writer role the ability to insert or update on a particular table or object, you can also grant a user permission to modify data on a specific table or object where they only have the data reader role. I can undo the denial of update permission for this user by using the revoke statement. Whether the special update permission was denied or granted, the revoke statement will reverse it.
Remember when we talked about authentication, and I set up an Azure Active Directory user group and granted permission for that group to authenticate against the database, thereby allowing members of that group to log in? The same can be done for database roles. I’ll remove hallamad from the data writer role with the drop role member procedure. Then I’ll create a new role, so equivalent to a user group, called specialrole, and I’ll add hallamad as a member of that role. Finally, I will grant update permissions on TestTab to the specialrole. Through membership of that role, hallamad is now able, once again, to update the TestTab table.
Querying the database role members table with database principles, we can see the roles currently in the database, including the one we’ve just created, along with users that are members of those roles.
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.