GRANT CONNECT Statement

GRANT CONNECT can be used to create a new user or also be used by any user to change their own password.

Tip: Use the CREATE USER statement rather than the GRANT CONNECT statement to create users.

Syntax

GRANT CONNECT 
 TO userID [,...]
 IDENTIFIED BY password [,...]

Parameters

userID – must be the name of an existing user or role that has a login password. Separate multiple userIDs with commas.

Examples

Example 1 – creates two new users for the database named Laurel and Hardy.
```
GRANT CONNECT TO Laurel, Hardy
IDENTIFIED BY Stan, Ollie
```
Example 2 – creates user Jane with no password.
```
GRANT CONNECT TO Jane 
```
Example 3 – changes the password for Bob to newpassword.
```
GRANT CONNECT TO Bob IDENTIFIED BY newpassword
```

Usage

The same command can be used to both create a new user or change the password of an existing user. If you inadvertently enter the user ID of an existing user when you are trying 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.

The stored procedures sp_addlogin and sp_adduser can also be used to add users. These procedures display an error if you try to add an existing user ID.

Note: Use system procedures, not GRANT and REVOKE, to add and remove user IDs.

A user without a password cannot connect to the database. This is useful when you are creating groups and you do not want anyone to connect to the role user ID. To create a user without a password, do not include the IDENTIFIED BY clause.

When specifying a password, it must be a valid identifier Passwords have a maximum length of 255 bytes. If the VERIFY_PASSWORD_FUNCTION database option is set to a value other than the empty string, the GRANT CONNECT TO statement calls the function identified by the option value. The function returns NULL to indicate that the password conforms to rules. If the VERIFY_PASSWORD_FUNCTION option is set, you can specify only one userid and password with the GRANT CONNECT statement.

Invalid names for database user IDs and passwords include those that:

Begin with white space or single or double quotes
End with white space
Contain semicolons

Standards

SQL – Other syntaxes are vendor extensions to ISO/ANSI SQL grammar.
Sybase – The security model is different in Adaptive Server Enterprise and SAP Sybase IQ, so other syntaxes differ.

Permissions

If you are creating a new user, you must have the MANAGE ANY USER system privilege.
Any user can change his or her own password.
If you are changing another user’s password, you must have the CHANGE PASSWORD system privilege.

Note: If you are changing another user’s password, the other user cannot be connected to the database.