Reference Manual
   CHAPTER 9. SQL Statements     

GRANT statement

Function 

To give permissions to specific users and to create new user IDs.

Syntax 1 

GRANT CONNECT TO userid,... IDENTIFIED BY password,...

Syntax 2 

GRANT {
DBA ,
GROUP ,
MEMBERSHIP IN GROUP userid,...,
[ RESOURCE | ALL ]
}
... TO userid,...

Syntax 3 

GRANT {
ALL [ PRIVILEGES ] ,
ALTER ,
DELETE ,
INSERT ,
REFERENCES [ ( column-name,... ) ] ,
SELECT [ ( column-name,... ) ] ,
UPDATE [ ( column-name,... ) ] ,
}
... ON [ owner.]table-name
... TO userid, ...
[ WITH GRANT OPTION ]

Syntax 4 

GRANT EXECUTE ON [ owner.]procedure-name TO userid,...

Syntax 5 

GRANT INTEGRATED LOGIN TO user_profile_name,... AS USER userid

Permissions 

For Syntax 1 or 2 one of the following conditions must be met.

• You are changing your own password using GRANT CONNECT.

• You have DBA authority.

If you are changing another user's password (with DBA authority), the other user must not be connected to the database.

For Syntax 3, one of the following conditions must be met:

• You created the table

• You have been granted permissions on the table with GRANT OPTION.

• You have DBA authority

For Syntax 4, one of the following conditions must be met:

• You created the procedure

• You have DBA authority

For Syntax 5, the following condition must be met:

• You have DBA authority

Side effects 

Automatic commit.

See also 

REVOKE statement

Description 

The GRANT statement is used to grant database permissions to individual user IDs and groups. It is also used to create and delete users and groups.

Special privileges 

Syntax 1 and 2 of the GRANT statement are used for granting special privileges to users as follows:

CONNECT TO userid,...     Creates a new user. GRANT CONNECT can also be used by any user to change their own password. To create a user with the empty string as the password, type:

GRANT CONNECT TO userid IDENTIFIED BY ""

To create a user with no password, type:

GRANT CONNECT TO userid

A user with no password cannot connect to the database. This is useful if you are creating a group and do not want anyone to connect to the database using the group user ID. The password must be a valid identifier, as described in Statement elements.

DBA     Database Administrator authority gives a user permission to do anything. This is usually reserved for the person in the organization who is looking after the database.

GROUP     Allows the user(s) to have members.

     For more information, see Managing groups.

MEMBERSHIP IN GROUP     This allows the user(s) to inherit table permissions from a group and to reference tables created by the group without qualifying the table name.

     For more information, see Managing groups.

Syntax 3 of the GRANT statement is used to grant permission on individual tables or views. The table permissions can be specified individually, or by specifying ALL grants all six permissions at once.

RESOURCE     Allows the user to create tables and views. In syntax 2, ALL is a synonym for RESOURCE that is compatible with Sybase Adaptive Server Enterprise.

ALL     In Syntax 3, this grants all of the permissions outlined below.

Permissions 

The permissions have the following meaning:

ALTER     The users will be allowed to alter this table with the ALTER TABLE statement. This permission is not allowed for views.

DELETE     The users will be allowed to delete rows from this table or view.

INSERT     The users will be allowed to insert rows into the named table or view.

REFERENCES [(column-name,...)]     The users will be allowed to create indexes on the named tables, and foreign keys which reference the named tables. If column names are specified, the users will be allowed to reference only those columns. REFERENCES permissions on columns cannot be granted for views, only for tables.

INDEX is a synonym for REFERENCES.

SELECT [(column-name,...)]     The users will be allowed to look at information in this view or table. If column names are specified, the users will be allowed to look at only those columns. SELECT permissions on columns cannot be granted for views, only for tables.

UPDATE [(column-name,...)]     The users will be allowed to update rows in this view or table. If column names are specified, the users will be allowed to update only those columns. UPDATE permissions on columns cannot be granted for views, only for tables.

Other notes 

If WITH GRANT OPTION is specified, then the named user ID is also given permission to GRANT the same permissions to other user IDs.

Syntax 4 of the GRANT statement is used to grant permission to execute a procedure.

Syntax 5 of the GRANT statement creates an explicit integrated login mapping between one or more Windows NT user profiles and an existing database user ID, allowing users who successfully log in to their local machine to connect to a database without having to provide a user ID or password.

     For more information on integrated logins see Using an integrated login.

Standards and compatibility 

• SQL/92     Syntax 3 is an entry-level feature. Syntax 4 is a Persistent Stored Module feature. Other syntaxes are vendor extensions.

• Sybase     Syntaxes 2 and 3 are supported in Adaptive Server Enterprise. The security model is different in Adaptive Server Enterprise and Adaptive Server Anywhere, so other syntaxes differ.

Examples 

• Make two new users for the database.

  GRANT
  CONNECT TO Laurel, Hardy
  IDENTIFIED BY Stan, Ollie

• Grant permissions on the employee table to user Laurel.

  GRANT
  SELECT, UPDATE ( street )
  ON employee
  TO Laurel

  More than one permission can be granted in a single statement. The permissions are separated by commas.

• Allow the user Hardy to execute the Calculate_Report procedure.

  GRANT
  EXECUTE ON Calculate_Report
  TO Hardy