GRANT Object-Level Privilege Statement

Grants database object-level privileges to a user or role.

Syntax

GRANT object-level-privilege [, …]
 ON [ owner.]object-name
 TO userID [,...]
 [ WITH GRANT OPTION ]

object-level-privilege:
  ALL [ PRIVILEGES ]
| ALTER 
| DELETE
| INSERT
| REFERENCES [ ( column-name [, …] ) ]
| SELECT [ ( column-name [, …] ) ]
| UPDATE [ ( column-name, … ) ] 
| LOAD
| TRUNCATE

Parameters

Usage

Grants privileges on individual tables or views. You can list the table privileges together, or specify ALL to grant all privileges at once. If you specify the WITH GRANT OPTION clause, the named user ID is also given privileges to grant the same privileges to other user IDs.
  • ALL – grants all privileges to users
  • ALTER – users can alter this table with the ALTER TABLE statement. This privilege is not allowed for views.
  • DELETE – users can delete rows from this table or view.
  • INSERT – users can insert rows into the named table or view.
  • LOAD – users can load data into the named table or view.

    These server switches may impact a user's ability to execute the LOAD command:

    • -gl NONE – no one can execute the LOAD or UNLOAD command on a table.
    • -gl ALL – users with ALTER ANY TABLE or LOAD ANY TABLE system privilege can execute LOAD command on any table. Table owners or users with ALTER or LOAD privilege on a given table can execute LOAD command on the table. Users with SELECT ANY TABLE system privilege or SELECT privilege on a given table can execute the UNLOAD command.
    • -gl DBA – users with ALTER ANY TABLE or LOAD ANY TABLE system privilege can execute the LOAD command on any table.
  • REFERENCES – users can create indexes on the named tables, and foreign keys that reference the named tables. If column names are specified, then users can reference only those columns. REFERENCES privileges on columns cannot be granted for views, only for tables.
  • SELECT – users can look at information in this view or table. If column names are specified, then the users can look at only those columns. SELECT permissions on columns cannot be granted for views, only for tables.
  • TRUNCATE – users can truncate the named table or view.
  • UPDATE – users can update rows in this view or table. If column names are specified, users can update only those columns. UPDATE privileges on columns cannot be granted for views, only for tables. To update a table, users must have both SELECT and UPDATE privilege on the table.

Standards

  • SQL – Syntax is an entry-level feature.

  • Sybase – Syntax is supported in Adaptive Server Enterprise.

Permissions

Requires one of:
  • MANAGE ANY OBJECT PRIVILEGE system privilege
  • You have been granted the specific object privilege with the WITH GRANT OPTION clause on the table.
  • You own of the table.
Parent topic: SQL Statements