How to Implement a Database in MySQL for HTML5 and CSS3 Programming
How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming

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:


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:


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:


The following example enables the sales manager to add new rows to the retail price list:


Now, thanks to this last example, the sales manager can delete unwanted rows from the table, too:

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Use SQL with Microsoft Access
How to Create a SQL Table with Microsoft Access
How to Use SQL:2011 Times and Periods
How to Use Module Language with SQL
How to Manage Many-to-Many Joins in SQL for HTML5and CSS3 Programming