How to Protect Data with SQL User Privileges
A major threat to data integrity is the users themselves. Some people should have no access to the data and SQL gives you that control. Others should have only restricted access to some of the data but no access to the rest. Some should have unlimited access to everything in the database. You need a system for classifying users and for assigning access privileges to the users in different categories.
The creator of a schema specifies who is considered its owner. As the owner of a schema, you can grant access privileges to the users you specify. You can also revoke privileges that you’ve already granted. A user must pass an authentication procedure to prove his identity before he can access the files you authorize him to use. The specifics of that procedure depend on the implementation.
SQL gives you the capability to protect the following database objects:
SQL supports several different kinds of protection: seeing, adding, modifying, deleting, referencing, and using databases. It also supports protections associated with the execution of external routines.
You permit access by using the GRANT statement and remove access by using the REVOKE statement. By controlling the use of the SELECT statement, the DCL controls who can see a database object such as a table, column, or view. Controlling the INSERT statement determines who can add new rows in a table.
Restricting the use of the UPDATE statement to authorized users gives you control of who can modify table rows; restricting the DELETE statement controls who can delete table rows.
If one table in a database contains as a foreign key a column that is a primary key in another table in the database, you can add a constraint to the first table so that it references the second table. When one table references another, a user of the first table may be able to deduce information about the contents of the second.
As the owner of the second table, you may want to prevent such snooping. The GRANT REFERENCES statement gives you that power. By using the GRANT USAGE statement, you can control who can use — or even see — the contents of a domain, character set, collation, or translation.
|Enable user to see a table||GRANT SELECT|
|Prevent user from seeing a table||REVOKE SELECT|
|Enable user to add rows to a table||GRANT INSERT|
|Prevent user from adding rows to a table||REVOKE INSERT|
|Enable user to change data in table rows||GRANT UPDATE|
|Prevent user from changing data in table rows||REVOKE UPDATE|
|Enable user to delete table rows||GRANT DELETE|
|Prevent user from deleting table rows||REVOKE DELETE|
|Enable user to reference a table||GRANT REFERENCES|
|Prevent user from referencing a table||REVOKE REFERENCES|
|Enable user to use a domain, character set, translation, or collation||GRANT USAGE ON DOMAIN, GRANT USAGE ON CHARACTER SET, GRANT USAGE ON COLLATION, GRANT USAGE ON TRANSLATION|
|Prevent user from using a domain, character set, collation, or translation||REVOKE USAGE ON DOMAIN, REVOKE USAGE ON CHARACTER SET, REVOKE USAGE ON COLLATION, REVOKE USAGE ON TRANSLATION|
You can give different levels of access to different people, depending on their needs. The following commands offer a few examples of this capability:
GRANT SELECT ON CUSTOMER TO SALES_MANAGER;
The preceding example enables one person — the sales manager — to see the CUSTOMER table.
The following example enables anyone with access to the system to see the retail price list:
GRANT SELECT ON RETAIL_PRICE_LIST TO PUBLIC;
The following example enables the sales manager to modify the retail price list. She can change the contents of existing rows, but she can’t add or delete rows:
GRANT UPDATE ON RETAIL_PRICE_LIST TO SALES_MANAGER;
The following example enables the sales manager to add new rows to the retail price list:
GRANT INSERT ON RETAIL_PRICE_LIST TO SALES_MANAGER;
Now, thanks to this last example, the sales manager can delete unwanted rows from the table, too:
GRANT DELETE ON RETAIL_PRICE_LIST TO SALES MANAGER;