Privileges for object types exist at the system level and the schema object level.
7.1.1 System Privileges for Object Types
Oracle database defines the following system privileges for object types:
TYPEenables you to create object types in your own schema
TYPEenables you to create object types in any schema
TYPEenables you to alter object types in any schema
TYPEenables you to drop named types in any schema
TYPEenables you to use and reference named types in any schema
TYPEenables you to create subtypes under any non-final object types
VIEWenables you to create subviews under any object view
The following roles are helpful:
RESOURCErole includes the
The DBA role includes all of these privileges.
7.1.2 Schema Object Privileges
Two schema object privileges apply to object types:
EXECUTEenables you to use the type to:
Define a table.
Define a column in a relational table.
Declare a variable or parameter of the named type.
EXECUTElets you invoke the methods of a type, including the constructor.
Method execution and the associated permissions are the same as for stored PL/SQL procedures.
UNDERenables you to create a subtype or subview under the type or view on which the privilege is granted.
Only a grantor with the
OPTIONon the direct supertype or superview can grant the
UNDERprivilege on a subtype or subview.
OPTION grants a specified object privilege on all subtypes of the object. This option is meaningful only with the
SELECT object privilege granted on an object view in an object view hierarchy. In this case, the privilege applies to all subviews of the view on which the privilege is granted.
7.1.3 Types Used in New Types or Tables
In addition to the permissions detailed in the previous sections, you need specific privileges to:
Create types or tables that use types created by other users.
Grant use of your new types or tables to other users.
You must have either the
TYPE system privilege or the
EXECUTE object privilege for any type used to define a new type or table. You must have been granted these privileges explicitly, and not through a role.
To grant access to your new type or table to other users, you must have either the required
EXECUTE object privileges with the
GRANT option or the
TYPE system privilege with the option
OPTION. You must have been granted these privileges explicitly, not through a role.
7.1.4 Example: Privileges on Object Types
This section presents several related examples, creating users or schemas and then granting privileges on them.
Example 7-1 creates three users or schemas,
USER3, and grants them the
RESOURCE roles. Some of the subsequent examples in this chapter use these schemas.
This example requires you to create and use several passwords. If you plan to run the example, make these changes to your SQL code first.
For simplicity, this example does not perform the password management techniques that a deployed system normally uses. In a production environment, follow the Oracle Database password management guidelines, and disable any sample accounts.
See Oracle Database Security Guide for password management guidelines and other security recommendations.
Example 7-1 Creating User Schemas
-- Requires passwords CONNECT SYSTEM -- Enter password CREATE USER user1 PROFILE default IDENTIFIED BY password DEFAULT TABLESPACE example ACCOUNT UNLOCK; GRANT CREATE SESSION TO user1; GRANT RESOURCE TO user1; GRANT CREATE SYNONYM TO user1; GRANT CREATE PUBLIC SYNONYM TO user1; GRANT DROP PUBLIC SYNONYM TO user1; CREATE USER user2 PROFILE default IDENTIFIED BY password DEFAULT TABLESPACE example ACCOUNT UNLOCK; GRANT CREATE SESSION TO user2; GRANT RESOURCE TO user2; CREATE USER user3 PROFILE default IDENTIFIED BY password DEFAULT TABLESPACE example ACCOUNT UNLOCK; GRANT CREATE SESSION TO user3; GRANT RESOURCE TO user3;
Example 7-2 requires the input of a password,
USER1 performs the
GRANT Data Definition Language (DDL) statements in the
Example 7-2 Granting Privileges on Object Types
CREATE TYPE type1 AS OBJECT ( attr1 NUMBER ); / CREATE TYPE type2 AS OBJECT ( attr2 NUMBER ); / GRANT EXECUTE ON type1 TO user2; GRANT EXECUTE ON type2 TO user2 WITH GRANT OPTION;
In Example 7-3,
USER2 performs the
CREATE DDL statement in the
Example 7-3 Performing DDL Statements in USER2 Schema
-- Requires Ex. 7-1, 7-2 and password input CONNECT user2 -- Enter password CREATE TABLE tab1 OF user1.type1; CREATE TYPE type3 AS OBJECT ( attr3 user1.type2 ); / CREATE TABLE tab2 (col1 user1.type2 );
In Example 7-4, the first two statements succeed because
USER2 was granted the
EXECUTE privilege with the
GRANT option on
TYPE2 in the last line of Example 7-2 and Example 7-3 created
type3 as an object using
However, the last grant Example 7-4 fails because
USER2 has not been granted the
EXECUTE privilege with the
GRANT option on
Example 7-4 Performing Grants to USER3
-- Requires Ex. 7-1, 7-2, and 7-3 GRANT EXECUTE ON type3 TO user3; GRANT SELECT ON tab2 TO user3; -- Privileges on Object Types GRANT SELECT ON tab1 TO user3 -- incorrect statement;
In Example 7-5,
USER3 has the necessary privileges to perform the following actions:
Example 7-5 Creating Tables and Types
-- Requires Ex. 7-1, 7-2, 7-3, and 7-4 CONNECT user3 -- Enter password CREATE TYPE type4 AS OBJECT (attr4 user2.type3); / CREATE TABLE tab3 OF type4;
7.1.5 Access Privileges on Objects, Types, and Tables
Object types only make use of the
However, object tables use all the same privileges as relational tables:
SELECTlets you access an object and its attributes from the table.
UPDATElets you modify attributes of objects in the table.
INSERTlets you add new objects to the table.
DELETElets you delete objects from the table.
Similar table and column privileges regulate the use of table columns of object types.
Selecting columns of an object table does not require privileges on the type of the object table. Selecting the entire row object, however, does.
Consider the schema and queries created below in Example 7-6:
Example 7-6 SELECT Privileges on Type Access
-- Requires Ex. 7-1, 7-2, 7-3, 7-4, and 7-5 CREATE TYPE emp_type AS OBJECT ( eno NUMBER, ename VARCHAR2(36)); / CREATE TABLE emp OF emp_type; // an object table GRANT SELECT on emp TO user1; SELECT VALUE(e) FROM emp e; SELECT eno, ename FROM emp;
For both queries, Oracle database checks the user's
SELECT privilege for the object table
emp. For the first query, the user needs to obtain the
emp_type type information to interpret the data. When the query accesses the
emp_type type, the database checks the user's
The second query, however, does not involve named types, so the database does not check type privileges.
USER3 can perform queries such as these:
SELECT t.col1.attr2 from user2.tab2 t; SELECT t.attr4.attr3.attr2 FROM tab3 t;
Note that in both queries,
USER3 does not have explicit privileges on the underlying type. However, the statement succeeds because the type and table owners have the necessary privileges with the
Oracle database checks privileges on the following requests and returns an error if the requestor does not have the privilege for the action:
Pinning an object in the object cache using its
REFvalue causes the database to check the
SELECTprivilege on the object table containing the object and the
EXECUTEprivilege on the object type.
Modifying an existing object or flushing an object from the object cache causes the database to check the
UPDATEprivilege on the destination object table. Flushing a new object causes the database to check the
INSERTprivilege on the destination object table.
Deleting an object causes the database to check the
DELETEprivilege on the destination table.
Invoking a method causes the database to check the
EXECUTEprivilege on the corresponding object type.
Oracle database does not provide column level privileges for object tables.
Oracle Call Interface Programmer's Guide for tips and techniques for using OCI program effectively with objects