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

The PUBLIC keyword indicates that the privileges are granted to all roles, including those that you create later. PUBLIC is an implicitly defined group that includes all roles. A role has the privileges that are granted directly to the roles, the privileges that are granted to another role of which the role is a member, and the privileges that are granted to PUBLIC.

If you specify WITHGRANT OPTION, the recipient of the privileges can grant it to other roles. If you do not specify these keywords, the recipient cannot grant privileges. Grant options cannot be granted to PUBLIC.

You do not need to grant privileges to the owner of an object (usually the user who created the object), because the owner has all privileges by default. The owners can choose to revoke some of their own privileges for safety. Grantable privileges do not include the privileges to drop an object or alter its definition. The privileges that cannot be granted are inherent in the owner and cannot be granted or revoked. In addition, the owner implicitly has all grant options for the object.

Depending on the type of object, certain privileges can be granted to PUBLIC. The default privileges are non-public access for tables, and EXECUTE privileges for functions, procedures, and packages. The object owner can revoke these privileges. For maximum security, you can issue the REVOKE command in the same transaction that creates the object. This way, other users cannot use the object in any window.

The following table describes the possible privileges.

Privilege Description
SELECT Allows to SELECT from columns of the specified table, view, or sequence. For sequences, this privilege also allows you to use the currval function.
INSERT Allows to INSERT a new row into the specified table.
UPDATE Allows to UPDATE a column of the specified table. SELECT ... FOR UPDATE also requires this privilege in addition to the SELECT privilege.
DELETE Allows to DELETE a row from the specified table.
REFERENCES Allows to create foreign key constraints. If you want to create foreign key constraints, you must have this privilege on both the referencing and referenced tables.
EXECUTE Allows to use the specified package, stored procedure, or function. This privilege on a package allows you to use all public stored procedures, public functions, public variables, records, cursors, and other public objects and object types in the package. This is the only type of privilege that is applicable to functions, stored procedures, and packages.

The syntax for granting the EXECUTE privilege in PolarDB for Oracle is not fully compatible with Oracle databases. PolarDB for Oracle requires qualification of the program name by one of the following keywords: FUNCTION, PROCEDURE, and PACKAGE. However, in Oracle databases, these keywords must be omitted. For functions, PolarDB for Oracle require all input (IN and IN OUT) argument data types after the function name. If no function arguments exist, the function name must be followed by an empty pair of parenthesis. For stored procedures, if a procedure has one or more input arguments, you must specify all input argument data types. In Oracle, function and stored procedure signatures must be omitted. This is because all programs share the same namespace in Oracle. However, the functions, stored procedures, and packages have their own individual namespaces in PolarDB for Oracle. This allows program name overloading to a certain extent.

ALL PRIVILEGES Grants all available privileges at once.

For more information about the privileges that are required by other commands, see the topic of the corresponding command.