GRANT (Object Privileges)
Grants privileges
on database objects to users.
SYNTAX
| column_name |
Name
of the column you want to grant object privileges on. |
| table_name |
Name
of the table you want to grant object privileges on. |
| user_name |
Name
of the user you want to grant object privileges to. |
| group_name |
Name
of the group you want to grant object privileges to. |
DESCRIPTION
The GRANT
(object privileges) command grants access privileges on database objects
to individual users. To execute the GRANT (object privileges) command,
you must be the object owner, or have DBA or SYSADM security privileges.
Object privileges
control which database objects a user can access and the actions they
can perform. There are seven object privileges: SELECT, INSERT, DELETE,
UPDATE, INDEX, ALTER, and REFERENCE. The keywords ALL and ALL PRIVILEGES
can also be used to simultaneously grant all privileges on an object.
- SELECT
privilege allows you to select the data in a database object. This privilege
applies to the entire object and cannot be granted on specific columns.
- INSERT
privilege allows you to insert new data into a database object. This
privilege can also be restricted to specific columns.
- DELETE
privilege allows you to delete data from a database object. This privilege
applies to the entire object and cannot be granted on specific columns.
- UPDATE
privilege allows you to update data in a database object. This privilege
can also be restricted to specific columns.
- INDEX
privilege allows you to create an index on a database object. This privilege
applies to the entire object and cannot be granted on specific columns.
- ALTER
privilege allows you to alter the schema of a database object. This
privilege applies to the entire object and cannot be granted on specific
columns.
- REFERENCE
privilege allows you to create referential constraints (such as foreign
keys) on a database object. This privilege can also be restricted to
specific columns.
The user
who creates a schema object is the owner of that object. The owner and
any user with DBA or SYSADM security privileges automatically have all
object privileges on that object. System catalog tables belong to a special
virtual user called SYSTEM. All users including the SYSADM have only SELECT
privileges on system catalog tables. You cannot grant additional object
privileges on the system catalog tables.
You cannot
grant privileges on specific columns and on the entire database object
at the same time. If you wish to do so you must use the command twice,
once to grant privileges on specific columns, and once to grant privileges
on the entire table. It is possible to grant object privileges to all
users simultaneously by granting the privileges to PUBLIC. All current
and future users will then have those privileges on the database object.
EXAMPLES
The following
example grants SELECT, INSERT, and UPDATE object privileges on the Checks
table the user named Vivian.
GRANT SELECT, INSERT, UPDATE ON Checks TO Vivian
|
The
following example grants INSERT, UPDATE, and REFERENCE privilege on the
Amount, PayDate columns of the Checks table to the user named Jenny.
GRANT INSERT, UPDATE, REFERENCE (Amount, PayDate) ON Checks TO Jenny
|
The following
example grants all object privileges on the table Checks to the user named
John.
GRANT ALL ON Checks TO John
|
RELATED COMMANDS
<
GRANT (Execute Privileges) | Contents
| GRANT (Security Privileges) >
|
