Object Privileges  «Prev  Next»

Lesson 1

Managing Object Privileges and Database Objects

You already learned about creating different types of database objects. Your Oracle database will store and manipulate these database objects to respond to user requests, but which users? You can control who can access objects in the database and what they are allowed to do with those objects by assigning object privileges[1]. Object privileges are used to enforce security on the objects by limiting the type of access that any one user has to the objects.

How does a DBA assign object privileges to a database

A database administrator (DBA) can assign object privileges to a database by using the GRANT command. The basic syntax for the GRANT command is:
GRANT privilege_name 
ON object_name TO user_name;

For example, to grant SELECT privilege on a table named "employees" to a user named "jane", the DBA would use the following command:
GRANT SELECT ON employees TO jane;

The DBA can also use the GRANT OPTION to allow the user to grant the same privilege to other users. For example, to grant SELECT privilege on a table named "employees" to a user named "jane" and allow her to grant the same privilege to other users, the DBA would use the following command:
GRANT SELECT ON employees TO jane WITH GRANT OPTION;
It's important to note that the specific implementation and syntax of object privileges can vary depending on the database management system (DBMS) being used.

Users, Roles, and Privileges

Every Oracle user has a name and password and owns any tables, views, and other resources that he or she creates. An Oracle role is a set of privileges (or the type of access that each user needs, depending on his or her status and responsibilities). You can grant or bestow specific privileges to roles and then assign roles to the appropriate users. A user can also grant privileges directly to other users. Database system privileges let you execute specific sets of commands. The CREATE TABLE privilege, for example, lets you create tables. The privilege GRANT ANY PRIVILEGE allows you to grant any system privilege.Database object privileges give you the ability to perform some operation on various objects. The DELETE privilege, for example, lets you delete rows from tables and views. The SELECT privilege allows you to query with a select from tables, views, sequences, and snapshots (materialized views).

Module Objectives

In this module, you will learn about object privileges. By the end of this module, you will be able to:
  1. Assign Oracle's object privileges
  2. Grant other users access to your tables
  3. Grant object privileges by using Security Manager
  4. List the privileges that have been granted on a table
  5. List the privileges that have been granted on columns and for users
  6. Use the WITH GRANT option
  7. Revoke privileges once granted

All database systems require some type of security on the data they contain, and this module will give you all the information you need to design and enforce a security scheme.

Oracle Database 12c
[1]Privilege : The capability to perform some type of database action.