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

5. 25, 113. configure security principals – individual permissions

In the previous video we had a look at Grant Revoke and deny for individual permissions but what are these individual permissions that you can use? Well, let’s have a look. So let’s just break this down. We have got authorization permission on securable name to principle. So authorization, well that can be Grant Revoke or deny the permission. This is probably the most complicated bit, we’ll just go back on that in just a second. The securable that can be an object schema database and while it’s not relevant for a zero SQL database, it could also be server principle is a login, a user or a role.

So you might have a role that you want to add additional permissions to a custom role and then there’s a final bit which you can add and that is with Grant option, be very careful jane is allowing Susan certain permissions. The with Grant option means that Susan will be able to grant those permissions to somebody else so do be careful with that. So now let’s look at permissions. So permissions for tables can be select, Insert, Update and delete.

Those are your main permissions. Now there are actually more permissions as well but I just want to concentrate on those. So you can read data, you can add data, you can amend data and you can remove data. You can also have control. Control gives you basically all rights to a table so select Insert, Update, Delete and references means you can view foreign keys, take ownership so you can change who has ownership of that view. Change tracking we’ll have a look at change tracking in a later set of videos and then view definition. That just means you can right and click on it and go to script table as now if the securable was a schema then you do have a few permissions but I want to concentrate on altar. Altar on a scheme is very wide ranging and it’s probably more wide ranging than you just looks at because it doesn’t just alter the schema.

You can also alter, create or drop any securable in that schema so whatever is in that schema is the equivalent of also being allowed to have create, alter and drop tables or views or whatever but you can’t change order shape just from the altar. So with functions and still procedures then I think the main one is alter and Execute. Can you run these functions and still procedures? You can also view change tracking and view definitions as well.

Now it should be noted that with functions and stored procedures that if you have a stored procedure so I will create a stored procedure so create prog and I’ll call it salesltprocure and inside that you have a select and we have a table you’ve got execute permissions on the stored procedure.

So yes, you can execute the stored procedure but you do not have select on the table. So can you actually run this stored procedure? And the answer is yes, because as soon as you’re going to a procedure so sales Lt or a function, then you will be able to read anything from that schema and more specifically anything by the same owner.

So having execute rights on the stored procedure will allow you to execute the stored procedure insofar as you still have the same owner. So even though you don’t have any select rights, you can still use the stored procedure which will do the select function. And this is called ownership chaining. It only rechecks whether you have got the right when it goes into a different owner. Now, there is one more permission that I’ve left out and that is the all permission. But it’s not as easy as it sounds. And we’ll look at that plus a function that we can use to find out what permissions we have in the next video.

6. 25, 113. configure security principals – the ALL permission

Now there is one more permission which I haven’t gone into and it’s very wide ranging, it’s deprecated. So that means Microsoft is saying it will be removed at some point. It is maintained for just backward compatibility and that is all. All for tables and views means that you can delete, insert references, select and update. So you have the main ones. But notice you haven’t got some things like take ownership for procedures then all means execute. For scalar functions then all means execute and references and for table valued functions all means delete, insert references, select and update. There is one more.

So you can see all doesn’t literally give you all of the functionality and that’s a database. Database allows you access to backup database, backup log, create database, create function, create procedure, create rule, create table and create view. Notice I put the word create very deliberately. It does not give you access to drop database, drop function, drop procedure, drop rule, drop table, drop rule and it doesn’t give you alter functionality either. So all is a bit misleading and I can see why it is deprecated. It’s no longer really the case. Now if you want to run a stored procedure as somebody else then you can create it using Create procedure.

So here’s my procedure and I will then add with Execute as and the name of the individual so that will give me whatever Susan’s permissions are. You may also be able to just execute the command Execute as user equals and put the username in. If you are in a managed instance or virtual machine you could also put in Execute as login. There is one function that I do want to point out and that is Sys FN underscore my permissions. So that will show you what your permissions are. So here we have the logging permissions, we can connect and select. Here we have the user permissions for a particular database and here we have the permissions for a particular object. In this case it’s a table called customer and you can see that we’ve got select permissions there. So that’s one well worth knowing. So in this video we’ve had a look at how we can grant or revoke or deny certain permissions. So it’s whatever the authorization is grant, revoke, deny what the permission is on what you’re securing to and who you’re securing it to. And remember, a deny is higher than a grant and revolt just takes away a grant or deny, just replaces it with no comment. What does everybody else think?

7. 114. configure permissions by using custom roles

Now you might be going, there’s a lot of grant revoke, deny. And if I say want to try and grant 50 grants to one person and then the same 50 to another, and the same 50 to another, maybe some revokes and maybe some denies, that is going to be a recipe for disaster. I’m going to forget something. And then when a new table comes along, you’ll have to do it afresh. Is there a way to package it up? And the answer is yes. These are fixed database roles, so what permissions they have doesn’t change, but you can have a custom database role. So what I’m going to do is I’m going to drop this DB data reader from Susan so that Susan now has access to null tables again.

So next I am going to say I want to grant, so I’m going to use a grant that I’ve done before. So I want to grant this select object. So I’m just going to move it down here so it’s a bit clearer. So I’m going to grant the select object, but I’m not going to grant it to Susan. I want to grant it to a new role. So I’m going to call it my custom role one. Now, before I can do that, I need to create that role and I do that with create role. Fairly simple. So create role my custom role one. Now, who is the person who’s going to own this? Or by default, it’s the current user. If you want somebody else to own it, then you can say the authorization and give a particular owner’s name.

So I’m just going to create this role myself. I’m going to be the user, so I’m going to be the owner. And now I’m going to grant this select on object address to my custom role one. Well, that’s great, but that doesn’t affect Susan. Susan isn’t a member of custom role one. So again, I’m going to use some earlier chord. We’re going to alter a role and we’re going to add to this role a member. So we’ve created a role, we’ve added a member to that role and then to the role, but not the member we have granted select on object. Does it filter through to the individual members? So if I refresh this, you can see yes, it does.

There it is. So let’s do exactly the same thing to another role. So this is my custom role two. So this time I’m going to have the customer to custom role two. And I’m going to add this member again, Susan. So same things again. And here, Susan now has access to two tables. So it just shows that a member can be a member of more than one role. Let’s add a third role here. So this is custom role three. And this one is going to give a revolt select on this object. So by the end of this, will she have access to the customer table. Let’s have a look. And the answer is no, she doesn’t, because the Revolt gets rid of this grant. So it’s like we’re saying to Susan all of these things grant select, grant select, revoke select. Now, just in case it’s not clear, can we alter a particular role to add a particular permission?

So let’s have this grant and let’s have a grant select on this object to a fixed customer database role. And the clues in the question you cannot grant deny Revolt permissions to or from special roles. Now, the one exception is the public role, but this is the one that everybody has. So be very careful if you are doing any permissions to the public role because then everybody has them. So customer data roles, so very easy to create, create role, name of role and you can have authorization to somebody else. And then you can use your grant denying Revokes to a particular role. And then you can alter the role to add or drop members. And because you can do this to roles, this means you can now package up those 50 select grant permissions that we previously said that could be in one role. And then you can add members into that one role and then they would all have those select permissions. And then when you expand the database, adding additional tables that need a grant, instead of having to go through all of those individual members, you just alter the role and then you can grant the additional permissions that you need to the role and then it will be cascaded through to its members.

8. 27. apply principle of least privilege for all securables

In this short video we’ll have a look at how to apply the principle of least privilege for all securables. So what does that mean? Well, users should have the least privilege that is necessary for them to do their job, the least privileged user account lua. So in other words, don’t give people DB honor when all they need is a single select grant for instance. So have a think about the minimum that users need. You can use roles, custom roles and also the fixed database roles.

So you can assign permissions to the roles and then assign users to the roles. This makes security administration more easy. Have a think about whether you should use a revolt or a deny. So a Revolt doesn’t give permissions, but to deny says okay, no permissions whatsoever. Now, a deny doesn’t actually apply to object owners otherwise even the owner wouldn’t have access to a particular object. So don’t put denies in the public role, otherwise very few people will actually have this permission. Now, maybe you can prevent users from querying tables for instance directly by allowing access to views, procedures or functions. In other words, don’t give rights to the underlying objects have a level of abstraction.

So in other words, have views instead of access to tables, have stored procedures or functions and don’t forget objects can be chained together. So you could have a stored procedure which has a select on an underlying table. Then you don’t have to give any permissions to the underlying table, only the stored procedure. And then have a think as to what the securable you are going to give. For example, let’s say you had a select permission.

Well, I select permission on a database that would include all child schemas and all tables and views. If you have select permissions on a schema, that gives you select permissions on all of the tables and views. Unless of course you’ve got to deny. If you give a select on a table then that gives the select permission on that one table only. And do be careful about giving control as your permission that gives ownership like permissions and include other permissions such as Auto, Select, Insert and Update. And of course do be careful out giving the all permission. It is deprecated. So the principle of least privilege don’t give people any more permission than what they actually need to do their job.

• Category: Uncategorized