GRANT CONNECT Statement

Quick Links:

Syntax

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

Parameters

(back to top)

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

Examples

(back to top)

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

(back to top)

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.

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

(back to top)

SQL–Other syntaxes are vendor extensions to ISO/ANSI SQL grammar.
SAP Database products–The security model is different in SAP ASE and SAP IQ, so other syntaxes differ.

Permissions

(back to top)

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.