![GOTO statement [T-SQL]](arrow-left.gif){kind=icon}
![HELP statement [DBISQL]](arrow-right.gif){kind=icon}
Gives 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
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, INSERT, DELETE ON employee TO Laurel
Allow the user Hardy to execute the Calculate_Report procedure.
GRANT EXECUTE ON Calculate_Report TO Hardy
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:
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 ""
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
The same command can also be used 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 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 IQ.
To create a user with no password, type:
GRANT CONNECT TO userid
Note that the user ID is not case sensitive.
A user with no password cannot connect to the database. This is useful when creating groups when you do not want anyone to connect to the group user ID. The password must be a valid identifier, as described in “Identifiers”.
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. See Chapter 12, “Managing User IDs and Permissions” in the Sybase IQ System Administration Guide for a complete description.
MEMBERSHIP IN GROUP userid,... 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.
Syntax 3 of the GRANT statement is used to grant permission on individual tables or views. The table permissions can be listed together, or specifying ALL grants all six permissions at once. The permissions have the following meaning:
RESOURCE Allows the user to create tables and views. In syntax 2, ALL is a synonym for RESOURCE that is compatible with Adaptive Server Enterprise.
ALL In syntax 3, this grants all of the permissions outlined below.
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, then the users will be allowed to reference only those columns. REFERENCES permissions on columns cannot be granted for views, only for tables.
SELECT [(column-name,...)] The users will be allowed to look at information in this view or table. If column names are specified, then 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. In order to update a table, users must have both SELECT and UPDATE permission on the table.
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.
Format 4 of the GRANT statement is used to grant permission to execute a procedure.
Format 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.
SQL/92 Syntax 3 is an entry-level feature. Syntax 4 is a Persistent Stored Module feature. Other syntaxes are vendor extensions.
Sybase Syntax 2 and 3 are supported in Adaptive Server Enterprise. The security model is different in Adaptive Server Enterprise and Sybase IQ, so other syntaxes differ.
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, or
You have DBA authority.
If you are changing another user's password, 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, or
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
