Privileges for SQL Objects
User access to database objects is authorized by granting privileges, either for a
single object or for that type of object anywhere in the database, through the
GRANT
statement. Access is removed through the REVOKE
statement.
This section covers the following:
Note:
Also, see Privileges for PL/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 explicitDELETE
privilege on the child table. However, a user must have theDELETE
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
orUPDATE
privilege on the child table, but not aSELECT
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 theCREATE ANY VIEW
privilege sopat
can create a view in the schema owned byjoe
. -
User
joe
must be granted theSELECT
object privilege onterry.table4
. -
User
joe
must be granted theSELECT
object privilege onpat.view2
-
User
pat
must be granted theSELECT
object privilege onterry.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 a private synonym in another user's schema. |
|
Create a 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 a 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;