Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

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:

  • Tables

  • Columns

  • Views

  • Domains

  • Character sets

  • Collations

  • Translations

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.

Protection operation Statement
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;
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!