User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To give permissions to specific users and to create new user IDs.

Syntax

     Syntax 1:

     GRANT CONNECT TO userid,... IDENTIFIED BY password,...

     Syntax 2:

     GRANT
          DBA
          | GROUP
          | MEMBERSHIP IN GROUP userid,...
          | RESOURCE

     ... 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

Usage

Anywhere.

Permissions

For Syntax 1 or 2 one of the three following conditions must be met. You must be:

• Be changing your own password using GRANT CONNECT
• Be adding members to your own user ID, or
• Have DBA authority.

If changing another user's password, the other user must not be connected to the database.

For Syntax 3, must have one of the following:

• Created the table
• Been granted permissions on the table with GRANT OPTION
• DBA authority

For Syntax 4, must have one of the following:

• Created the procedure
• DBA authority

For Syntax 5, must have:

• DBA authority

Side effects

Automatic commit.

See also

• REVOKE statement

Description

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 ""

To create a user with no password, type:

     GRANT CONNECT TO userid

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 "Watcom-SQL language elements".

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 "Managing groups" 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. See "Managing groups" for a complete description.

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.

ALL All of the permissions outlined below.

With Release 5.0.02, there is a change in permission requirements for statements that require reading of a column value. A statement such as

     UPDATE table_name
     SET col_name = col_name + 1

or

     UPDATE table_name
     SET col_name = 1
     WHERE col_name = 2

both require that information in the column be read, and so require both UPDATE and SELECT permissions on the column col_name. Prior to release 5.0.02, only UPDATE permissions were required to carry out operations of this sort.

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, then the users will be allowed to update only those columns. UPDATE permissions on columns cannot be granted for views, only for tables.

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 NT 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.

  For more information on integrated logins see "Using an integrated login".

Examples

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, UPDATE ( street )
     ON employee
     TO Laurel

Allow the user Hardy to execute the Calculate_Report procedure.

     GRANT
     EXECUTE ON Calculate_Report
     TO Hardy