![GOTO statement [T-SQL]](arrow-left.gif){kind=icon}
Gives permissions to specific users and creates 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 ] [ FROM userid ]
Syntax 4
GRANT EXECUTE ON [ owner.]procedure-name TO userid [, …]
Syntax 5
GRANT INTEGRATED LOGIN TO user_profile_name [, …] AS USER userid
Syntax 6
GRANT CREATE ON dbspace_name TO userid [, …]
Syntax 7
GRANT KERBEROS LOGIN TO client-Kerberos-principal, ... AS USER userid
Makes two new users for the database:
GRANT CONNECT TO Laurel, Hardy IDENTIFIED BY Stan, Ollie
Grants permissions on the Employees table to user Laurel:
GRANT SELECT, INSERT, DELETE ON Employees TO Laurel
Allows the user Hardy to execute the Calculate_Report procedure:
GRANT EXECUTE ON Calculate_Report TO Hardy
Gives users Lawrence and Swift CREATE permission on dbspace DspHist:
GRANT CREATE ON DspHist TO LAWRENCE, SWIFT
Grants CREATE privilege on dbspace DspHist to users Fiona and Ciaran:
GRANT CREATE ON DspHist TO Fiona, Ciaran
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:
Syntax 6 gives CREATE permission on the specified dbspace to the specified user(s) and/or group(s).
CONNECT TO userid,... Creates a new user. GRANT CONNECT can also be used by any user to change their own password.
Sybase recommends using the CREATE USER statement
to create users. See CREATE USER statement.
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.
If Login Management is enabled for the database, you
must use system procedures, not GRANT and REVOKE,
to add and remove user IDs.
To create a user with no password, enter:
GRANT CONNECT TO userid
The user ID is not case sensitive.
A user with no password cannot connect to the database. This is useful when you are creating groups and you do not want anyone to connect to the group user ID.
The password must be a valid identifier, as described in “Identifiers” in Chapter 2, “SQL Language Elements” in Reference: Building Blocks, Tables, and Procedures. Passwords have a maximum length of 255 bytes. If the database option VERIFY_PASSWORD_FUNCTION is set to a value other than the empty string, the GRANT CONNECT TO userid IDENTIFIED BY password 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. For details, see “VERIFY_PASSWORD_FUNCTION option”.
The following are invalid for database user IDs and passwords:
Names that begin with white space or single or double quotes
Names that end with white space
Names that contain semicolons
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 users to have members. See Chapter 8, “Managing User IDs and Permissions,” in the System Administration Guide: Volume 1 for a complete description.
MEMBERSHIP IN GROUP userid,… Allows users to inherit table permissions from a group and to reference tables created by the group without qualifying the table name.
If you do not want a specific user to access a particular table, view, or procedure, then do not make that user a member of a group that has permissions on that object.
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:
RESOURCE Allows the user to create tables and views. In syntax 2, ALL is a synonym for RESOURCE, which is compatible with Adaptive Server Enterprise.
ALL In syntax 3, this grants all of the permissions outlined below.
ALTER Users can alter this table with the ALTER TABLE statement. This permission is not allowed for views.
DELETE Users can delete rows from this table or view.
INSERT Users can insert rows into the named table or view.
REFERENCES [(column-name,...)] Users can create indexes on the named tables, and foreign keys that reference the named tables. If column names are specified, then users can reference only those columns. REFERENCES permissions on columns cannot be granted for views, only for tables.
SELECT [(column-name,...)] Users can look at information in this view or table. If column names are specified, then the users can look at only those columns. SELECT permissions on columns cannot be granted for views, only for tables.
UPDATE [(column-name,...)] Users can update rows in this view or table. If column names are specified, users can update only those columns. UPDATE permissions on columns cannot be granted for views, only for tables. 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 Employees table to user Laurel, enter:
GRANT SELECT, UPDATE ( street ) ON Employees 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.
Syntax 6 grants CREATE permission to the specified user(s) or group(s).
Syntax 7 creates a Kerberos authenticated login mapping from one or more Kerberos principals to an existing database user ID. This allows users who have successfully logged in to Kerberos (users who have a valid Kerberos ticket-granting ticket) to connect to a database without having to provide a user ID or password. For more information on this syntax, see “GRANT statement” in SQL Anywhere Server – SQL Reference > Using SQL > SQL statements > SQL statements (E-O).
Automatic commit.
SQL92 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
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.
For Syntax 6, you must have DBA authority.
For Syntax 7, you must have DBA authority.
