1. Security Guide
  2. Authorization in TimesTen
  3. Overview of Privileges to Create, Alter, or Drop Objects

Overview of Privileges to Create, Alter, or Drop Objects

There are privileges that are required in order to create, alter, or drop database objects.

Privileges to Create Database Objects

To create a database object such as a table, view, materialized view, sequence, PL/SQL procedure, PL/SQL function, PL/SQL package, or synonym, you must have the appropriate CREATE object_type or CREATE ANY object_type privilege.

The following describes the CREATE and CREATE ANY privileges:

  • The CREATE object_type privilege grants a user the ability to create an object of the specified type (such as TABLE), but only in the user's own schema. After creation, the user owns the object and has all privileges for the object.

  • The CREATE ANY object_type privilege grants a user the ability to create any object of that type in any schema of the database. The CREATE ANY object_type privileges are CREATE ANY TABLE, CREATE ANY INDEX, CREATE ANY VIEW, CREATE ANY MATERIALIZED VIEW, CREATE ANY SEQUENCE, CREATE ANY SYNONYM and CREATE ANY PROCEDURE.

A user must be granted CREATE TABLE privilege to create a table in their schema, as in this example: 

Command> GRANT CREATE TABLE TO terry;

This example grants the privilege to create any table in any schema to user terry: 

Command> GRANT CREATE ANY TABLE TO terry;

Note:

  • See Object Privileges for Views and Object Privileges for Materialized Views.

  • When CREATE OR REPLACE results in an object (such as a procedure, function, package, or synonym) being replaced, there is no effect on privileges that any users had previously been granted on that object. This is as opposed to when there is an explicit DROP and then CREATE to re-create an object, in which case all privileges on the object are revoked.

Privileges to Alter Database Objects

The ALTER ANY object_type privilege is necessary to modify the properties of objects that the user does not own.

For example, if a procedure proc1 is created in the hr schema and pat is granted the ALTER ANY PROCEDURE privilege, pat can alter the procedure hr.proc1.

The ALTER privilege cannot be granted on an individual object. Instead, you must grant the ALTER ANY privilege for the desired object type.

Privileges to Drop Database Objects

The DROP ANY object_type privilege enables a user to drop any object of the specified type in the database and is necessary to drop an object of object_type that the user does not own.

For example, granting pat the DROP ANY TABLE privilege enables pat to drop the employees table that is owned by the user hr.

The DROP privilege cannot be granted on an individual object. Instead, you must grant the DROP ANY privilege for the desired object type.