![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
{ BACKUP
| DBA
| GROUP
| MEMBERSHIP IN GROUP userid [, …]
| MULTIPLEX ADMIN
| OPERATOR
| PERMS ADMIN
| PROFILE
| READCLIENTFILE
| READFILE
| [ RESOURCE | ALL ]
| SPACE ADMIN
| USER ADMIN
| VALIDATE
| WRITECLIENTFILE }
... 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 are used for granting special privileges to users as follows:
BACKUP Grants the authority to back up the database. See ”GRANT statement” in SQL Anywhere Server – SQL Reference.
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.
MULTIPLEX ADMIN Allows users to perform multiplex administration tasks such as creating and deleting multiplex servers. See “MULTIPLEX ADMIN authority overview”in Chapter 2, “Managing Multiplex Servers,” in the Using Sybase IQ Multiplex for a complete description.
PROFILE Grants the user the authority to perform profiling and diagnostic operations. See ”GRANT statement” in SQL Anywhere Server – SQL Reference.
READCLIENTFILE Grants the user the authority to read from a file on the client computer, for example, when loading data. See ”GRANT statement” in SQL Anywhere Server – SQL Reference.
READFILE Allows the user to execute a SELECT statement against a file using the OPENSTRING clause. See ”GRANT statement” in SQL Anywhere Server – SQL Reference.
RESOURCE Allows the user to create database objects such as tables, views, and stored procedures. In syntax 2, ALL is a synonym for RESOURCE, which is compatible with Adaptive Server Enterprise.
OPERATOR Allows users to checkpoint and backup databases, drop connections, and monitor the system. See Chapter 8, “Managing User IDs and Permissions,” in the System Administration Guide: Volume 1 for a complete description.
PERMS ADMIN Allows users to manage data permissions, groups, authorities and passwords. See Chapter 8, “Managing User IDs and Permissions,” in the System Administration Guide: Volume 1 for a complete description.
SPACE ADMIN Allows users to manage dbspaces. See Chapter 8, “Managing User IDs and Permissions,” in the System Administration Guide: Volume 1 for a complete description.
USER ADMIN Allows users to manage users, external logins, and login policies. See Chapter 8, “Managing User IDs and Permissions,” in the System Administration Guide: Volume 1 for a complete description.
VALIDATE Allows users to perform the validation operations supported by the various VALIDATE statements, such as validating the database, validating tables and indexes, and validating checksums. It also allows the user to use the Validation utility (dbvalid), and the Validate Database wizard in Sybase Central. See ”GRANT statement” in SQL Anywhere Server – SQL Reference.
WRITECLIENTFILE Grants the user the authority to write to a file on the client computer, for example, when unloading data. See ”GRANT statement” in SQL Anywhere Server – SQL Reference.
Syntax 3 grants 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 these meanings:
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
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 or PERMS ADMIN authority, you can change the password of any existing user with this 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.
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. See “VERIFY_PASSWORD_FUNCTION option”.
These 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
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 is used to grant permission to execute a procedure.
Syntax 5 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. See “GRANT statement” in SQL Anywhere Server – SQL Reference > Using SQL > SQL statements > SQL statements (E-O).
Automatic commit.
SQL Syntax 3 is an entry-level feature. Syntax 4 is a Persistent Stored Module feature. Other syntaxes are vendor extensions to ISO/ANSI SQL grammar.
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, one of these conditions must be met:
If you are creating a new user, you must have DBA or USER ADMIN authority.
You are changing your own password.
If you are changing another user’s password, you must have DBA or PERMS ADMIN authority.
If you are changing another user’s password, the other user cannot be connected to the database.
For Syntax 2, one of these conditions must be met:
To grant DBA authority to any user, you must have DBA authority.
To GRANT GROUP, GRANT MEMBERSHIP IN GROUP, or grant any other authority to any user, you must have DBA or PERMS ADMIN authority.
For Syntax 3, one of these conditions must be met:
You created the table.
You have been granted permissions on the table with GRANT OPTION.
You have DBA or PERMS ADMIN authority.
For Syntax 4, one of these conditions must be met:
You created the procedure.
You have DBA or PERMS ADMIN authority.
For Syntax 5, you must have DBA or USER ADMIN authority.
For Syntax 6, you must have DBA or SPACE ADMIN authority.
For Syntax 7, you must have DBA or USER ADMIN authority.
