DP-300 Microsoft Azure Database – Configure database AuthN and AuthZ by using platform and database tools Part 2

3. 25, 113. configure security principals – roles

In the previous video, we solved the authentication problem of adding Susan to my database. But now I can’t see any tables. Well, maybe it’s just a mistake. Maybe it’s just graphically not there. So I’ll rightandclick on here and go to new query. So this is in my Susan persona, and I’ll just go select Star and I’m going to choose this one sales Lt dot address. Okay, no, the select permission was denied on this object. So not only can I not see it in my tree view, I’ve got no permissions.

Okay, so what can I do? Well, one option is to add Susan to a role. So if I click on roles here in the database roles, you can see that there are lots of different types of roles, and it’s very important that you know what they mean for the exam. DP 300. So DB Owner database Owner this is a fixed database role with most configuration, without all configuration, most maintenance activities in Azure SQL database.

So activities may require other permissions. Now this includes the Drop database permission. So you can see really important. So I am going to add this member to this role. So what I need to do is alter role DB honor and add this member. Okay, so that’s done. And now if I refresh this database with Susan’s login, you can see that we now have access to all of the databases. And if I run this query now, you can see we can do that. Now if I want to remove her, then I drop the member.

So. DB honor very dangerous. It gives far too many permissions, unless you need to do so. So maybe I can add something a bit lower than all of that. And you can see if I refresh this, all of those tables disappear. So what do the other roles do? Well, there is security admin, you can modify role memberships for custom roles only, and you can manage permissions. Okay, that doesn’t sound too bad. Big warning on this one. Because you can elevate your own permissions. Because you can manage permissions, you can manage your own, and therefore you can add additional permissions to yourself.

So this is another one to be careful, these bottom two. So DB Access admin, you can add and remove access to the database for loggings and groups. DB backup operator. This is not applicable in Azure SQL database. It’s there just for compatibility purposes. You can back up the database, but manage instance and virtual machines. DDL admin, you can run a DDL command. So we’re talking things like Create, Alter, Drop DB data Reader you can read all data from all user tables and views. That sounds better. So if I add that and let’s have a look, then again, I have access to everything.

So if I select Star brilliant, maybe I want to add an extra item. So if I do that, so I’ve got a primary key there. I can right and click Edit top 200 rows. Here we go. And I will add an extra item at the bottom. Everything is read only. Oh, I can’t do that because I have been given data reader. If I wanted to be able to do that, then I would have to also be given Data Writer. So that is the ability to add, delete, or change data in user tables and all user tables at that.

So now you can see it’s not saying Read Only. Now the opposite of this DB data writer is DB deny data writer. And there’s also a deny data reader. Now, that means that you cannot read old data. You cannot add, delete, change data in old user tables. Now, suppose I didn’t want this, so I will drop this member and I will drop this member from the reader. So now again, I can’t do anything, none of the tables I have access to. So what you might be wondering is, is there something a bit more granular that you can do? So for instance, maybe I want somebody to have access to one particular table. Well, that is indeed possible, but not with these fixed database roles.

Public, by the way, is everybody. So when you get access to the database, you have access to the public database role, which gives you absolutely nothing. Now, what’s additional in Azure SQL database, and if you are used to these in the on prem version, is that there are two additional database roles, but you can’t see them in here. That’s because they only exist in the Master database. So Master database has a lot of system tables and views and that sort of thing.

So if I go into security and roles and database roles, you can see that there are two additional items. DB Manager you can create or delete databases so it will connect as the database owner, the DB or then Login Manager, you can create or delete Logins in the Master database. So that’s the equivalent of the security admin on an on prem SQL Server. But this is for the Master database. Now you can get a list of all of the database roles with the still procedure Help role. So SP underscore Help Role, and that gives you this list.

Now, there are also role based access control in Azure, so that’s usually abbreviated to RBAC role Based Access Control. And if you’re going to the I am, that’s the access control. You can add role assignments. And there are three role assignments I particularly want to talk about. So SQL DB Contributor an SQL Server contributor allows you to manage SQL databases and allows you to manage SQL servers and databases, but you don’t actually have access to the contents of the databases themselves.

You can’t also manage their security related policies. That would be the SQL Security Manager. So you can manage the security related policies on Azure of SQL servers and databases, but not actually have access to them. So in other words, you don’t have access to this sort of thing. Incidentally, while deploying your databases, then what Azure does is uses something called a server admin. So you may hear that term is a principle in Azure SQL Database.

Now, if you’re wondering when I’m going to be mentioning server wide login permissions, well, I’m not going to do so with regard to Azure SQL Database because you don’t actually have access to the underlying server. So we’ll be talking about that when we’re looking at managed instances and virtual machines. So this is how you can configure security principles with regard to roles. So alter role, name of role, and then add or drop member and the name of member. So we’ve got DB owner and DB security admin, which you really need to pay special attention to and not give it to anybody where you can give it something get letter. And then we’ve Got data Reader, data Writer denying data reader denying Data writer. DDL Admin So running DDL commands access admin, add or remove access to database for logins and groups and then backup operator, which is not really applicable in the Azure SQL Database.

4. 25, 113. configure security principals – GRANT/REVOKE/DENY

Now, suppose we wanted Susan to have access to one particular table, but not all of them, like data reader would give you. So let’s say we wanted sales lt address. Well, we can do this. Instead of using roles, we can just say, well, I want you to grant this particular permission so we can do that with grant. So I want to grant the select permission on an object and then two colons. So it’s this particular object. So I’m granting it two and I’m granting it to Susan.

So if I run that and notice there’s no tables that we can see, no user tables, but now that I’ve run that, we now have access to one user table, the exact one that I’ve just been added to. So if I go into here, I can now run this select statement so I can’t run this select statement on any other table, just doesn’t work. Select permission was denied on the object.

Now, it’s a bit confusing this, because it’s not actually true. And I’m looking at the word denied. How I would write this is the select permission was not granted. So how can I say, okay, I don’t want to grant this permission, I want to do the reverse. Well, that will be revoke. Revoke, select on object. So now, if I do this, now if I refresh, I can’t see this table anymore and this execute doesn’t work. But what does deny do? Deny means definitely not. So let’s just go through this again. Grant means yes. Revolt means remove the yes.

And deny means no. And a no is higher than a grant. So if I have, for instance, a security rule, maybe I’ve got a security role where I’ve got the data reader. So let’s add to this data reader so that will get me everything. So refresh this. So Susan should have access to everything. If I now say deny this select, then I have got a role that says yes, granted, and I have got a deny that says no. So what happens? I can no longer select it and this time it’s true. It has been denied. Now, you’ll see this error message, when it’s been denied or when it’s currently in the state of revoke, revoke just means somewhere in the middle. How do I get rid of a deny? I revoke it.

So revoke just means no permissions. It doesn’t mean a absolutely no to permissions. It’s basically the same as no comment. So it removes yeses, it removes nos, but only insofar as you have got the direct permissions. If you’ve got the permission from something else, like for instance, I’ve been given the role DB data reader, then revoke won’t actually override a role, deny overrides grants and any other permissions. So now I’ve got access to it because even though I have got a no comment, I have it through this particular role. So this is the principle of how you can grant deny or revoke permissions. In the next video, we’re going to have a look at what those permissions can be.

• Category: Uncategorized