Users and Permissions

At the base of the authentication process is the concept of a user account. When installing database software, one or more administrative accounts are created by default, and nobody else has access to the system. However, it's likely you'll need to give database access to other people.

To give database access to someone, you need to take two steps:

1. Create a user account for that person (if he or she doesn't already have one). Using that account, the database system will be able to authenticate that person. Typically, the user is protected by a password that's requested during the login process.

2. When a user is created, by default the user doesn't have access (permissions) to any database objects. So, you need to grant the necessary permissions to the person's account; by doing this, you authorize the person to access the necessary database resources.

You can grant users various kinds of permissions, such as the rights to access various databases; create new data tables; update, select, insert, or delete records from data tables; execute stored procedures; add or modify other user accounts; and so on. The permissions can be generic (such as the user can create data tables or read any of the data tables), or they can be specific to existing database objects (such as the user can read or modify the Friend data table).

Each database supports a large number of possible permissions that a user can have. Users can be granted one or any combination of permissions; by setting permissions, a database administrator has the power to control who can do what to the database.

Along with the flexibility to provide each user with any combination of permissions, there comes the complexity of administering these permissions. When having a large number of users, each with its own combination of permissions, it can be become a nightmare to be in charge of security. What are the implications of an employee leaving the company or being transferred to another department? What about the permissions that the employee gave to other people while having the account?

SQL has answers for some of these problems. Additionally, most databases have started to offer additional tools to improve the database administrator's life. One of them is the support for roles.

Roles

A role is a named set of database permissions. In other words, a role contains permissions. Roles can be granted to or revoked from users; granting a role to a user has the same effect as granting the user all the permissions in that role.

Roles are important because they're extremely helpful for administering database security; they allow the database administrator to manage user privileges in a much more convenient way than setting individual permissions.

From the databases we've presented so far, SQL Server, Oracle, and DB2 support roles. MySQL doesn't support roles. Access supports the notion of "user groups," which is similar to the notion of roles.

Each database that supports roles ships with a number of predefined database roles. For example, the system administrator (sa) account in SQL Server is associated with the sysadmin role. After creating a new SQL Server user, if you want to give it full access to the server's resources, you just need to assign it to the sysadmin role, and the job is done; the user will have all permissions associated with the system administrator role. This is much easier than if you had to manually assign permissions (and believe us, a system administrator has a lot of individual permissions on the database).

Views

Views aren't directly related to security, but database administrators often use them to control the security of a database.

If you want to grant a user access to a portion of a table (such as a limited number of columns or only to certain rows), you can create a view based on that table (or even on multiple tables) and grant permissions on that view. Working with views sometimes offers more flexibility than assigning privileges on database tables.

For more information about views, please refer to Chapter 8, "Hiding Complex SQL with Views."