Hey, everyone, welcome back. In this video, part 11 of topic six in our database class, I'm going to further discuss database security with a specific focus on permissions and roles. Let's get started. All right. So again, once someone has authenticated to the database, it is they've done whatever is necessary to prove that they are who they claim to be. At that point, what they are allowed to do in our database processing environment depends on their processing rights. And these are permissions that are specifically granted to or revoked from our various users. Okay. So we define processing rights on a user account basis. This is one way that we do it. So we don't link processing rights to a particular login. We link processing rights to user accounts. And as we'll see a little later, we can also link processing rights to roles in the database. So yeah, I'll speak about that here in a few moments. But for now, we need to talk about a little security philosophy before we move on. And the first point to make here is that we set up processing rights that define who that is which users are allowed to perform which actions in the database. And we may also set up temporal constraints. So when are certain actions allowed to perform? So maybe I don't know like on non work days, I don't want my people to be, I don't want those user accounts to be able to access things. So things like that can be configured. So it's who is permitted to do what and when are they permitted to do it? Okay. Now from a security philosophy standpoint, this is an important consideration. The people or the user accounts that perform these activities, if they are authorized activities, need to accept responsibility for the implications of their actions. So what this means is that if we have granted permission to a user to do something in the database, because it's necessary for their job duties. And as part of that permission, as part of that ability, if they do something that causes a problem, like maybe they delete a bunch of customer data, or they accidentally change everybody's phone number or something like this, it's that person's responsibility. They need to take ownership of that mistake. It's not the fault of the database administrator, what legitimate users do with their legitimate access. So the individuals themselves need to have and take that responsibility. And of course, as we saw earlier, we typically authenticate via username and password, but this is not always the case. Indeed, there are companies out there that their whole raison d'etre, is to provide things like biometric security for enterprise-level databases. So rather than authenticating with the user names and passwords, maybe our actual human users would be required to use a fingerprint or a facial recognition or something similar, or two-factor authentication in order to log into the database environment. Okay, so as I mentioned just a few moments ago, the rights, the privileges, the permissions that we grant in enterprise-level databases can apply to individual users, but they can also apply to something called a database role. Now, roles are extremely useful and time-saving from a security perspective. So the idea here is that while it is possible to assign every individual user permissions to do things, it may be easier to identify roles and then assign the permissions to a role. So for example, in your company, you may have third employees who work in the accounting department. So we could, for example, create like an accounting employee role and then grant access to different data and database objects in our database based on that role. And then when we have users, we assign them as members of the role. So if we hire a new accountant to work in our company, rather than assigning that new person individual privileges saying you can access this table and you can access that table, but you can't access this third table. We instead simply make them a member of the accounting employee role, and in so doing they inherit all of the privileges that have been granted to that role. So hopefully you can start to see how this can be convenient in a multi-user database environment. Maybe we have accounting employees and we want them to be able to do very specific things in the database. And then maybe we have, I don't know, accounting managers who have a higher level of, and maybe they, for example, are allowed to delete data or update data that regular accounting employees are not allowed to delete or update. So in that case, we can assign them an elevated level of privileges. That is, they'll have additional permissions, permissions to do things that may be regular accounting employees cannot do. So the database security model generally then is that we can assign rights or privileges or we can revoke rights and privileges to individual users and to roles. So these permissions then define what a user or what a member of a role is allowed to do in the database. So maybe they can access the employee table but not the customer table or maybe they can access, depending on maybe their customer service people. So they need access to the customer data and the order data but not to the employee data. So we can define these roles and grant permissions and so on and then by assigning users to be members of one or more roles, they will inherit that compilation of permissions. Okay. So for an individual user account for a specific database, that user will have the combination of permissions that have been granted to the user individually as well as to all of the roles for which that user is a member. So that is the user is going to receive the union of all of these permissions that have been granted denied or revoked through their individual permissions or their role permissions. And when a conflict exists, we tend to be conservative in our security thinking. Okay. So if you have access to something as an individual user, but you belong to a role that does not have access to business data or that table, then you will be denied permission to it. So we tend to take these, take a conservative approach to figuring out what permissions have been, or what permissions are allowed for each individual user. So a deny will always take precedence. And so if I have intentionally denied you permission to do something, it doesn't matter. Even if you are in a role that has permission, if I deny you as an individual, you won't be able to access those data or those protected objects. And aside from denies, the general strategy is that role permissions will take precedence over individual permissions. And the reason here is that multiple people can be assigned to the same role. So if there's a conflict between a role and an individual, like if a role is allowed to, I should say if a role is not allowed to do something, an individual is allowed to do something, then the role permission will take precedence because we want to use that as a mechanism of protecting the data, because potentially many, many members could be assigned to the role. So that's going to take precedence over any sorts of individual permissions. All right. So some concepts there. What I like about this slide is it illustrates for us the value of the crow's foot symbols that we learned earlier in our class for modeling things outside of just as tables and relationships. And so we're using these symbols here to create a model of database security. So over here on the right are database objects that we want to secure. So these could be tables in our database. They could be views. They could be specific columns within a table. All right. It's just some kind of secureable. And something that we may want to protect. These are our database objects. And then we have our users and our roles here. And we can see that each user may belong potentially to many roles. So they can be a member of many different roles. And each role potentially can have many different users. All right. Now both users and roles can be assigned permissions. Okay. So and you'll note that this is a one to many. So each role can have many different permissions in the database. Each user can have many different permissions in the database. All right. And it is these permissions that define which objects the user or members of a role are allowed to access. So some examples here. These might be some users, right? Like we might have Eleanor Wu, Richard Ant, etc. These could be individual users. But we can have roles that we define ourselves. Like earlier I was using, I don't know, like an accounting employees role. Or if we're a bank, maybe we have bank tellers, managers, may have higher level privileges. If you're running like a data-driven website, you may have unknown public. This is just they need to have access to certain things, even though it's just some random unknown person out there on the internet. If you navigate out to, I don't know, Amazon or Wikipedia and you do a search for something, there needs to be a way of your request being processed by the database. So we set up like an unknown public role or user account and grant that account certain privileges to do things, hopefully a very limited number of things. And then we see some of these permissions. So here's an example of a role permission. This says anyone who is a member of the accounting role is allowed to update the customer table. So that's an example of a permission that we might assign to a role. Here are some examples of permissions that we might assign to individual users. Okay, so Eleanor Wu may have the permission as an example to run our a particular stored procedure. Like maybe we do financial processing at the end of every month. So we program in all of those tasks into a series of SQL statements and store those in the database in the form of a stored procedure. And we grant Eleanor the ability to run that. Okay. Or maybe James is like a high level user in our database, like the database administrator. So James in this case is given permission to alter the structure of all the tables. That is a very, very high level of permission. There should be very, very few accounts that have that level of access to the database. We don't want people to be adding and dropping columns or tables or relationships unless they really, really, really know what they're doing. So we want to limit that level of access to just a very small number of user accounts. So just to brief over you, a kind of a graphical model of Dbms security there. The combination of users roles, permissions and our secureables or database objects, things that we want to protect. So in SQL server, this roles can be flexible. That is, custom roles that we create ourselves. Or they can be fixed. And this is generally true in enterprise level databases. It's not just a SQL server thing. Right. We can have fixed roles. These are pre-defined roles that come with the database. So as soon as you install the database, you're going to have a set of available fixed roles that you can use for managing security. And then we have our flexible or custom roles. So these are the types of roles that we saw illustrated here on this slide. So we might create like an accounting role or a role for the managers of the shop. So these are custom roles. They don't come with the database as soon as we install it, we would need to create these ourselves. So these are flexible roles in SQL server. But let's take a look at some of the fixed roles. They usually do a pretty good job and have lots of good use cases where we can just apply these pre-existing roles and not have to create a custom role. All right. So the DB owner, the database owner, has a very, very high level of access to the database. It's not as high as a system administrator. But for an individual database, a DB owner, anyone that's assigned to that role can basically do whatever they want. Right. So all configuration and maintenance activities, they can drop the database if they want to. Right. They could delete all the data, delete the tables. They could get rid of the entire database. But we have a role here for people that are allowed to administer permissions on a large corporation. There may be several people that can assign and manage permissions. So maybe we hire new employees and if we have thousands of employees, we probably need at least a few people that have security admin level privileges to assign and permissions to those people. And then you see there are other ones. I won't go into all these makes granting and really access allowed to do database backups. I remember earlier when we were learning about the structured query language, we talked about DDL, the data definition language. So these are things like create tables, create relationships, define columns, data types, default values, null statuses, all these kinds of things are DDL tasks. So someone that has that role would be allowed to do any of those kinds of things in the database. Some of the more common ones that are used quite frequently are things like DB data writer. This is a relatively high level of access. Someone a user who is a member of that role is allowed to basically change all of the data in any database table. Someone who is assigned the DB data reader role is allowed to read data from all of the affected tables so they can't make any changes but they could read it. So if you start thinking about this, maybe a typical user out on, let's say Wikipedia, might be assigned the equivalent of a DB data reader role. So if I'm just an anonymous web user, I might get this and it allows me to read data. So I could read my Wikipedia articles, fetch those data, but I wouldn't be allowed to change anything unless say I create an account or I'm willing to have my IP address logged by their machine. So to get that higher level of access, maybe I have to log in just as an example. I then we also have denies. So we have some basic deny fixed roles here in SQL Server. So we can intentionally deny people the ability to make changes to any data or we can deny people the right to read data in the database. So it's going to be very, very useful. These fixed roles and of course, as we saw earlier, we can define our own custom roles as well. So here are some SQL statements for working with custom roles. This allows us to create defined custom roles. And so I want to make my own role, maybe a role for my accounting employees. I just use the keywords create role and then I specify the name of the role that I would like to create. If I want to get rid of or delete an existing role, I use the drop role syntax along with the name of that database role. And here you can see how we can add users as members of our database roles that we have. So if we have a say a role for our accounting employees and we want to add user Bob to that role, we would use something like this. We would just say ultra role accounting, add member, and then Bob's username. So that would add Bob as a member of that accounting role. And similarly, if we want to remove Bob, so maybe Bob has left the company and we want to ensure that his user account no longer has access to the accounting role, then we could use an ultra role statement like this to remove Bob from that role. So similar to what we've learned before, create drop alter. Just in this case, we're working with roles or users or logins. So I remember that after creating a user account or creating a role, the idea is that we grant that user account or that role permission to do different things in the database. And examples of these permissions being granted are shown here. So we have our four basic DML data manipulation language operations. If you remember, right? So these are our four basic operations in the DML part of the broader structured query language. Select insert update delete. And we can grant permissions to do these various tasks or these various operations at a table level by doing something like this. So if I want to grant a particular role, say my accounting employees, the abilities or read data in the orders table, I might do something like this, grant select orders to accounting employees. And then anybody that is a member of the accounting employees role would have the ability to select data out of the orders table in this example. Any of those same applies to inserts, updates, deletes. These are changing data. So we want to be more careful, just intrinsically, more careful in granting these types of permissions than here. Although this also could be very dangerous if we don't have like encrypted or hashed data in the database. Maybe we store a very sensitive customer information like social security numbers, credit card numbers, etc. and the bank account numbers. And by granting select, if a hacker is able to get access to a user account, that is a member of a role that can select data out of that table, then that hacker may be able to see those sensitive data. So here's an example of how we can grant permission for a role to just a specific column in a table. So if we want a particular role to be able to, I don't know, say the data in a table, but only for a specific column and not the entire table, then we would do something like this. So you would say grant update and then inside parentheses, a comma separated list of the names of the columns that you would like that role to have, for which you would like that role to have update permission. And the same applies. I just showed an example here of update, but if you wanted to do the same thing with, say, user accounts rather than roles, you would just change that there. So we have the ability to update specific columns by using this kind of syntax. Cool. So here's permission for users. On this slide, this previous slide, we were looking at roles. On this slide, we're looking at user. You'll notice that it's basically the same, except that we use the user names rather than column names. So instead of putting, or I should say a role name, so we put user names in here rather than role names in order to grant permissions. So here are our four basic DML operations again, select insert update delete. If we want to assign those to specific users, we could do so here by using this type of SQL syntax. If we want to do a deny explicitly prevent a user from doing something, in this case, deleting data, we could do that with a deny statement. So deny, at the type of operation, we want to delay to deny, in this case a delete, and then the database object, like a table name to whatever user we want to deny those permissions to. So this is that we says that a user is not allowed to do something. And if you remember, from a while back here, we said that denies always take precedence. So if we're denying something that will always be honored by the database security model, even if their, even if a user, for example, is a member of a role that ordinarily would have permission to something, if we deny the user permission, then they will not have access to it. Because the deny will always take precedence. All right, so that should be enough SQL for to get you through your basic operations of managing security as a database administrator, but just a few additional guidelines, just friendly advice for protecting data. And that is, hey, be sure to run your database behind a firewall. You don't want random people from outside of your organizational boundary to be able to gain access to the DBMS where they could potentially, for example, brute force their way into getting inside the database management system. You want to keep your DBMS up to date and the operating system up on which the server is running. So keep all those patches up to date so that any newly identified security holes will be fixed as quickly as possible. And again, remember that principle of least privilege, right, that we want to apply that to individual users. All right, so manage you can access the data and what they're allowed to access very carefully, physically protect the database servers, protect them so that no one will just spill their coffee on it or her but locks on the doors have a fire suppression system, these types of physical protections in place and also limit the functionality of the enterprise level database management system only to the needed features. The modern enterprise DBMS's are vast software programs and your company probably doesn't need to use all of its various functions. So we should disable all the functions that we don't need since they potentially represent an attack vector for a malicious party.