Join Gerry O'Brien for an in-depth discussion in this video SQL Server securables, part of Securing SQL Server 2012.
Microsoft defines securables in SQL Server, as the resources to which the SQL Server Database Engine authorization system regulates access. Essentially these are the database objects to which you can apply security on your instance of SQL Server some securables are standalone and others may be contained within another securable. Starting at the highest level, SQL Server supports hierarchies of securables, that is securables that are nested with inside the scope of another securable. SQL Server provides three scopes at the highest level.
The server securable, the database securable and the schema. The server can be considered the instance of SQL Server. Where as the database is the specific database hosted on the instance and schemas are security domains or containers that are used to store database objects. Lets take a look at the server scope. On SQL Server management studio, under object explorer, if we expand the server objects folder, we can see some of the server objects that sequel server can apply security to. Most importantly in this particular instance. Are the endpoints that SQL Server makes available, these are the entry points into the SQL Server instance.
Most of them you will find become a part of the system endpoints that we will focus on in the network security portion, a little bit later on in this course. These revolve around the TCPIP connections, local machine access in main pipe access for users connecting to our SQL Server instance. Other aspects of the server-level security objects are logins, which are found under the Security folder for the SQL Server instance itself. We can have Windows logins and we can have SQL Server logins, depending on the authentication mode for SQL Server.
We also have server roles that exist under the server scope. These server roles will be discussed in later portions of this course, as we start to assign users and permissions to them. And under the servers scope, SQL Server also focuses security at the database level. As we drill down into the database scope, we can start to see the different aspects for the security purposes within the database scope itself. Each database also has its own security folder, where we can focus on users, which again, become the logins that we assign the permissions to for our database.
We also have database level roles and application roles. Currently, there are no application roles assigned on this server. Once again, these database roles will be covered in latter segments of the security course. Other components that we don't see within the object explorer, are components that, they still exist at the data base level. Such as assemblies which are typically DLL files that are used in the instance of sequel server for deploying functions stored procedures, or triggers. And are typically managed by the common language run time. You also don't see messages for the service broker, although, the service broker folder does exist.
You can see that under the service broker, we could focus on messages, contracts, ques, services, and routes as security principles, as well, within the SQL Server scope of the database. Also under the security folder for the database itself, we will see schemas as well as asymmetric keys, certificates and symmetric keys for the use of encryption and digital certificates within the database itself. It is also important to note that the schema also becomes a container in its own right. The schema scope contains types or it may contain specific schemas themselves, it may contain XML schema collections and it may contain specific objects.
Such as aggregates functions store procedures, tables and views. Another term you will become familiar with is a term known as principle. Principles are the entity to which permissions are assigned, which are typically logins and users. Access to securables are controlled through granting or denying permissions to these principles. So in summary, SQL Server contains many securables. These database objects are used to define the security for the SQL Server instance. Security principals, such as, log in or users are grant or denied specific permissions to these securibles in order to create this system.
Where users can perform their roles and complete their work within the database while still securing the data from unauthorized access.
- Understanding security best practices
- Managing logins and users
- Understanding how SQL Server checks permissions
- Creating and assigning logins and roles
- Securing SQL on the network