|
|
For the authentication part, the SQL-99 standard doesn't provide you with any commands for managing user accounts. As a result, once again you're facing difficult territory, where each database has its own approach to the subject, even though the features they provide are quite similar. You'll see, for each database, how to create and remove users, assign passwords to them, and so on.
As for the authorization part, SQL-99 specifies two commands for managing permissions: GRANT and REVOKE. Most database systems support these two commands, but the syntax differs slightly between the implementations. You'll take a quick look at each of them before moving on to database specifics.
SQL-99 also specifies the CREATE ROLE, SET ROLE, and DROP ROLE commands, which are about managing roles and associating them with users. However, out of the RDBMS platforms covered in this book, only Oracle implements these commands; the other databases have different commands for dealing with roles. For this reason, we'll discuss roles separately for each database.
| Tip |
Most databases ship with visual tools that allow administering security with mouse clicks instead of lengthy commands. MySQL doesn't ship with such tools, but you'll find many free ones provided by third parties. We'll leave these features for you to explore. The following sections cover how you can manage user accounts for each database system using command-line instructions. |
You use GRANT to grant privileges to existing users. There are two main kinds of permissions: statement-level permissions and object-level permissions.
Statement-level permissions are about permitting users to perform certain actions that aren't related to a specific database object. Examples of statement-level permissions are CREATE DATABASE, CREATE TABLE, CREATE PROCEDURE, and CREATE VIEW. A user with the CREATE TABLE permission has the right to create data tables. For granting statement-level permissions, you use a command similar to the following:
GRANT <privilege type> TO <username>
Object-level permissions control access to specific, existing database objects. The most obvious database object type you might want to limit access to is the data table. The typical rights that can be granted for data tables are INSERT, UPDATE, DELETE, SELECT, or a combination of all of these. As an example, a user can be allowed to read a data table but will not be allowed to modify it. Some databases permit restricting access on a column-level basis inside the table. For stored procedures, the typical privilege type that can be granted is EXECUTE. When granting object-level permissions, you must also specify the object (resource):
GRANT <privilege type> ON <resource> TO <username>
By default, users have full access to the objects they create, but they don't have access to other users' objects. A user who creates a data table will have all possible rights on that table, including permissions to read, modify, and even drop it, and a user who creates a stored procedure will have the right to execute that stored procedure. However, if you want to access some other user's data, that user will have to grant you access to his or her objects.
With SQL Server, Oracle, and DB2, you can also use GRANT to assign roles to existing users. You'll see examples of this later in the chapter.
The exact GRANT options, privilege types, and supported resource types are database specific; you'll need to consult the documentation for your database for more details.
Here is the SQL-99 syntax for GRANT:
GRANT { [ALL PRIVILEGES] }
| SELECT
| INSERT [(column_name [,...n])]
| DELETE
| UPDATE [(column_name [,...n])]
| REFERENCES [(column_name [,...n])]
| USAGE }[,...n]
ON { [TABLE] table_name
| DOMAIN domain_name
| COLLATION collation_name
| CHARACTER SET charset_name
| TRANSLATION translation_name }
TO (granteee_name | PUBLIC}
[WITH GRANT OPTION]
You use REVOKE to remove privileges that had previously been granted with GRANT or deny permissions that users have by default (such as the rights users have over their own data).
REVOKE also works supports a statement-level syntax and an object-level syntax. For dealing with statement-level permissions, you use it like this:
REVOKE <privilege type> FROM <username>
For object-level permissions, you also need to specify the resources you're revoking privileges from:
REVOKE <privilege type> ON <resource> FROM <username>
When all rights are revoked from a given user, the user and its objects (such as data tables) aren't deleted from the database. Instead, the user is simply forbidden to access them.
SQL-99 specifies the following REVOKE syntax:
REVOKE [GRANT OPTION FOR]
{ ALL PRIVILEGES }
| SELECT
| INSERT
| DELETE
| UPDATE
| REFERENCES
| USAGE }[,...n]
ON { [TABLE] table_name
| DOMAIN domain_name
| COLLATION collation_name
| CHARACTER SET charset_name
| TRANSLATION translation_name }
FROM (granteee_name | PUBLIC} [,...n]
{CASCADE | RESTRICT}
|
|