Chapter 12: SQL and RDBMS Security
Chapter 12: SQL and RDBMS Security
SQL provides only limited security mechanisms, relying on the software to implement a more robust security framework. Recognizing this, we've decided to give you a comprehensive overview of the RDBMS security, in addition to detailed coverage of SQL specific security statements (both mandated by the SQL99 standard and vendor-specific implementations).
Basic Security Mechanisms
Database security is an enormous topic, and exploring the ways in which leading database vendors implemented its various aspects is even larger. Security was not invented with the relational database; the password authentication, locks, and other security concepts are as ancient as human history, and SQL just added a new twist. Following SQL92/99's lead, all databases essentially comply in establishing the security procedures. There are three levels of security common to all RDBMS:
-
Authentication. User connects to the RDBMS.
-
Authorization. User gets access to the database or database schema objects to perform certain actions, based on the set of privileges assigned to the user.
-
Auditing. For monitoring suspicious (and otherwise) activity, and performing postmortem analysis.
What differs is the way each of these relational database management systems (RDBMS) implement these levels.
Identification and authentication
The first line of defense is authentication. Before you even access RDBMS you must submit sufficient information validated either by RDBMS itself, or by the operating system within which this database is installed. Once the identity is authenticated, you may proceed with the attempt to access the database resources, objects, and data.
Authorization and access control
Once the user is authenticated and granted access to the database, RDBMS employs a complex, finely grained system of privileges (permissions) for the particular database objects. These privileges include permission to access, modify, destroy, or execute relevant database objects, as well as add, modify, and delete data.
Encryption
Encryption provides an additional security layer, protecting the data from unauthorized viewing. Even if access to the database is obtained, it will not be easy to decipher encrypted data into a human readable form.
Integrity and consistency
While security is mostly based on authentication and authorization procedures, data integrity plays a certain role in protecting data from unintentional or malicious manipulation. For example, even if a user gains access to the database (by stealing a password, for example), s/he still has to follow relational rules for data manipulation, which, among others, do not allow orphaned records; s/he wont be able to delete records from a parent table without understanding database relationships (though some vendors had implemented the CASCADE feature that instructs RDBMS to remove child records upon deletion of the parent one), won't be able to insert a duplicate a record into a column protected by the UNIQUE constraint, or won't be able to insert invalid data that would violate CHECK constraints.
Auditing
Auditing provides means to monitor database activity, both legitimate and unauthorized. It preserves the trail of database access attempts — either successful or failed, data deletions and inserts (in case one has to find out what had happened), and so on. It is a necessary component in order to be considered for security certification, discussed later in the chapter.
