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.
The public role is a good segue into the principle of least privilege. The least privileged user account model or approach is a simple idea but with far-reaching ramifications. It is the user account equivalent of the “need to know” concept often quoted in spy movies. Users should only have the bare minimum access and privileges they need to perform their functions; any more is unnecessary and a potential security risk.
Let’s think of two different scenarios. We have an organization with a thousand users, most of which just need to read data and maybe update a couple of tables. A couple of system administrators and a handful of users need the ability to change and modify a greater number of tables and objects in the database.
In one scenario, the database administrator doesn’t know which users need what kind of access and can’t be bothered setting up different access levels for different users, so he makes everyone a database owner with full access to read and change anything.
In the other scenario, the principle of least privilege is applied to all users. Users who are system admins that can do anything. A handful of users have elevated privileges on the database tables and objects they need for their job, the rest of the users have read, insert, and update rights only on the tables they need to.
In both cases, all things being equal, the attack surface is the same: a thousand users in terms of passwords being cracked or a login being breached in some other way. However, in the first scenario, the hacker will strike the jackpot no matter which login he breaches.
In contrast, in the second scenario, the hacker will need to breach two specific logins out of 1000 to strike the complete access and control jackpot. In both scenarios, I am assuming that the attack is from an external source, but that is often not the case. Data breaches, whether intentional or otherwise, often originate from within an organization. This makes the principle of least privilege access and only allows users to see and modify only what they need to crucially important.
Yes, it does take more work to set up access based on job roles, and that is why the database role is so vital as it allows you to tailor access and permissions to the most granular level and then assign multiple users to that database role. Let’s look at how we can use permissions on database objects to customize role access.
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.