Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

GOTO statement [T-SQL] Next Page

GRANT statement


Use this statement to create new user IDs, to grant or change permissions for the specified users, and to create or change passwords.

Syntax 1

GRANT CONNECT TO userid, ...
[ AT starting-id ]
[ IDENTIFIED BY password, ... ]

Syntax 2

GRANT permission, ...
TO userid, ...

permission :
DBA
| BACKUP
| VALIDATE
| GROUP
| MEMBERSHIP IN GROUP userid, ...
| [ RESOURCE | ALL ]

Syntax 3

GRANT permission, ...
ON [ owner.]table-name
TO userid, ...
[ WITH GRANT OPTION ]
[ FROM userid ]

permission :
ALL [ PRIVILEGES ]
| ALTER
| DELETE
| INSERT
| REFERENCES [ ( column-name, ... ) ]
| SELECT [ ( column-name, ... ) ]
| UPDATE [ ( column-name, ... ) ]

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 KERBEROS LOGIN TO client-Kerberos-principal, ...
AS USER userid

Parameters

CONNECT TO    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, use:

GRANT CONNECT TO userid IDENTIFIED BY ""

To create a user with no password, use:

GRANT CONNECT TO userid

A user with no password cannot connect to the database. This is useful if you are creating a group and do not want anyone to connect to the database using the group user ID. A user ID must be a valid identifier, as described in Identifiers. User IDs and passwords cannot:

A password can be either a valid identifier, as described in Identifiers, or a string (maximum 255 bytes) placed in single quotes. Passwords are case sensitive. It is recommended that the password be composed of 7-bit ASCII characters, as other characters may not work correctly if the database server cannot convert from the client's character set to UTF-8.

The verify_password_function option can be used to specify a function to implement password rules (for example, passwords must include at least one digit). If a password verification function is used, you cannot specify more than one user ID and password in the GRANT CONNECT statement. See verify_password_function option [database].

AT starting-id    This clause is not for general purpose use. The clause specifies the internal numeric value to be used for the first user ID in the list.

The clause is implemented primarily for use by the Unload utility.

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.

BACKUP    Backup authority gives a user permission to back up the database.

VALIDATE    Validate authority gives a user permission to perform the validation operations supported by the various VALIDATE statements, such as validating the database, validating tables and indexes, and validating checksums. As such, it also allows the user to use the Validation utility (dbvalid), and the Validate Database wizard in Sybase Central.

GROUP    Allows the user(s) to have members. See Managing groups.

MEMBERSHIP IN GROUP    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. See Managing groups.

Syntax 3 of the GRANT statement is used to grant permission on individual tables or views. The table permissions can be specified individually, or you can use ALL to grant all six permissions at once.

RESOURCE    Allows the user to create tables and views. In syntax 2, ALL is a synonym for RESOURCE that is compatible with Sybase Adaptive Server Enterprise.

ALL    In Syntax 3, this grants ALTER, DELETE, INSERT, REFERENCES, SELECT, and UPDATE permissions.

ALTER    The users are allowed to alter the named table with the ALTER TABLE statement. This permission is not allowed for views.

DELETE    The users are allowed to delete rows from the named table or view.

INSERT    The users are allowed to insert rows into the named table or view.

REFERENCES [ (column-name, ...) ]    The users are allowed to create indexes on the named table, and foreign keys that reference the named tables. If column names are specified, the users are allowed to reference only those columns. REFERENCES permissions on columns cannot be granted for views, only for tables. INDEX is a synonym for REFERENCES.

SELECT [ (column-name, ...) ]    The users are allowed to look at information in the view or table. If column names are specified, the users are allowed to look at only those columns. SELECT permissions on columns cannot be granted for views, only for tables.

UPDATE [ (column-name, ...) ]    The users are allowed to update rows in the view or table. If column names are specified, the users are allowed to update only those columns.

FROM    If FROM userid is specified, the userid is recorded as a grantor user ID in the system tables. This clause is for use by the Unload utility (dbunload). Do not use or modify this option directly.

Remarks

The GRANT statement is used to grant database permissions to individual user IDs and groups. It is also used to create users and groups.

If WITH GRANT OPTION is specified, then the named user ID is also given permission to GRANT the same permissions to other user IDs. Members of groups do not inherit the WITH GRANT OPTION if it is granted to a group.

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 or group profiles and an existing database user ID, allowing users who successfully log in to their local computer to connect to a database without having to provide a user ID or password. The user-profile-name can be of the form domain\user-name. The database user ID the integrated login is mapped to must have a password. See Using integrated logins.

Syntax 6 of the GRANT statement 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. The database user ID the Kerberos login is mapped to must have a password. The client-Kerberos-principal must have the format user/instance@REALM, where /instance is optional. The full principal, including the realm, must be specified, and principals that differ only in the instance or realm are treated as different.

Principals are case sensitive and must be specified in the correct case. Mappings for multiple principals that differ only in case are not supported (for example, you cannot have mappings for both jjordan@MYREALM.COM and JJordan@MYREALM.COM).

If no explicit mapping is made for a Kerberos principal, and the Guest database user ID exists and has a password, then the Kerberos principal connects using the Guest database user ID (the same Guest database user ID as for integrated logins).

For more information about Kerberos authentication, see Using Kerberos authentication.

Permissions

Syntax 1 or 2    You must either be changing your own password using GRANT CONNECT, or have DBA authority. If you are changing another user's password (with DBA authority), the other user must not be connected to the database.

Syntax 3    If the FROM clause is specified you must have DBA authority. Otherwise, you must either own the table, or have been granted permissions on the table WITH GRANT OPTION.

Syntax 4    You must either own the procedure, or have DBA authority.

Syntax 5 or 6    You must have DBA authority.

Side effects

Automatic commit.

See also
Standards and compatibility
Example

Make two new users for the database.

GRANT
CONNECT TO Laurel, Hardy
IDENTIFIED BY Stan, Ollie;

Grant permissions on the Employees table to user Laurel.

GRANT
SELECT, UPDATE ( Street )
ON Employees
TO Laurel;

More than one permission can be granted in a single statement. Separate the permissions with commas.

Allow the user Hardy to execute the Calculate_Report procedure.

GRANT EXECUTE ON Calculate_Report
TO Hardy;