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? Let’s take a look, so let’s just break this down: we have authorization permission on securable name to principle, which can be granted, revoked, or denied. This is probably the most complicated, but we’ll just go back on that in just a second. A login, a user, or a role is a secureable that can be an object schema database, and while it is not relevant for Geo SQL databases, it could also be a server principle.
So, for example, you may want to add additional permissions to a custom role, and then there’s a final bit that you can add, which is the grant option, so use caution. Jane is allowing Susan certain permissions. The “grant” option means that Susan will be able to grant those permissions to somebody else. So do be careful with that. Let’s take a look at permissions now, so that tables can be selected for 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 modify data, and you can remove data. You can also have control. Control gives you basically all rights to a table: select Insert, Update, Delete, and References, which means you can view foreign keys and take ownership, so you can change who has ownership of that view. Change Tracking We’ll look at change tracking in a later set of videos before moving on to view definition.
That just means you can right-click on it and go to the script table, as now if the secureable was a schema, you do have a few permissions. But I need to focus on the altar. The scope of Altar on a Scheme is extremely broad. And it’s probably more wide-ranging than you think because it doesn’t just alter the schema. You can also alter, create, or drop any security in that schema. So whatever is in that schema is the equivalent of also being allowed to have, create, alter, and drop tables, views, or whatever. However, you cannot change ownership at the altar. So, among functions and procedures, I believe the most important are alter and execute. Can you run these functions and still procedures? You can also look at change tracking and definitions. Now, it should be noted that with functions and stored procedures, if you have a stored procedure, I will create a stored procedure. So develop a program, which I’ll refer to as a sales-led procedure. And inside that, you have a select, we have a table, and 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, such as a sales letter 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 perform the select function. And this is called ownership chaining. It only checks whether you have the right when it is transferred to a new owner. Now, there is one more permission that I’ve left out, and that is 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 that 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 tables and views mean that you can delete, insert references, select, and update. So you have the main ones. The term “electronic commerce” refers to the sale of goods and services over the internet. For scalar functions, this means execute and references, and for table-valued functions, this means delete, insert references, select, and update. There is one more. As you can see, all does not literally give you all of the functionality of a database. Database gives you access to backup databases, backup logs, create databases, create functions, create procedures, create rules, create tables, and create views. Notice I used the word “create” very deliberately. It does not give you access to drop databases, drop functions, drop procedures, drop rules, drop tables, or drop rules, and it doesn’t give you alter functionality either. So all of this 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 someone else, use Create procedure. So here’s my procedure, and I will then add Execute as and the name of the individual who 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 your login. I do want to highlight one function, and that is Sys FN underscore my permissions. So that will show you what your permissions are. So now that 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, revoke, or deny certain permissions. So it’s whatever the authorization is—grant, revoke, deny—what the permission is on what you’re securing and to whom you’re securing it. And remember, a deny is higher than a grant, and revolt just takes away a grantor deny and replaces it with no comment. What does everybody else think?
7. 114. configure permissions by using custom roles
“There’s a lot of grant revocation and denial,” you may be thinking. And saying I want to give 50 grants to one person and then the same 50 to another, with some revoked and some denied, is a recipe for disaster. I’m going to forget something. And then, when a new table comes along, you’ll have to do it anew. Is there a way to package it up? and the answer is yes. These are fixed database roles, so the permissions they have don’t change, but you can have a custom database role. So what I’m going to do is drop this DB data reader from Susan so that she has access to null tables again. So next I am going to say that 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 a new role. So I’ll 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 I’ll make my own role. 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 give 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 selected object address to my custom role 1. Well, that’s great, but that doesn’t affect Susan. Susan isn’t a member of Custom Role 1. So again, I’m going to use some earlier chords. We’re going to alter a role, and we’re going to add a member to this role a member. So we’ve made a role and added a member to it, but not the member to whom we’ve granted select on an 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 second custom role. So this time I’m going to have the customer customise role two. And I’m going to add this member again, Susan. So it’s the same old story. And here, Susan now has access to two tables. So it simply demonstrates that a member can be a part of more than one role. Let’s add a third role here. So this is custom role number three. And this one is going to revolt select on this object. So by the end of this, will she have access to the customer table? Let’s take 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 grantselect on this object to a fixed customer database role. And the clues in the question indicate that you cannot grant or 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, if you give permissions to the public, be very careful because then everyone has them. So customer data roles are very simple to create; simply create a role, name it, and grant authorization to someone else. And then you can use your grant-denying revocations for a particular role. And then you can alter the role to add or drop members. And because you can do this with roles, this means you can now package up those 50 select grant permissions that we previously said could be in one role. Then you can add members to that single role, and they will all have those specific 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 securab
In this short video, we’ll have a look at how to apply the principle of least privilege to all securables. So what does that mean? Well, users should have the least amount of privilege that is necessary for them to do their job, the least privileged user account (LUA). So, for example, don’t give people DB honour when all they need is a single collect grant. Consider the bare minimum that users require. You can use roles, custom roles, and 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 denial. So a revolt does not grant permissions, but to deny grants no permissions at all. Now, a denial 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 because they 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 that has a select on an underlying table. Then you don’t have to give any permissions to the underlying table, only the stored procedure. Then consider what kind of security you will provide. As an example, suppose you had select permission. Well, I selected 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. The term “electronic commerce” refers to the sale of electronic goods. When you give a select on a table, you only give the select permission on that table. Also, be cautious about granting control as your permission, which includes other permissions such as Auto, select, insert, and update. And, of course, exercise caution when granting permission. It is deprecated. So the principle of least privilege doesn’t give people any more permission than what they actually need to do their job.
