GRANT statement

Description

Gives permissions to specific users and creates new user IDs.

Syntax

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

Examples

Example 1

• Makes two new users for the database:

GRANT
CONNECT TO Laurel, Hardy
IDENTIFIED BY Stan, Ollie

Example 2

• Grants permissions on the employee table to user Laurel:

GRANT
SELECT, INSERT, DELETE
ON employee
TO Laurel

Example 3

• Allows the user Hardy to execute the Calculate_Report procedure:

GRANT
EXECUTE ON Calculate_Report
TO Hardy

Usage

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.

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

To create a user with the empty string as the password, enter:

GRANT CONNECT TO userid IDENTIFIED BY ""

If you have DBA authority, you can change the password of any existing user with the following command:

GRANT CONNECT TO userid IDENTIFIED BY password

You can also use the same command to add a new user. For this reason, if you inadvertently enter the user ID of an existing user when you mean to add a new user, you are actually changing the password of the existing user. You do not receive a warning because this behavior is considered normal. This behavior differs from pre-version 12 Sybase IQ.

To avoid this situation, use the system procedures sp_addlogin and sp_adduser to add users. These procedures give you an error if you try to add an existing user ID, as in Adaptive Server Enterprise, and pre-version 12 Sybase IQ.

To create a user with no password, enter:

GRANT CONNECT TO userid

The user ID is not case sensitive.

Syntax 3 of the GRANT statement is used to grant permission on individual tables or views. You can list the table permissions together, or specify ALL to grant all six permissions at once. The permissions have the following meaning:

For example, to grant SELECT and UPDATE permissions on the employee table to user Laurel, enter:

GRANT
SELECT, UPDATE ( street )
ON employee
TO Laurel

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 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.

Automatic commit.

Standards

Permissions

• For Syntax 1 or 2, one of the three following conditions must be met:

  • You are changing your own password using GRANT CONNECT

  • You are adding members to your own user ID

  • You have DBA authority.

  If you are changing another user’s password, the other user cannot 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, you must have DBA authority.

See also