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 to
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, and packages.
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.