What are schema privileges?

What are schema and object privileges?

You can use Oracle Portal to grant database object-level privileges and schema-level privileges. Object level privileges are granted on an object-by-object basis and apply only to Oracle database user accounts (schemas) or roles. Schema-level privileges are granted on a schema-by-schema basis and apply only Oracle Portal users or groups.

What are object-level privileges?

An object-level privilege is a permission granted to an Oracle database user account or role to perform some action on a database object. These object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX on tables and views and EXECUTE on procedures, functions, and packages. They can be granted through Portal or using Oracle database commands. You typically grant object-level privileges to give access to objects needed to build a provider. The privileges are granted to the schema that maps to the provider, not to the provider itself. In the figure below, the provider on the left side maps to provider Schema 1. Schema 1 has object level privileges on the tables in Schema 2.

Schema 1

Schema 2

What are schema-level access privileges?

A schema-level privilege is granted to a Oracle Portal user or group to perform some action on a schema, for example, inserting rows in any table in the schema or modifying the definition of objects in the schema. Schema level privileges are specific to Oracle Portal and are valid only for schemas and objects accessed through the Database Objects Navigator.

For example, you can grant the INSERT privilege that allows a Oracle Portal user to insert new rows into any table owned by the specified schema. This user cannot insert a row into a table using Oracle database commands, but can through the Modify Rows action in the Database Objects Navigator.

Schema-level access privileges

Manage

Modify

Insert

View

Drop the schema and all of its objects from the database.

Drop any object in the schema from the database.

Build a new database object.

Edit any object definition, for example add a new constraint on a table.

Export database objects.

Grant schema-level privileges to Oracle Portal users and groups.

Grant object-level privileges such as EXECUTE, INSERT, UPDATE, and DELETE to Oracle database user accounts and roles.

Modify (update, delete) and query rows in all tables and base tables for views in the schema.

Compile a function, procedure or package.

Execute a function or procedure.

Insert new rows into all tables and base tables for views in the schema.

View object properties.

Get the next value in a sequence.

View and query all objects in the schema.

Object-level access privileges

Privilege

Enables user to

ALTER

Change the definition of an object.

DELETE

Remove rows from a table or view's base table.

EXECUTE

Execute a procedure, function, or Java source.

INDEX

Create an index on a specified table or view.

INSERT

Insert rows into a table or a view's base table.

REFERENCES

Create a constraint on a table or view.

SELECT

Retrieve data from one or more tables or views.

UPDATE

Change existing values in a table or view's base table.

You can also assign the Grant Option privilege on an object to another Oracle database user account or Public. With this privilege, the user or can in turn assign privileges on the object to another database user or role.

Notes

You can assign the With Grant Option to database users or Public, but not to database roles.
You cannot grant the INDEX and REFERENCES object-level privileges to a database role.

	Manage	Modify	Insert	View
Drop the schema and all of its objects from the database.
Drop any object in the schema from the database.
Build a new database object.
Edit any object definition, for example add a new constraint on a table.
Export database objects.
Grant schema-level privileges to Oracle Portal users and groups.
Grant object-level privileges such as EXECUTE, INSERT, UPDATE, and DELETE to Oracle database user accounts and roles.
Modify (update, delete) and query rows in all tables and base tables for views in the schema.
Compile a function, procedure or package.
Execute a function or procedure.
Insert new rows into all tables and base tables for views in the schema.
View object properties.
Get the next value in a sequence.
View and query all objects in the schema.

Privilege	Enables user to
ALTER	Change the definition of an object.
DELETE	Remove rows from a table or view's base table.
EXECUTE	Execute a procedure, function, or Java source.
INDEX	Create an index on a specified table or view.
INSERT	Insert rows into a table or a view's base table.
REFERENCES	Create a constraint on a table or view.
SELECT	Retrieve data from one or more tables or views.
UPDATE	Change existing values in a table or view's base table.