Understand Database Security Concepts

[Music] hello again and welcome back to our course on MTA database fundamentals exam 98 - 3 6 4 in this section we're going to look at database security concepts let's start by taking a look at the objectives for this section first of all to understand the need to secure a database secondly to understand what objects can be secured and what objects should be secured and then understanding user accounts and roles let's look now at some basic security principles first of all development of a security plan should occur early in the development of a database secondly

security must be carefully designed implemented and tested the security plan will depend heavily on the size and nature of the database and on its business requirements consider these two very different scenarios in the first case we have a small database containing information that is not confidential and is to be accessed by three or four users in this case the 3 or 4 users may each be able to do pretty much anything they want to the database and they have no confidentiality either from each other or from the outside world and therefore there's no particular reason

to protect anything in the database in the second scenario we have a large database containing financial data that is to be maintained by a professional team and made available to the general public in this case what the professional team who are maintaining the database can do and what members of the general public can do are very likely to be very different things then when we're talking about security in general in relation to databases we have the concept of securable things that can be secured and different objects the databases themselves the tables in the database the

logins the users different roles all of the kinds of object that we're going to talk about in this section can be granted permissions to access these different securable x' in that first scenario we may well say that every object can access everything in the second scenario we need a very careful plan a very careful mapping from the objects to the security boards in terms of who or what can access what now in order to demonstrate these general security principles I'm going to use a very straightforward simplified security model and this security model works simply on

the principle going from left to right that a user can log into a database server and then via the master database which keeps information basically about who can access which databases a user is given an ID and is allowed to access certain user databases what they're allowed to do in those user databases is governed by their roles that they may have and by specific permissions that they may have alongside this and optionally there may be guests with access to user databases and guests generally will have very limited access they will have very limited permissions on

the database and in fact guest users may not be allowed at all but this is the very simplified security model that we're going to use now the first step in our security model is server level security this is where the user gets access first of all to the server users get access to the server via one of three methods either via a Windows user login or by a membership of a Windows user group or by a sequel server specific login this third case is only available if sequel server is using mixed-mode security now I'm going

to talk some more about that a little bit later on a login is controlled by our users credentials which are often just to username and password once logged in users unknown at the server level by their login either their sequel server login if we're using a sequel server specific login or by their windows logging and once the user is logged in his or her rights are determined by their server roles now it's important that you're familiar with these fixed server roles and this table shows you the fixed several roles unfortunately you pretty much need to

know these the most important one is the one right at the top sysadmin because a sysadmin can perform any activity in the server at all and that means sis happening can do all sorts of dangerous things including granting other users rights to do anything and they can map any network login ID to a specific database user ID so that means the sysadmin really can do just about anything a user may belong to multiple roles let's just take a quick look at these roles a server admin can change server wide configuration options and shut down the

server security admin can manage logins and their properties they can grant deny and revoke server level permissions they can also grant deny on revoke database level permissions if they have access to a database we'll talk more about that a little bit later on as well and they can reset passwords for sequel server logins process admin can end processes that are running in an instance of sequel server setup admin can add or remove link servers by using transact SQL statements bulk admin can run the bulk insert statement which enables bulk and serve data disk admin can

manage disk files and DB creator can create alter drop and restore any database so they are the fixed server roles now in addition there are some other roles that you need to be aware of one of them is the public role which is another fixed role but it can have object permissions like a standard role we'll talk about object permissions later as well every user is automatically a member of the public role and cannot be removed from the public role and for most users when they log in that's the role that they get so they

don't get things like sysadmin or setup admin they get the public role and it is in effect a baseline level of permission for users you cannot modify the permissions assigned to a fixed server role for this reason it is possible to establish user defined roles with very specific sets of permissions to avoid assigning a fixed server role that may have more permissions than a user needs and particularly in a large and sophisticated database where the security model is pretty complicated you may need to establish user defined roles so that people only have the permissions that

they need so the user has logged in and they will have a server role or roles whether they can use a Windows login or a sequel server login will be determined by the authentication mode that has been set up and this is set up when sequel server is installed Windows authentication mode will only allow Windows authentication so they'll log in by the windows login in mix mode then both windows authentication and sequels a user authentication are allowed but which authentication motors in use is set up when single server is installed although it can be changed

sequel server uses a two phase authentication scheme so once the user has been authenticated in the appropriate mode access can be granted to individual databases a sequel service stores login information within its master database now in the case of windows authentication obviously when those users are created and managed using tools that are dependent on the version of Windows that is in use so if you needed to set up a Windows user that is a Windows function once a user exists in Windows they can then be recognized by sequel server within sequel server users can be

managed using either the logins option within SSMS or using T sequel commands let's take a quick look at the dialog for creating a new login bearing in mind that SSMS can also be used for other administrative functions such as removing a windows login note that when we talk about removing and creating new logins we're not actually creating or deleting the windows accounts we're just making them recognized or not recognized by sequel server now there's a very important point to note here I've connected to my installation of sequel server we're looking at logins logins are unrelated

to a specific database at this point we haven't tried to get access to a database yet we're not talking yet about doing something to or in a database we're talking about access to the sequel server installation itself access to the server so we look under security and within security we look at logins and if I expanded logins you could see all of the users that have logins to my installation of sequel server if I wanted to create a new login right click click on new login and I get login new now that login new dialog

lets me set up a login name to choose between windows authentication and sequel server authentication I should point out that we're not going through all this so that you can actually do it it's just the concept of what you can do here and you can also do things like select the server roles now as you can see by default the new login here gets the public role if I wanted to give this person some of the other roles I could and in fact with the login that I'm logged in under I can do anything I'm

effectively sis admin on this so that's how we would set up a new login also if I wanted to delete a login I could right click on it and I could actually remove that login now apart from using the SSMS GUI interface to create and delete windows logins I can also set up windows authentication using T sequel commands so to add a new Windows user or group I can use the create login command like this create login and then the name of the existing Windows user or group now bear in mind that here I've got

a Windows domain so it's on Windows domain Acme domain and the user is Johnson B if I wanted to remove a Windows user or group I would use drop login something like this now let's just briefly return to that login new dialog again notice that apart from the windows authentication option the sequel server authentication option is there if I choose that option then the user will actually be set up as a sequel server user so I'm going to need to give that use a password and I'm going to need to set some options related to

passwords and so on and in fact when I setup a new login in either case there are some other options down here just to be aware of such as the default database for that user now for single server users you have two options again you can either do administration using the SSMS GUI and a similar way to Windows users as I just pointed out or of course you can use T sequel commands so to add a new sequel server user you would use a create login command so for example create login Smith a the thing

to note of course is when you're setting up a new sequel server user you'll need to set up additional information as I pointed out just now and there is an alter login command and with Auto login you can set up or modify all of the various properties that you might need to set for the sequel server user so for example if you wanted to set the default database one of the things I pointed out just now having created the login Smith a you could auto log in Smith a with default database is one of the

options equals parts DB now if you search for create log in and auto log in within the Microsoft con site and look within MSDN or TechNet you will see all of the options that are available with those create log in and auto log-in commands now let's go back to our simplified security model again we've managed to get as far as the server now we've logged in we have access to the server we've got our server roles the next thing we need to do is to be able to get into a database so we need to

look at database security next once the user has gained access to the server access may be granted to individual user databases users are generally granted access to a specific database by adding them to the database other of users for the database and a guest database user can be set up for users without their own access so let's now turn our attention back to s SMS and have a look at users within a specific database now what happens within the server and what the person who is doing administration on the server does for each user let's

choose this user here if I right click on that user and look at that users properties I'll see that the pages of properties include a user mapping page and that you use a mapping page lets me choose which databases that user can access so this user can access those two databases now within those databases I can also say what that uses username is now in this particular case that user owns those databases so their username is dbo but if I wanted to lock them in with just a different username in any case I could now

within one of those databases and let's go to parts DB within the parts DB database there is a security folder within that security folder there is a list of users and one of the users is dbo which is the name the mapped name given to that Windows user when they're working within this database now again if I wanted to add a new user I could right click on users click on new user a database user new comes up I can put in a user name I can choose the login name for the person who is

going to correspond to this username and then I can assign to them database roles and I'm going to talk about database roles in just a moment the other thing I'm going to talk about in just a couple of minutes time is sick curable remember I mentioned securable x' earlier on and this is where i specify which of the securable x' within the database they have access to and what sort of access they have so I can actually say what they can insert what they can select what they can delete and so on now again we'll

come back to that in a couple of minutes time but for the moment that is one of the ways that I can add a user to a database and I've also shown you there how I can map the login to the username within the database so when it comes to granting access to a database as we've seen it can be done using the SSMS GUI it can also be done by issuing a T sequel command from within the database it has to be done from within the database itself and it would be create user and

then you use the login name that's a windows login name in that particular case and then the user name for the user within the database that would set up the user with Windows login that as database user tombi note also if you want to drop that user to remove that users access to the database you would use drop user tombi now I mentioned just now database roles let's look at the fixed database roles there are again quite a few of them and again you pretty much need to know what these are DB owner can perform

all configuration and maintenance activities on the database and can also drop the database DB security admin can modify role membership and manage permissions adding principles to this role could enable unintended privilege escalation so you've got to be careful who has that role because it can easily escalate so that people get that role that you don't want to have that wrong DB access admin can add or remove access to the database for Windows logins wind groups and sequel server logins DB backup operator come back up the database DB DD L admin can run any DD l

command in a database DB data writer can add delete or change data in all user tables DB data reader can read all data from all user tables DB deny data rights it cannot add modify or delete any data in the user tables within a database and DB underscore deny data reader cannot read any data in the user tables within a database so you need to know those and now we need to turn our attention to object ownership and security a critical aspect of security in sequel server is ownership objects include tables views store procedures and

columns every object is contained within a schema and a schema is owned and everything within the schema has the same owner if a user has access to a database that doesn't actually give them permissions by default what they get will depend on membership of roles but they can also be granted permissions on an individual basis so if a user has access to a database they may be granted permissions to individual database objects permission may be granted directly to the user or by assigning the permission to the role and assigning the user to the role this

is the preferred approach to security in relation to object permissions also note that users may be assigned to multiple roles so there may be multiple permission paths working out what permissions a user has can be quite complicated particularly if the user has multiple roles and particularly if in a particular database permissions are complicated anyway now before I go into object permissions in a bit more detail just like to show you something very quickly in SSMS I'm in the parts database and if I expand tables and look at the parts table and look at the properties

of the parts table notice that one of the pages in the table properties is permissions now I'm not going to do this now but basically if I want to assign permissions to either users or roles on an object such as a table and bear in mind that I can assign permissions on other objects such as columns or stored procedures then there is usually a dialog like this one where you can assign those permissions in a very flexible and powerful way now I'm not going to go through that here in SSMS you don't need to be

able to do that you only really need to understand the basic principles and we're going to look at that now and a couple of examples using T sequel commands so let's look then at object permissions object permissions are assigned by means of grant revoke and deny revoke removes the permission assigned deny overalls grant and don't forget a user may have multiple permission pass to an object now the rule to remember is that deny always rules so if there are five permission paths to a particular object for a user and four of them grant access on

one denies then access is denied denied always overrules grant permissions can be administered using SSM S or T sequel commands now the basic object permissions are these select is the right to select data and select the mission can be applied to specific columns insert is the right to insert data Update is the right to modify existing the data update rights for which a where clause is used do require those select rights as well and update permissions can be set on specific columns in a table delete is the right to delete existing data DRI is the

right to create foreign keys with DRI and execute is the right to execute store procedures or user-defined functions and if you're wondering what DRI is it's declarative referential integrity so let's look at some examples of granting object permission using T sequel commands grant select on parts - Tom B would give the user Tom B the permission to select on the path stable you can grant more than one permission to more than one user at the same time so for instance you could say grant both insert and delete on the customers table - Jayne C and

Tom B and also there are various additional clauses you might want to look at grant and deny and revoke in MSDN or TechNet to see all the various options but this is an important one grant delete on orders - Jayne C so Jane C has the permission to delete from the oddest table with grant option means that not only that but Jane C can pass this permission on to somebody else revoke all on parts - Tom B says whatever permissions have been given to Tom B on the parts table are to be revoked now no

that is not the same as saying that Tom B has permissions denied it just means that anything that's been said about permissions for him on the parts table is revoked and the final example denies select on customers - Carl D so Carl D cannot select from the customers table so that's it just a quick review of the objectives understanding the need to secure a database understanding what objects can be secured understanding what objects should be secured understanding user accounts and understanding roles that's the end of this section please join me for the next one