DB Table Grants
A database (DB) administrator can set table privileges that allow an employee to access and manipulate one or more tables in a database. The administrator accomplishes this level of data control by using certain commands from the Structured Query Language. The two SQL commands used for managing data control are "grant" and "revoke." Database table grants can be assigned to allow an employee to select, insert, update, delete and execute data.
Other People Are Reading
-
Select
-
Granting the right to "select" allows the user to query a table for information, that is, searches for a set of parameters the user has defined. For instance, a database administrator for a bookstore may grant all employees working with customers the ability to select items in the book database. This enables the employee to look up titles by querying an author's name, title or book category. Granting the privilege of selecting database records does not allow the user to make changes to the data but only view the data.
Insert
-
The privilege to "insert" allows the user to insert a new record into a database. Granting this privilege enables the user to add a row to the database and fill-in all the information the record should have. For instance, an administrator in a doctor's office may be responsible for entering new patient information in a database table titled "Patient Contact Information." Granting this employee permission to "insert" will add the privilege to his login information. When the employee logs into the database he will automatically be able to add a new record. The "insert" privilege would not necessarily require the "select" privilege.
-
Update
-
Granting the "update" privilege to a user allows her to make changes to existing records in a database. The user can correct mistakes or make customer requested changes to records in designated database tables. The user would first find the record (row) with the "select" privilege and then update the record's column designations such as address, zip code or phone number.
Delete
-
Granting the "delete" privilege allows the user to delete whole records. Before granting the right to delete records in a table, administrators should be familiar with the company's records management policies. Deleting records could go against company policies or even jeopardize compliance with federal agencies regarding client records. The "select" privilege is necessary to find and then remove a record.
Execute
-
Script languages allow programmers and administrators to write code to execute detailed queries on a table or group of tables in a database. The code is stored and executed when needed, saving employees' time when a commonly run report or set of data is required. Granting the "execute" privilege allows an employee to run designated reports as needed but does not allow him to insert, update or delete records or data.
Combining Table Grants
-
Granting and revoking privileges can become a tedious job when a company is large. Administrators often create user profiles (groups) that are assigned according to a job position. Each profile has an automatic list of tables and privileges that can be used. Table grants are then manipulated at the profile level instead of the employee level, making the task of granting privileges less tedious to manage.
-
