GRANT Statement

Gives permissions to specific users and creates new user IDs.

Syntax

Syntax 1 – Grant authorities

GRANT authority, …
  TO userid, …

authority:
  BACKUP
| DBA
| GROUP
| MEMBERSHIP IN GROUP userid [, …]
| MULTIPLEX ADMIN
| OPERATOR
| PERMS ADMIN
| PROFILE
| READCLIENTFILE
| READFILE
| [ RESOURCE | ALL ]
| SPACE ADMIN
| USER ADMIN
| VALIDATE
| WRITECLIENTFILE

Syntax 2 – Grant group status or membership in a group 

GRANT { GROUP | MEMBERSHIP IN GROUP userid, … }
  TO userid, …

Syntax 3 – Grant database object permissions

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 permission

GRANT EXECUTE ON [ owner.]procedure-name
 TO userid [, …] 

Syntax 5 – Grant integrated login

GRANT INTEGRATED LOGIN TO user_profile_name [, …]
 AS USER userid

Syntax 6 – Grant Kerberos login

GRANT KERBEROS LOGIN TO client-Kerberos-principal, …
 AS USER userid

Syntax 7 – Grant connect permissions

GRANT CONNECT TO userid [, …]
 IDENTIFIED BY password [, …]

Syntax 8 – Grant creation permission on a dbspace

GRANT CREATE ON dbspace_name
 TO userid [, …] 

Examples

Usage

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.

GRANT authority clause – Grant one of these authorities to users:
  • BACKUP authority – Grants the authority to back up the database. See SQL Anywhere Server - SQL Reference > SQL statements > SQL statements > GRANT statement.
  • DBA authority – Database Administrator authority gives a user permission to do anything. This authority is usually reserved for the person in the organization who is looking after the database.
  • MULTIPLEX ADMIN authority – Allows users to perform multiplex administration tasks such as creating and deleting multiplex servers. See Using Sybase IQ Multiplex > Multiplex Server Administration > Administration Authorities > MULTIPLEX ADMIN Authority for a complete description.
  • OPERATOR authority – Allows users to checkpoint and backup databases, drop connections, and monitor the system. See System Administration Guide: Volume 1 > Managing User IDs and Permissions for a complete description.
  • PERMS ADMIN authority – Allows users to manage data permissions, groups, authorities and passwords. See System Administration Guide: Volume 1 > Managing User IDs and Permissions for a complete description.
  • PROFILE authority – Grants the user the authority to perform profiling and diagnostic operations. See SQL Anywhere Server - SQL Reference > SQL statements > SQL statements > GRANT statement .
  • READCLIENTFILE authority – Grants the user the ability to read from a file on the client computer, for example, when loading data. See SQL Anywhere Server - SQL Reference > SQL statements > SQL statements > GRANT statement.
  • READFILE authority – Allows the user to execute a SELECT statement against a file using the OPENSTRING clause. See SQL Anywhere Server - SQL Reference > SQL statements > SQL statements > GRANT statement.
  • RESOURCE authority – Allows the user to create database objects such as tables, views, and stored procedures. In syntax 1, ALL is a synonym for RESOURCE, which is compatible with Adaptive Server Enterprise.
  • SPACE ADMIN authority – Allows users to manage dbspaces. See System Administration Guide: Volume 1 > Managing User IDs and Permissions for a complete description.
  • USER ADMIN authority – Allows users to manage users, external logins, and login policies. See System Administration Guide: Volume 1 > Managing User IDs and Permissions for a complete description.
  • VALIDATE authority – 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. This authority also allows the user to use the Validation utility (dbvalid), and the Validate Database wizard in Sybase Central. See SQL Anywhere Server - SQL Reference > SQL statements > SQL statements > GRANT statement .
  • WRITECLIENTFILE authority – Grants the user the ability to write to a file on the client computer, for example, when unloading data. See SQL Anywhere Server - SQL Reference > SQL statements > SQL statements > GRANT statement.
Note: These references point to SQL Anywhere documentation.

GROUP clause – Allows the users to have members. See System Administration Guide: Volume 1 > Managing User IDs and Permissions for a complete description.

MEMBERSHIP IN GROUP clause – 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.

GRANT permission clause – Grant permission on individual tables or views. You can list the table permissions together, or specify ALL to grant all six permissions at once. If WITH GRANT OPTION is specified, then the named user ID is also given permission to GRANT the same permissions to other user IDs.
  • ALL permission – In syntax 3, grants all of the permissions
  • ALTER permission – Users can alter this table with the ALTER TABLE statement. This permission is not allowed for views.
  • DELETE permission – Users can delete rows from this table or view.
  • INSERT permission – Users can insert rows into the named table or view.
  • REFERENCES permission – 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 permission – 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 permission – 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:

GRANT
SELECT, UPDATE ( street )
ON Employees
TO Laurel

EXECUTE ON clause – Grants permission to execute a procedure.

INTEGRATED LOGIN TO clause – 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.

KERBEROS LOGIN TO clause – 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 SQL Anywhere Server - SQL Reference > SQL statements > SQL statements > GRANT statement.

CONNECT TO clause – Creates a new user. GRANT CONNECT can also be used by any user to change their own password.

Note: 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:

GRANT CONNECT TO userid IDENTIFIED BY ""

If you have DBA or PERMS ADMIN authority, you can change the password of any existing user:

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.

Note: Use system procedures, not GRANT and REVOKE, to add and remove user IDs.

To create a user with no password:

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 Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Identifiers. 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 names 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

CREATE ON clause – Grants CREATE permission on the specified dbspace to the specified user(s) and/or group(s).

See also REVOKE Statement.

Side Effects:
  • Automatic commit.
Note: These references point to SQL Anywhere documentation.

Standards

  • 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 1 and 3 are supported in Adaptive Server Enterprise. The security model is different in Adaptive Server Enterprise and Sybase IQ, so other syntaxes differ.

Permissions

  • For Syntax 1 and 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 USER ADMIN authority.

  • For Syntax 7, 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 8, you must have DBA or SPACE ADMIN authority.

Parent topic: SQL Statements
Related reference
CREATE USER Statement
REVOKE Statement
VERIFY_PASSWORD_FUNCTION Option