DP-203 Data Engineering on Microsoft Azure – Design and Implement Data Security Part 4

11. Lab – Azure Synapse – Azure AD Authentication – Creating a user

Now in the last chapter we had seen how to define a SQL Active Directory admin when it comes to Azure Synapse. Now in this chapter, let’s create a new user in Azure Active Directory and give that user access onto our dedicated SQL pool. So here I’ll create a new user. So I’ll hit on create, user. I’ll scroll down, I’ll give a username and put the same name. Here. I’ll scroll down and specify my own password.

Please note that based on the password policies that is inbuilt in Azure Active Directory, when a user logs in for the first time, we will need to change our password. Here. I’ll just hit on create. So here, if you search for your users, we have our user in place. This user can now log into Azure, into your Azure account, right? Using this particular user name, obviously this user can’t access any resource in Azure because we have to give specific permissions for this user.

This is just when it comes to permissions for users in Azure Active Directory. In this particular chapter, we are trying to give this user access onto a dedicated SQL pool. So here I need to go onto SQL Server Management Studio, onto the session wherein I am logged in as the Active Directory admin for my synapse workspace. I’ll then go on to my new pool. I’ll right click and choose New query. Now I’ll take these statements, I’ll copy it, I’ll place it here. So now I am creating a user in my new pool database. This is the entire user ID just copied from as your Active Directory. And here we have to mention the phrase from external provider. This indicates that this login details for this user is coming in from an external provider. And our external provider is Azure Active Directory. Here it is giving a default schema.

Then I am creating a specific role here. In this specific role, I am giving the select on permissions for the DBA schema on to this role. So here I am giving now specific permissions to this user. Because even if you create a user by default, they won’t have access to the tables in a dedicated SQL pool. For SQL databases. One thing is having your user define and next is giving permissions. As with any system, you have a users and you have your permissions. Next, once I have the role in place, I’m executing a stored procedure which allows me now to add my user on to this particular role. And then we need to log in with that new user and then we can perform the statement of select star from DB customer. So, just looking at one of our tables, so I will need to run this in a different session. So, let me just delete this statement. First of all, let me create my user.

So this is done, I’ll create my role. Next, I’ll give permissions for my user onto this role. So this is also done. Now let’s login has this new user. So I’ll go on to the object explorer. I’ll hit on connect database engine. Here I’ll put the username now in the options. I have to go on to the additional connection parameters. I need to go on to Database and mention what is my database name. Now, the reason I am doing this is because there is also system databases in place, which is normal for an Azure SQL database, for a SQL Server instance, even for Azure Synapse. And we have not given permissions to our user for that master database for the system databases. So when you try to connect hash this user, it will also try to connect onto the system databases, which is not allowed at this point in time because we have not given permissions here. We have only given permissions as per our SQL statements onto our database.

That is new Pool, because we had run our query based on the context of new pool. So here I’ll now hit on Connect. I have to enter my password and I told you I will be prompted now to change my password. I’ll hit on sign in. So now we should be connected. Please know that when you’re creating a user in as your Active Directory, right, you can specify your own password for the user, but there are some password constraints. So you’ll come to know about that when you define the password. Now here I can go on to my databases. I can see my new pool database.

Now, I can’t see the tables in this particular view, and that’s because I don’t have access onto the system databases. But if I right click and if I hit on new query, if I take the statement to read data from our customer table, I can see that I have permission to read the data. So now, instead of defining a separate SQL login and then a SQL user based on that login, we are now using a login based on Azure Active Directory.

So again, remember that in a SQL database or in Azure Synapse, it is based on that same sort of model. When it comes to security, the first thing that you have defined is a new login and then based on that login, you create a new user. But here our login is defined in Azure Active Directory. So now we are making that as our identity store to log into a Zok synapse in a dedicated SQL pool.

12. Lab – Azure Synapse – Row-Level Security

Now, when it comes to the tables in your dedicated SQL pool in Azure Synapse, you have something known as row level security and another aspect that is known as column level security. In this chapter we will go through row level security. And after you implement this lab, you will understand the benefit of rowlevel security. The first thing I’m going to do is to create a new table. So this table has order information, it has the order ID, agent course and quantity. So here are the different data types. So in SQL Server Management studio I’ll right click and hit on new query for my dedicated SQL pool. This is being done as the SQL admin user. And here let me see if I already actually have an orders table in place. Just want to confirm, right, so this is based on one of our earlier laps. So what I’ll do is that let me drop this table. Now let me recreate this table of orders.

Next I’m going to insert some data into this table. So here you can see that when it comes to the agent here I am assuming that there are different agents that are taking the orders from the various customers based on the courses. So here I have two distinct agents, agent A and agent B. Let me run this so that we have some data in this table.

So now if I do a select star, I can see I have my data in place. Now I’m going to create three database users again with no logins required. So one is the, let’s say, supervisor who asks the permission to read all of the data in the table. And let’s say now you want user agent A to only read those rows where the value is agent A for the agent column. So you want this user to only read their rows and agent B to only read their rows from this particular table. And that’s what we are trying to achieve when it comes to row level security.

So based on the user, that user should have the permissions to only select their rows. So firstly I’ll create the users. Then first I’ll grant the normal select permission on the table to all of these users. Now, once this is done, we need to create something known as a function. This function is going to implement the logic which will be used for row level security. We are going to first create a new schema that will encapsulate this particular function and then we’ll create the function itself. So let me copy this. So first I’ll create the schema and here we are creating a function. The name is Security Predicate. This is our schema. Here in the function it’s taking the agent has the parameter and then what this function does, based on the agent that is being passed has the parameter. It will only return the rows where the agent is equal to the username.

Or if the username is just equal to supervisor. Then return all of the rows. Again, we are not going into details about the function itself in row level security. You should understand the steps that are involved in implementing row level security. Next, we need to now create a security policy on that particular table and ensure that the state is on. So here we are mentioning now what is the function that needs to be used. And here we are passing agent. Agent agent is nothing but the column name in our table. So let me create the security policy. So first I think I forgot to create the function itself. So let me do that first and then let me create the security policy.

Now next I need to ensure to also grant access on to the users onto the function itself once this is done. Now let me take all of these commands. So first we are going to execute this command. Now in the security context of user that’s agent A. So first let me execute this statement. And now let me do a select star. And now you can see only the rows of agent A. So now the user is actually being passed on to a function and now only the rows that belong to that particular agent are then returned. Now I can use the revert statement to revert control back onto the SQL admin user.

We can do the same thing for agent B. Here again select star. We can see only agent B details. I can do a revert. Now we can work has the supervisor. And now we should see all of the rows working as desired. Again I’ll ensure to revert back on to the SQL admin user. In this chapter I just want to show you how you can perform the step by step implementation when it comes to row level security.

13. Lab – Azure Synapse – Column-Level Security

Now, in the last chapter we had looked at row level security. Now in this chapter we will look at column level security. Before that, if you want to do a clean up of what we have created in the last chapter, what you can do is that you can first drop the users, then drop the security policy, drop the table, drop the function and then drop the schema. So I’ll take all of this, let me just replace it here, hit on execute. So all of this is done. Now let’s look at implementing column level security. So first I’ll again create the table. I’ll insert some data into the table and also let’s create two users now. So first I’ll insert the rows and then create the users. So I already have a user A.

What I’ll do. I’ll define user b. So this is also done. So let me change that here and also let’s ensure change it here as well. So let’s grant access onto our users onto the orders table. But here we are making a slight change for the supervisor, I am granting the select access onto the entire table. But here, when it comes to user B, I am only granting access to see the order ID, the course and the quantity. So if you want to implement column level security, you will use the grant statement. So now again, let’s take all of this and execute them as individual users.

So first has user B, execute as user B. Now, if I do a select star from orders here, you can see we are getting an error and that’s because the user is not allowed to select the data in the agent column. So here I will need to only specifically take these rows so I can execute the statement. And now it works. If we revert, control and execute has the supervisor and now select star, this should work because there are no constraints for this particular user.

So again, if you want to do a cleanup, we can go ahead and do that. Yes, we just need to drop the users and drop the table. So I think we first need to do a revert. If that has not been done right now we can drop the users and the table. So initially it was running in the context of the supervisor and not the SQL admin user, right? So in this chapter we have looked at column level security when it comes to tables in your dedicated SQL pool.

• Category: Uncategorized