Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :

Previous PageTable Of ContentsNext Page

3.3.45 GRANT on Database Objects

This variant of the GRANT command gives specific privileges on a database object to a role. These privileges are added to those already granted, if any.

The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that may be created later. PUBLIC may be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.

If the WITH GRANT OPTION is specified, the recipient of the privilege may in turn grant it to others. Without a grant option, the recipient cannot do that. Grant options cannot be granted to PUBLIC.

There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default. (The owner could, however, choose to revoke some of his own privileges for safety.) The right to drop an object or to alter its definition in any way is not described by a grantable privilege; it is inherent in the owner, and cannot be granted or revoked. The owner implicitly has all grant options for the object as well.

Depending on the type of object, the initial default privileges may include granting some privileges to PUBLIC. The default is no public access for tables and EXECUTE privilege for functions, procedures, and packages. The object owner may of course revoke these privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user may use the object.)

The possible privileges are:


      Allows SELECT from any column of the specified table, view, or sequence. For sequences, this privilege also allows the use of the currval function.


      Allows INSERT of a new row into the specified table.


Allows UPDATE of a column of the specified table. SELECT ... FOR UPDATE also requires this privilege (besides the SELECT privilege).


Allows DELETE of a row from the specified table.


To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced tables.


Allows the use of the specified package, procedure, or function. When applied to a package, allows the use of all of the package’s public procedures, public functions, public variables, records, cursors and other public objects and object types. This is the only type of privilege that is applicable to functions, procedures, and packages.

The Postgres Plus Advanced Server syntax for granting the EXECUTE privilege is not fully Oracle compatible. Postgres Plus Advanced Server requires qualification of the program name by one of the keywords, FUNCTION, PROCEDURE, or PACKAGE whereas these keywords must be omitted in Oracle. In addition for functions, Postgres Plus Advanced Server requires all input (IN, IN OUT) argument data types after the function name (including an empty parenthesis if there are no function arguments). For procedures, all input argument data types must be specified if the procedure has one or more input arguments. In Oracle, function and procedure signatures must be omitted. This is due to the fact that all programs share the same namespace in Oracle, whereas functions, procedures, and packages have their own individual namespace in Postgres Plus Advanced Server to allow program name overloading to a certain extent.


Grant all of the available privileges at once.

The privileges required by other commands are listed on the reference page of the respective command.

Previous PageTable Of ContentsNext Page