|Lesson 2||Granting privileges|
|Objective||Describe 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:
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
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 Slide Show.
- The EMP table has six columns - 1) NAME, 2) ADDRESS, 3) CITY, 4) STATE, 5) ZIP, and 6) SALARY
- The current user does not have INSERT privilege for the SALARY column
- If the user were to insert a value for the SALARY column, an error would be returned
- 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
Insert With Limited Access to Columns
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.
There are three object privileges that do not directly relate to DML commands:
ALTER privilege gives a user the ability to alter the structure of a database object
INDEX privilege gives a user the ability to build an index on a table
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
for different types of objects. The next lesson examines the syntax for granting these object privileges.