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

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (E-O)

 

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.

You can grant permissions on disabled objects. Permissions to disabled objects are stored in the database and become effective when the object is enabled.

Syntax 1 - Grant authorities
GRANT authority, ... 
 TO userid, ...
authority :  
BACKUP 
| DBA
| PROFILE 
| READCLIENTFILE 
| READFILE
| [ RESOURCE | ALL ]
| 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.]object-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 | user-defined-function }
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, ...
[ AT starting-id ]
[ IDENTIFIED BY password, ... ]
Syntax 8 - Grant creation permission on a dbspace
GRANT CREATE ON dbspace-name 
TO userid, ...
Parameters
  • AT starting-id clause   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 AT starting-id clause is used by the Unload utility.

  • GRANT authority clause   Use this clause to grant one of the authorities listed below:

    • BACKUP authority   This authority grants the user the ability to back up the database. See BACKUP authority.

    • DBA authority   This authority grants the user the ability to perform all tasks. This is usually reserved for the person in the organization who is looking after the database. See DBA authority.

    • PROFILE authority   This authority grants the user the ability to perform profiling and diagnostic operations. See PROFILE authority.

    • READCLIENTFILE authority   This authority grants the user the ability to read from a file on the client computer, for example when loading data. See READCLIENTFILE authority.

    • READFILE authority   This authority grants the user the ability to execute a SELECT statement against a file using the OPENSTRING clause. See READFILE authority.

    • RESOURCE or ALL authority   This authority grants the user the ability to create tables and views. ALL is a synonym for RESOURCE that is compatible with Sybase Adaptive Server Enterprise. See RESOURCE authority.

    • VALIDATE authority   This authority grants the user the ability 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 VALIDATE authority.

    • WRITECLIENTFILE authority   This authority grants the user the ability to write to a file on the client computer, for example when unloading data. See WRITECLIENTFILE authority.

  • GROUP clause   This permission allows the user(s) to have members. See Managing groups.

  • MEMBERSHIP IN GROUP clause   This permission grants a user membership in a group. The user inherits the inheritable permissions and authorities set at the group level. See Managing groups.

  • GRANT permission clause   The GRANT permission clause allows you to grant permission on individual tables or views. The table permissions can be specified individually, or you can use ALL to grant all permissions at once. The following is a list of grantable permissions:

    • ALL permission   This permission grants ALTER, DELETE, INSERT, REFERENCES, SELECT, and UPDATE permissions. ALL is a synonym for RESOURCE.

    • ALTER permission   This permission allows the user to alter the named table with the ALTER TABLE statement. This permission is not allowed for views.

    • DELETE permission   This permission allows the user to delete rows from the named table or view.

    • INSERT permission   This permission allows the user to insert rows into the named table or view.

    • REFERENCES permission   This permission allows the user to create indexes on the named table, and foreign keys that reference the named tables. If column names are specified, the user can reference only those columns. REFERENCES permissions on columns cannot be granted for views, only for tables. INDEX is a synonym for REFERENCES.

    • SELECT permission   This permission allows the user to view information in the view or table. If column names are specified, the users are allowed to view only those columns. SELECT permissions on columns cannot be granted for views, only for tables.

    • UPDATE permission   This permission allows the user to update rows in the view or table. If column names are specified, the user can update only those columns.

  • FROM clause   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.

  • CONNECT TO clause  

    Note

    It is recommended that you use the CREATE USER statement to create users. See CREATE USER statement.

    Creates a new user. GRANT CONNECT can also be used by any user to change their own password. To create a user with an 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.

    User IDs and passwords cannot:

    • begin with white space, single quotes, or double quotes
    • end with white space
    • contain semicolons
    A password can be either a valid identifier, or a string (maximum 255 bytes) placed in single quotes. For information about specifying a valid password, see Setting a password.

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

  • CREATE ON clause   Allows users to create database objects in the specified dbspace. The CREATE permission can be inherited through group membership. Before a user can create objects, they must also have RESOURCE authority. See RESOURCE authority.

Remarks

The GRANT statement is used to grant database permissions and authorities 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 Kerberos authentication.

Permissions

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 and 6   You must have DBA authority.

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

Side effects

Automatic commit.

See also
Standards and compatibility
  • SQL/2003   Syntax 3 is a core feature. Syntax 4 is a Persistent Stored Module feature. Other syntaxes are vendor extensions.

Example

Create a new database user.

GRANT CONNECT TO SQLTester
IDENTIFIED BY welcome

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;