Course Intro and Overview
Azure Active Directory Authentication
Access within the Database
Summary
The course is part of this learning path
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.
When we created a user within the database, that user could connect to the database, but they couldn’t do anything without the appropriate permissions. To enable a user to read data from a table, I assigned them to the DB data reader role with the SP_add role member stored procedure.
You might recall I also gave the user the db_owner role to enable them to write data to a table. These database roles are similar to user groups that have predefined permissions assigned to them. When we look at database roles under the security node, we can see these predefined roles.
The DB_owner role is overkill for just inserting, updating, and deleting data; the same can be achieved with the DB_data writer role. The DB_owner role can perform all tasks within a database that don’t require server-level permissions. The DB_access admin role can add and remove access privilege to a database, while the DB_security admin role can add or remove permissions to roles and objects within a database.
The DB_backup operator role does what it says, enabling its members to back up the database. The DB_DDL admin role allows members to run data definition language commands that permit schema changes. DB_deny data reader and DB_deny data writer are the inverses of the data reader and data writer roles.
Every user that can access the database is a member of the public role. Give that role permissions to perform actions that you want every user to be able to do.
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.