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.
GRANT authority, ... TO userid, ...
authority : BACKUP | DBA | PROFILE | READCLIENTFILE | READFILE | [ RESOURCE | ALL ] | VALIDATE | WRITECLIENTFILE
GRANT { GROUP | MEMBERSHIP IN GROUP userid, ... } TO userid, ...
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, ... ) ]
GRANT EXECUTE ON [ owner.]{ procedure-name | user-defined-function } TO userid, ...
GRANT INTEGRATED LOGIN TO user-profile-name, ... AS USER userid
GRANT KERBEROS LOGIN TO client-Kerberos-principal, ... AS USER userid
GRANT CONNECT TO userid, ... [ AT starting-id ] [ IDENTIFIED BY password, ... ]
GRANT CREATE ON dbspace-name TO userid, ...
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.
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; |
User IDs and passwords cannot:
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.
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.
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.
Automatic commit.
SQL/2003 Syntax 3 is a core feature. Syntax 4 is a Persistent Stored Module feature. Other syntaxes are vendor extensions.
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; |
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |