SQL Series...41

Providing Database Security

A system administrator must have special knowledge of how a database works. That’s why, in preceding articles, I discuss the parts of SQL that create databases and manipulate data — and then  introduce SQL’s facilities for protecting databases from harm or misuse. In this article, I go into more depth on the subject of misuse.

The person in charge of a database can determine who has access to the data-base — and can set users’ access levels, granting or revoking access to aspects of the system. The system administrator can even grant — or revoke — the right to grant and revoke access privileges. If you use them correctly, the security tools that SQL provides are powerful protectors of important data. Used incorrectly, these same tools can tie up the efforts of legitimate users in a big knot of red tape when they’re just trying to do their jobs.

Because databases often contain sensitive information that you shouldn’t make available to everyone, SQL provides different levels of access — from complete to none, with several levels in between. By controlling which opera-tions each authorized user can perform, the database administrator can make available all the data that the users need to do their jobs — but restrict access to parts of the database that not everyone should see or change.

The SQL Data Control Language

The SQL statements that you use to create databases form a group known as the Data Definition Language (DDL). After you create a database, you can use another set of SQL statements — known collectively as the Data Manipulation Language (DML) — to add, change, and remove data from the database. SQL includes additional statements that don’t fall into either of these categories. Programmers sometimes refer to these statements collectively as the Data Control Language (DCL). DCL statements primarily protect the database from unauthorized access, from harmful interaction among multiple database users, and from power failures and equipment malfunctions. In this article, I discuss protection from unauthorized access.

User Access Levels

SQL:2003 provides controlled access to nine database management functions:

Creating, seeing, modifying, and deleting: These functions correspond to the INSERT, SELECT, UPDATE, and DELETE operations that I discuss in previous article.

Referencing: Using the REFERENCES keyword involves applying referential integrity constraints to a table that depends on another table in the database.

Using: The USAGE keyword pertains to domains, character sets, collations, and translations. 

Defining new data types: You deal with user-defined type names with the UNDER keyword.

Responding to an event: The use of the TRIGGER keyword causes an SQL statement or statement block to be executed whenever a predeter-mined event occurs.

Executing: Using the EXECUTE keyword causes a routine to be executed.

The database administrator

In most installations with more than a few users, the supreme database authority is the database administrator (DBA). The DBA has all rights and privileges to all aspects of the database. Being a DBA can give you a feeling of power — and responsibility. With all that power at your disposal, you can easily mess up your database and destroy thousands of hours of work. DBAs must think clearly and carefully about the consequences of every action they perform.

The DBA not only has all rights to the database, but also controls the rights that other users have. This way, highly trusted individuals can access more functions — and, perhaps, more tables — than can the majority of users.

The best way to become a DBA is to install the database management system. The installation manual gives you an account, or login, and a password. That login identifies you as a specially privileged user. Sometimes, the system calls this privileged user the DBA, sometimes the system administrator, and some-times the super user (sorry, no cape and boots provided). As your first official act after logging in, you should change your password from the default to a secret one of your own. If you don’t change the password, anyone who reads the manual can also log in with full DBA privileges. After you change the pass-word, only people who know the new password can log in as DBA.

I suggest that you share the new DBA password with only a small number of highly trusted people. After all, a falling meteor could strike you tomorrow; you could win the lottery; or you may become unavailable to the company in some other way. Your colleagues must be able to carry on in your absence. Anyone who knows the DBA login and password becomes the DBA after using that information to access the system.

If you have DBA privileges, log in as DBA only if you need to perform a spe-cific task that requires DBA privileges. After you finish, log out. For routine work, log in by using your own personal login ID and password. This approach may prevent you from making mistakes that have serious conse-quences for other users’ tables (as well as for your own).

Database object owners

Another class of privileged user, along with the DBA, is the database object owner. Tables and views, for example, are database objects. Any user who creates such an object can specify its owner. A table owner enjoys every pos-sible privilege associated with that table, including the privilege to grant access to the table to other people. Because you can base views on underly-ing tables, someone other than a table’s owner can create a view based on that owner’s table. However, the view owner only receives privileges that he normally has for the underlying table. The bottom line is that a user can’t circumvent the protection on another user’s table simply by creating a view based on that table.

The public

In network terms, “the public” consists of all users who are not specially priv-ileged users (that is, either DBAs or object owners) and to whom a privileged user hasn’t specifically granted access rights. If a privileged user grants cer-tain access rights to PUBLIC, then everyone who can access the system gains those rights.

In most installations, a hierarchy of user privilege exists, in which the DBA stands at the highest level and the public at the lowest. Figure 13-1 illustrates the privilege hierarchy.