Object Privileges  «Prev  Next»

Lesson 2Granting privileges
ObjectiveDescribe Oracle's object privileges

Database Access Privileges

The most widely used privileges are applied to the main storage structures for data, the table and the view.
You can grant one of four object privileges that control access to the data contained within tables and views:
  1. SELECT
  2. UPDATE
  3. INSERT
  4. DELETE

DML Statement

Each of these privileges corresponds to a data manipulation language (DML) command. If a user does not have the privilege to perform a specific type of DML, attempting to execute this command will result in an error being returned from the Oracle database.
You can assign UPDATE and INSERT privileges to columns within a table. If a user performs one of these operations, any columns that they do not have access to will receive NULL values, as shown in the following series of images.

1) The EMP table has six columns - 1) NAME 2) ADDRESS 3) CITY 4) STATE 5) ZIP 6) SALARY
1) The EMP table has six columns - 1) NAME, 2) ADDRESS, 3) CITY, 4) STATE, 5) ZIP, and 6) SALARY

2) The current user does not have INSERT privilege for the SALARY column
2) The current user does not have INSERT privilege for the SALARY column

3) If the user were to insert a value for the SALARY column, an error would be returned
3) If the user were to insert a value for the SALARY column, an error would be returned

4) If the user were to insert only values for the five columns for which he or she has INSERT privileges, the SALARY column would be assigned the NULL value
4) If the user were to insert only values for the five columns for which he or she has INSERT privileges, the SALARY column would be assigned the NULL value

  1. The EMP table has six columns - 1) NAME, 2) ADDRESS, 3) CITY, 4) STATE, 5) ZIP, and 6) SALARY
  2. The current user does not have INSERT privilege for the SALARY column
  3. If the user were to insert a value for the SALARY column, an error would be returned
  4. If the user were to insert only values for the five columns for which he or she has INSERT privileges, the SALARY column would be assigned the NULL value

What happens when a user has limited access to a column?

If a user performs one of these operations, any columns that the user does not have access to will receive NULL values, as shown in the images below.

If you want to limit the columns a user can see, create a separate view that contains only those columns and grant them SELECT access on the view but not on the underlying table.

Other Privileges

There are three object privileges that do not directly relate to DML commands:
  1. The ALTER privilege gives a user the ability to alter the structure of a database object
  2. The INDEX privilege gives a user the ability to build an index on a table
  3. The REFERENCES privilege gives a user the ability to reference a table with another table, such as including a FOREIGN KEY constraint that references a parent key in the table

There are other privileges that can be granted to users. System privileges control how a user can interact with the Oracle system. Many of the system privileges control the ability of a user to execute data definition commands, such as CREATE, ALTER, and DROP for different types of objects. The next lesson examines the syntax for granting these object privileges.

Oracle Database 12c