Privileges for SQL Objects

Object Privileges for Tables

For a user to create a table, that user must be granted the CREATE TABLE or CREATE ANY TABLE privilege.

For a user to perform operations on tables that they do not own, they must be granted the appropriate object privilege for that table. This includes privileges for tables within cache groups. The object privileges for tables include SELECT, UPDATE, DELETE, INSERT, INDEX and REFERENCES.

For example:

Command> GRANT SELECT ON hr.employees TO pat;
Command> GRANT UPDATE ON hr.employees TO pat;

The INDEX privilege enables a user to create an index on the table.

The REFERENCES privilege enables use of the REFERENCES clause in the CREATE TABLE or ALTER TABLE statement. This clause creates a foreign key dependency from a child table column (in the following example, table1.col1) to a parent table column (in the example, table2.pk).

Command> ALTER TABLE pat.table1 ADD CONSTRAINT fk1 FOREIGN KEY (col1) 
REFERENCES pat.table2 (pk);

If pat, owner of the tables, executes the statement, no additional privileges are needed. Any other user executing the statement would need ALTER ANY TABLE privilege.

In addition, if the user executing an ALTER TABLE ... REFERENCES statement does not own the table referenced by the REFERENCES clause, then REFERENCES object privilege on the applicable table column is required. For example, for pat to execute this statement:

Command> ALTER TABLE pat.table1 ADD CONSTRAINT fk1 
FOREIGN KEY (col1) REFERENCES terry.table2 (pk);

Pat would need the following privilege grant:

Command> GRANT REFERENCES (pk) ON terry.table2 TO pat;

Note that the REFERENCES privilege implicitly grants SELECT privilege for a user creating a foreign key from the parent table. However, this implicit grant does not mean that the user has the SELECT privilege on the parent table, so any SELECT statements fail if the only privilege on the parent table is REFERENCES.

Note:

If you have tables related by foreign key constraints, these notes apply:

If ON DELETE CASCADE is specified on a foreign-key constraint for a child table, a user can delete rows from the parent table resulting in deletions from the child table without requiring an explicit DELETE privilege on the child table. However, a user must have the DELETE privilege on the parent table for this to occur automatically.
When you perform an insert or update on a child table, TimesTen determines whether there is a foreign key constraint violation on the parent table resulting from the change to the child table. In this case, a user is required to have the INSERT or UPDATE privilege on the child table, but not a SELECT privilege on the parent table.
A user who creates a child table needs the REFERENCES object privilege on the parent table to create a foreign key dependency.

Object Privileges for Views

For a user to select from a view that they do not own, they need to be granted the SELECT object privilege for that view. Furthermore, the owner of the view must have the SELECT object privilege for all of the objects referenced by the view.

For user pat to create a view that references only objects owned by pat, as in the statement that follows, then pat needs only the CREATE VIEW privilege.

Command> CREATE VIEW pat.view1 AS SELECT * FROM pat.table1;

For pat to create a view that references a table owned by terry, as in the statement that follows, then pat also needs the SELECT object privilege on that table. The owner of a view must be granted the SELECT object privilege on each object referenced by the view.

Command> CREATE VIEW pat.view2 AS SELECT * FROM terry.table2;

For a third user, joe, to execute the preceding statement, joe needs the CREATE ANY VIEW privilege. And pat, as the owner of the view, still must have been granted the SELECT object privilege in order to perform the select on the table that terry owns.

When you select from a view, TimesTen validates the view at execution time, as well as any views referenced by that view, for the required underlying privileges.

Now consider the following example:

Command> CREATE VIEW pat.view2 AS SELECT * from terry.table2;
Command> CREATE VIEW joe.view4 AS SELECT * from pat.view2, terry.table4;

For pat to execute these statements, the following privileges must be granted:

User pat must be granted the CREATE ANY VIEW privilege so pat can create a view in the schema owned by joe.
User joe must be granted the SELECT object privilege on terry.table4.
User joe must be granted the SELECT object privilege on pat.view2
User pat must be granted the SELECT object privilege on terry.table2

Object Privileges for Sequences

For a user to perform operations on sequences that they do not own, they must be granted the SELECT object privilege. The SELECT privilege on a sequence enables the user to perform all operations on that sequence, including NEXTVAL, even though that ultimately updates the sequence.

For example, to grant SELECT privilege on the employees_seq sequence in the hr schema to the user pat:

Command> GRANT SELECT ON hr.employees_seq TO pat;

User pat can subsequently generate the next value of the sequence with the following statement:

Command> SELECT hr.employees_seq.NEXTVAL FROM DUAL;
< 207 >
1 row found.

Object Privileges for Materialized Views

To create a materialized view, a user needs at least the CREATE MATERIALIZED VIEW privilege. To create a materialized view in another user's schema, the CREATE ANY MATERIALIZED VIEW privilege is required.

Additionally, the owner of the materialized view needs to have CREATE TABLE privilege as well as SELECT privilege on every detail table in that materialized view. If the owner of an existing materialized view loses the SELECT privilege on any detail table on which the materialized view is based, the materialized view becomes invalid.

For a user to select from a materialized view that they do not own, the user needs to be granted the object privileges for materialized views, which include SELECT, INDEX and REFERENCES.

Note:

The status of a materialized view is indicated in the STATUS column of the SYS.DBA_OBJECTS, SYS.ALL_OBJECTS, and SYS.USER_OBJECTS views. The owner of the materialized view can see its status in the USER_OBJECTS view.

Also, if a materialized view is invalid, the ttIsql describe output appends INVALID for the materialized view.

Furthermore, regarding materialized views:

Users that have the privilege to do so can still update the detail tables of the materialized view. However, an invalid materialized view does not reflect these changes.
In order to re-validate an invalid materialized view, you must grant the appropriate privileges to the owner of the materialized view and then drop and re-create the materialized view.

Object Privileges for Synonyms

A synonym is an alias for a database object. Synonyms are often used for security and convenience, because they can be used to mask object names and object owners. In addition, you can use a synonym to simplify SQL statements.

Synonyms provide independence by permitting applications to function without modification regardless of which object a synonym refers to. Synonyms can be used in DML statements, some DDL statements and cache statements.

For a user to create or drop private or public synonyms, the user must have the following privileges:

Table 2-1 Privileges for Synonyms

Action	Required Privilege
Create a private synonym in the user's own schema.	`CREATE SYNONYM`
Create a private synonym in another user's schema.	`CREATE ANY SYNONYM`
Create a public synonym.	`CREATE PUBLIC SYNONYM`
Drop a private synonym in the user's own schema.	No privilege needed.
Drop a private synonym in another user's schema.	`DROP ANY SYNONYM`
Drop a public synonym.	`DROP PUBLIC SYNONYM`

In addition, in order to use a synonym, the user must have the appropriate access privileges for the object that the synonym refers to. For example, if you create a synonym for a view, then to select from that view using the synonym, the user would need SELECT privilege on the view.

ALL Object Privileges

You can grant all privileges for an object to a user with the ALL keyword. This grants a user the right to perform any operation on the object. The object owner and any user with the ADMIN privilege can execute the GRANT ALL and REVOKE ALL statements.

For example, GRANT ALL ON hr.employees TO pat grants all privileges for the employees table to user pat. It is possible to revoke individual privileges after granting all object privileges:

Command> GRANT ALL ON hr.employees TO pat;
Command> REVOKE DELETE ON hr.employees FROM pat;

You may also REVOKE ALL object privileges that were granted to a user for the object, as demonstrated here for user pat:

Command> REVOKE ALL ON hr.employees FROM pat;