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.
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
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
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.
|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,
The syntax for granting the
|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.