From the course: Database Foundations: Administration

Principals and securables

From the course: Database Foundations: Administration

Start my 1-month free trial

Principals and securables

- [Instructor] In addition to protecting a database from external calamities with a robust backup and restore strategy, administrators are also responsible for securing the information within the database. The security of the server's contents is maintained through access permissions and user accounts. It should be no surprise that databases often store sensitive information. They might be trade secrets or personal information about users or anything else that should not be made available to the general public. Controlling who has access to what is an important thing to think about. Not every user of the database needs to have full access to everything that it contains. Just like in the real world, database users will have specific areas of responsibility. A human resources manager probably shouldn't be able to edit product inventory quantities, and a customer service agent probably shouldn't be able to adjust employee salaries. By compartmentalizing the contents of the database and assigning permissions to different users, you can secure the information that it contains and ensure that only the appropriate people have access. Microsoft uses two terms in the documentation for SQL server that I think are really useful, principals and securables. Though other RDBMS platforms don't use these terms specifically, the general concepts still apply, and I think they're helpful in understanding how a database is protected from unauthorized access. Principals are generally thought of as individual people. Each end user is a principal. They don't have to be an actual human, though. They could be a client application or an automated terminal. Principals can also be groups of users and individuals may have membership in a number of different groups. By grouping users together, you can manage their access as a single unit. Everyone that's a member of the group will have the same ability to access the database's contents. Securables are any of the database's resources. Objects, such as tables, views, or stored procedures are considered securable objects. Like with principles, you can group securable objects together to make management easier. We've actually been using these groups all along in the organization of our Two Trees database. Schemas, such as the inventory schema, are used to group related tables and other objects together in the structure of the database. So principals are the individual users or groups. Securables are the database objects and schemas. The way that they connect is through a system of permissions. By explicitly giving permission for a principal to access a securable, you can control who can access what.

Contents