Skip to content Skip to main navigation Skip to footer

Creating and Revoking Object Privileges

Objectives

This lesson covers the following objectives:

  • Explain what a ROLE is and what its advantages are
  • Construct a statement to create a ROLE and GRANT privileges to it
  • Construct a GRANT .. ON .. TO.. WITH GRANT OPTION statement to assign privileges on objects in your schema to other users and/or PUBLIC
  • Construct and execute a statement to REVOKE object privileges from other users and/or from PUBLIC
  • Distinguish between privileges and roles
  • Explain the purpose of a database link

Purpose

  • If you share a computer with others, whether at school or at home, you’ve probably had something you’ve worked on either viewed, changed, or deleted by someone else.
  • Wouldn’t it be nice to be able to control the privileges others have to your personal files?
  • For databases, just as at school or home, data security is very important.
  • In this lesson, you will learn how to grant or take away access to database objects as a means to control who can alter, delete, update, insert, index, or reference those database objects.

Roles

  • A role is a named group of related privileges that can be granted to a user.
  • This method makes it easier to revoke and maintain privileges.
  • A user can have access to several roles, and several users can be assigned the same role.
  • Roles are typically created for a database application.

Using roles makes the process of granting privileges much easier. Instead of assigning individual privileges to hundreds of users, the DBA can create a role, assign privileges to the role, then grant the role to users.

  • To create and assign a role, first the DBA must create the role.
  • Then the DBA can assign privileges to the role, and the role to users.
CREATE ROLE manager;

Role created.

GRANT create table, create view TO manager;

Grant succeeded.

GRANT manager TO jennifer_cho;

Grant succeeded.

  • Use the following syntax to create a role:
CREATE ROLE role_name;
  • After the role is created, the DBA can use the GRANT statement to assign the role to users as well as assign privileges to the role.
  • The example shown creates a manager role and then allows managers to create tables and views.
  • It then grants the role to a user.
  • Now the user can create tables and views.
  • If users have multiple roles granted to them, they receive all of the privileges associated with all of the roles.
  • Note: The CREATE ROLE is a system privilege that has not been issued to Academy classrooms.

Characteristics Of Roles

  • Roles are named groups of related privileges.
  • They can be granted to users.
  • They simplify the process of granting and revoking privileges.
  • They are created by a DBA.
Granting Object Privileges

Granting Object Privileges

  • Use the following syntax to grant object privileges:
GRANT object_priv [(column_list)]
ON object_name
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
Granting Object Privileges

Object Privileges Guidelines

  • To grant privileges on an object, the object must be in your own schema, or you must have been granted the privilege using the WITH GRANT OPTION.
  • An object owner can grant any object privilege on the object to any other user or role of the database.
  • The owner of an object automatically acquires all object privileges on that object.

GRANT Examples

  • Scott King (username scott_king) has created a clients table.
  • In Example 1 on the right, all users are granted permission to SELECT from Scott’s clients table.
  • Example 2 grants UPDATE privileges to Jennifer and to the manager role on specific columns in Scott’s clients table.
1. GRANT SELECT
ON clients
TO PUBLIC;

2. GRANT UPDATE(first_name,
last_name)
ON clients
TO jennifer_cho, manager;

3. SELECT *
FROM scott_king.clients;
4. CREATE SYNONYM clients
FOR scott_king.clients;

5. SELECT *
FROM clients;
  • If Jennifer now wants to SELECT data from Scott’s table, the syntax she must use is listed in Example 3.
  • Alternatively, Jennifer could create a synonym for Scott’s table and SELECT from the synonym.
  • See the syntax in Examples 4 and 5.
  • Different object privileges are available for different types of schema objects.
  • A user automatically has all object privileges for schema objects contained in his schema.
  • A user can grant any object privilege on any schema object that the user owns to any other user or role.

WITH GRANT OPTION

  • A privilege that is granted using the WITH GRANT OPTION clause can be passed on to other users and roles by the grantee.
  • Object privileges granted using the WITH GRANT OPTION clause are revoked when the grantor’s privilege is revoked.
  • The example below gives user Scott access to your clients table with the privileges to query the table and add rows to the table.
  • The example also allows Scott to give others these privileges:
GRANT SELECT, INSERT
ON clients
TO scott_king
WITH GRANT OPTION;

The PUBLIC Keyword

  • An owner of a table can grant access to all users by using the PUBLIC keyword.
  • The example shown below allows all users on the system to query data from Jason’s clients table:
GRANT SELECT
ON jason_tsang.clients
TO PUBLIC;

DELETE Object

  • If you attempt to perform an unauthorized operation, such as deleting a row from a table on which you do not have the DELETE privilege, the Oracle Server does not permit the operation to take place.
  • If you receive the Oracle Server error message “table or view does not exist,” you have done one of the following:
    • Referenced a table or view that does not exist
    • Attempted to perform an operation on a table or view for which you do not have the appropriate privileges

Revoking Object Privileges

  • You can remove privileges granted to other users by using the REVOKE statement.
  • When you use the REVOKE statement, the privileges that you specify are revoked from the users that you name and from any other users to whom those privileges were granted using the WITH GRANT OPTION clause.
  • Use the following syntax to revoke object privileges:
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
  • CASCADE CONSTRAINTS is required to remove any referential integrity constraints made to the object by means of the REFERENCES privilege.

With Grant Option

  • The example below revokes SELECT and INSERT privileges given to user Scott on the clients table.
REVOKE SELECT, INSERT
ON clients
FROM scott_king;
  • If a user is granted a privilege with the WITH GRANT OPTION clause, that user can also grant the privilege using the WITH GRANT OPTION clause.
  • This means that a long chain of grantees is possible, but no circular grants are permitted.
  • If the owner revokes a privilege from a user who granted privileges to other users, the revoke statement cascades to all privileges granted.
  • For example, if user A grants SELECT privileges on a table to user B, including the WITH GRANT OPTION clause, user B can grant to user C the SELECT privilege including the WITH GRANT OPTION clause as well.
  • Now, user C can grant to user D the SELECT privilege.
With Grant Option
  • However, if user A revokes privileges from user B, then those privileges granted to users C and D are also revoked.
However, if user A revokes privileges from user B, then those privileges granted to users C and D are also revoked.

Private and Public Synonyms

  • As mentioned earlier in this lesson, you can create a synonym to eliminate the need to qualify the object name with the schema and provide you with an alternative name for a table, view, sequence, procedure, or other object.
  • Synonyms can be either private (the default) or public.
  • A public synonym can be created by Database Administrators, or database users who have been given the privileges to do so, but not everyone can automatically create public synonyms.
  • Note: The CREATE PUBLIC SYNONYM privilege has not been granted to Academy students.

Roles and Privileges

  • Roles and Privileges differ in a number of ways:
    • A user privilege is a right to execute a particular type of SQL statement, or a right to access another user’s object.
    • All privileges are defined by Oracle.
    • Roles, on the other hand, are created by users (usually administrators) and are used to group together privileges or other roles.
    • They are created to make it easier to manage the granting of multiple privileges or roles to users.
    • Privileges come with the database and Roles are made by Database Administrators or users of a particular database.

Database Links

  • A database link is a pointer that defines a one-way communication path from one Oracle database to another database.
  • The link pointer is actually defined as an entry in a data dictionary table.
  • To access the link, you must be connected to the local database that contains the data dictionary entry.
  • A database link connection is “one-way” in the sense that a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A.
  • CREATE DATABASE LINK – In Oracle Application Express, there is no constant connection to the database, and as a result, this feature is not available.
Database Links
  • If local users on database B want to access data on database A, they must define a link that is stored in the data dictionary of database B.
  • A database link connection gives local users access to data on a remote database.
  • For this connection to occur, each database in the distributed system must have a unique global database name.
  • The global database name uniquely identifies a database server in a distributed system.
  • The great advantage of database links is that they allow users to access another user’s objects in a remote database so that they are bounded by the privilege set of the object’s owner.
  • In other words, a local user can access a remote database without having to be a user on the remote database.
  • The example shows a user scott_king accessing the EMP table on the remote database with the global name HQ.ACME.COM.
  • Typically, the Database Administrator is responsible for creating the database link.
  • The dictionary view USER_DB_LINKS contains information on links to which a user has access.
  • Once the database link is created, you can write SQL statements against the data in the remote site.
  • If a synonym is set up, you can write SQL statements using the synonym.
  • For example:
CREATE PUBLIC SYNONYM HQ_EMP
FOR emp@HQ.ACME.COM;
  • Then write a SQL statement that uses the synonym:
SELECT *
FROM HQ_EMP;
  • You cannot grant privileges on remote objects.

Terminology

Key terms used in this lesson included:

  • CREATE ROLE privilege
  • WITH GRANT OPTION
  • REVOKE privilege
  • REVOKE statement
  • PUBLIC SYNONYM
  • PRIVATE SYNONYM
  • Database Links

Summary

In this lesson, you should have learned how to:

  • Explain what a ROLE is and what its advantages are
  • Construct a statement to create a ROLE and GRANT privileges to it
  • Construct a GRANT .. ON .. TO.. WITH GRANT OPTION statement to assign privileges to objects in their schema to other users and/or PUBLIC
  • Construct and execute a statement to REVOKE object privileges from other users and/or from PUBLIC
  • Distinguish between privileges and roles
  • Explain the purpose of a database link

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *