Hey, everyone, welcome back. In this video, part 10 of topic six in our database class, I'm going to provide an introduction to database security. Now, database security is one of the three major database administration tasks that we'll investigate. The reason why it's classified as one of these major database administration tasks, is because data have so much value to these modern organizations. And therefore, it's a major responsibility of database administrators to secure those data to protect them. Because there are a lot of people out there in the world that if those data were left unprotected would happily steal them. And it is an unfortunate reality that this is the nature of the world in which we live. But it is it is true. So I don't know. I don't know what that says about humanity. Right? If you don't secure your stuff, in this case, your data, someone else will come along and steal it from you. And they will do so to try to make their own lives better. Right? To try to, I don't know, improve the speed or the ability of their company to compete. Right. And they, it's just, it's just sad that they don't recognize that they are hurting other human beings of someone breaks into a retailer's database and steals your credit card information. They are not seeing you as a human being. Right. They can sell that to somebody on the dark web or they can use it themselves to apply fraudulent charges to your credit card, which may have effects on your financial situation or your credit. And they simply don't care that they're hurting another human being. And I personally find that to be a very sad thing. It's a linked, I think, to the simple fact that there aren't enough resources in this world for everyone to live the kinds of lives that they would prefer to live. So essentially, if you have something valuable and you choose not to protect it, you need to live with the consequences of that. And this philosophy certainly holds true in the context of database security. So we as individuals and as database administrators who are charged with protecting our company's valuable data, do not want to find ourselves in the spotlight or in a news story about yet another big company, yet another retailer whose customer data was stolen by hackers or other malicious parties. We hear these stories all the time. And when they happen, it is almost always because there was some kind of mistake made in securing the data or lack of attention to security practices and principles. So I'm going to spend a little time talking about database security as we don't want to find ourselves in that situation. Now the broad model of or at least one depiction of database security is shown here and it gives us a sense of how data are protected. Now I want to start over here on the right because this is the database where databases that we're trying to protect, right? These are the valuable data. This is the, I don't know, the big pot of gold, right? This is the stuff that has the value and we need to protect this. And if you recall, one of the ways that we seek to protect the data is by isolating all of the data from any sort of users or application programs that may want to gain access to those data. So our first layer of protection to secure our data just comes in the way that we channel all requests for data through the DBM S. Now if you notice the databases here, right? And as I mentioned earlier, when we began our investigation of this topic, none of the applications or users that might want access to the data can access those data directly. They all need to go through the DBM S. So what this means then is that the DBM S kind of serves as a layer of protection for our data, right? To get to the data, we must go through the DBM S. So we have that natural, that natural boundary there. However, as you can see here in this graphical representation of the database processing environment, there are many different types of application programs and requests that have legitimate access to the DBM S. Okay, so you as a user might just type some SQL query, select something from somewhere, and that goes to the DBM S and going to send requests to the database and so on. Or it might be our company's web page or maybe we have like a, I don't know, an Android application written in Java that needs to work with our database. But the point is that each of these connections along here represents an area where data potentially could be stolen in transit. So data, our valuable data could be stolen as they come out of the DBM S and are being say returned to an Android app or returned to our website. Or we could have security holes in these applications. All right, so the application programs themselves could have security flaws in their underlying source code that would allow the valuable data to be obtained or exploited by malicious party. So even if we secure the channels through which the data are communicated between the DBM S and these other application programs that are out there, it may still be possible for malicious parties to access those data by capitalizing on laws in the source code for, say, our web applications, internal desktop applications. If we have some like internal apps that we use, or maybe our Android or iOS apps that our customers or employees use on their mobile devices. So lots of potential security flaws, but from the database perspective, all we can try to do is secure this, the best that we can. All right, so we want to put a layer of protection around this, the strongest layer of protection that we can. And then we have to trust in our software developers and so on in order to try to further secure the data out here. But as database administrators, we want to make sure that it's not our fault if something goes wrong, which means we need to be interested in securing this. Because by doing so, we will also be securing this, which in turn protects our very valuable assets. So that's just philosophically what we're up to when we're thinking about database security on a, from a high level. So turning back here, let's take a look now at this depiction of a database security model. So we have our users over here on the left. And remember, users could be human beings. They also can be application programs, data driven websites, mobile apps, whatever it may be. I did see that wants the data and users need to go through an authentication process. And that authentication from a security perspective is the process through which a user attempts to convince a system that they are who they say they are. So for now and for many, many decades, the most common type of authentication mechanism that's out there has been login credentials. All right, so you have a login name, you have a password. And by providing those two pieces of information, a login name with its matching password, that has traditionally been sufficient to accept the user as legitimate. So if somebody comes along and types a name, legitimate login name with a matching password, then that is enough to convince the system that this person or this user is who they claim to be. Whether or not that is actually true is obviously debatable. Right. You could give me your login name and password to a database or to your email or whatever. And I could use that to gain access to that protected resource, but as far as the system is is concerned, it would be you. Right, because it's only way of authenticating you based on this type of authentication procedure is to check the login name and password that were provided. Naturally, we developed new technologies in the past of several decades. And those new authentication technologies are becoming more and more popular. Many of us, maybe even most of us now no longer use login names or passwords, for example, to access your mobile devices. All kinds of different authentication mechanisms have been developed. It could be a fingerprint scanner. It could be a facial recognition based on any number of technologies, iris patterns. Right. We have other ones that you can use like retinal scanners, other types of biometrics. So biometric authentication has started to become very, very common out there. And it's possible that it may one day supplant login names and passwords as the most common. But the challenge here is remembering that our users may not necessarily be human beings. So they are not going to have faces to recognize. They are not going to have fingerprints to recognize. So we need a way, minimally, to maintain a way for application programs, websites, mobile apps, etc. to authenticate to the database. So we have this kind of layer of security here. That is the authentication layer. And so assuming a person is able to authenticate, then we have another layer of protection. And that is this authorization layer. And so once a person has authenticated to the system, to the database in this case, that doesn't mean that they can do whatever they want. They have gained access to the system, but what they are allowed to do or not do in the system is determined by their privileges. Or the permissions that they have been assigned, that they've been granted or that have been revoked. And so I can intentionally deny users permissions as well. So this layer of security represents what users are authorized or are allowed to do. So for example, you may have users that have different roles in your company. And you may not want every user, for example, to have the ability to delete customers. Or to modify or see sensitive customer information that's stored in the database. So we, as database administrators, define through a set of permissions, what each user is allowed to do in the data. And the general principle here with this layer, this authorization layer of security, is a principle of least privilege. And what that means is that we want to grant our users the minimum level of access that they need to do their jobs or perform their tasks. And nothing more. Okay, so we want them to have the absolute minimum level of access that they need to carry out their work tasks or perform whatever sorts of legitimate requests they may need to perform. But we do not want them to have any permissions beyond that minimum. And in this way, we can try to protect our valuable sensitive data over here on the right as well as we can. So layers of security is the general model. All right, we have a layer here. We have a layer here. We may have additional layers. Like if we assume that this is some kind of communications network, as someone may need to go through, say, a VPN to get through our corporate firewall, which may be like a layer here. So layers and layers of security, if you'd like to philosophically understand this, you can think of it as like the underlying philosophy is similar to, say, a castle in the medieval world or a fortress or a walled city. And if you think about, think of a typical castle in its different layers of protection, right? First, it would be constructed typically in a place that was not easily accessible by potential enemies or attackers. So I might put it on the top of a tall hill, or I might position it on an island, or I might position it on a cliff, right? Somewhere where the geographic location provides me with some layer of protection, I then might construct my castle with tall walls built out of stone that make it difficult for someone to get inside unless they have permission to come inside. So there's another layer of protection. I might have a body of water outside like a moat that protects them from gaining easy access to the walls, so there I have another layer of protection. And then I may have soldiers or archers inside the castle to further protect it against any sort of attack. So layers and layers and layers of protection, and collectively the idea is, if I have enough layers in place, the chances of someone getting through in order to grab my most valuable assets are very, very low. So this is the underlying philosophy of computer security in general and security in particular. We want to have layers of protection in place. So with respect to our authentication in the database world, we create a user log-ins, so we can have a login. And what we see here on this slide are the SQL statements that we can use, just sample SQL statements that we can use to create and drop its users. So what we do is I'll start with log-ins and note that a login is different from a database user. So a database user is a user account for a specific database on a database server. So you can, for example, that in SQL Server, you can go out there and create several different databases. Each of those databases can have different users on them, but we can have log-ins, which allow people access into the server, the database server environment, in the first place. Okay, so I can have login credentials. And once I have access to the database management system, I then can associate those login credentials with different user accounts on different databases. So you can almost think of it as a one to many relationship. We may have log-ins and then we may have different user accounts on different databases. So these may be our login. And this could be, I don't know, user one, maybe on database one, user two, and so on. So let's come up one to many relationship in this way. So you can think of it as each login can potentially be associated with many user accounts. So let's see how we create these log-ins and users in using a structured query language. You see this here. If we want to create or drop a login, we can use these, this type of example syntax. Right. So we use the keywords create login. We then specify the login name and you specify the password. Right. So here's your password. The password is enclosed inside single quotes. And if you want to drop a login, we simply simply use the drop login statement along with the name of the login that we want to drop. However, remember that log-ins can be associated with users. So if we want to create a user for our database, we can do something like this, where we see create user and then whatever we want the user name to be for that user. And we can associate it with a particular login name by using the four log-ins and tags. So this might be like create user Dan for login, whatever my login credentials are that I use in order to get into the database environment. It is also possible to create users without a login. So maybe they have access to the database, for example, through like an operating system account. So SQL Server, just as an example, allows us to authenticate in several ways. One of which is through your operating system credentials. So rather than creating a specific login to let you into SQL Server, depending on the configuration, we could have our database set up in such a way that if someone is able to log into the operating system, they can log into Windows, then they have access to the SQL Server database environment. So it is possible in that case to create a users that don't have a login, and we would use syntax like this for that purpose. And of course, if we want to drop users, we can do so in this way. All right, but remember, users apply at the database level and log-ins apply at the database server level. So each login could potentially be associated with many user names, one for each database.