Grants membership in groups, creates new user IDs, grants or changes permissions for specified users, and creates or changes passwords.
Syntax 1 - Grant authoritiesGRANT authority, ... TO userid, ...
authority : BACKUP | DBA | PROFILE | READCLIENTFILE | READFILE | [ RESOURCE | ALL ] | VALIDATE | WRITECLIENTFILE
Syntax 2 - Grant group status or membership in a groupGRANT { GROUP | MEMBERSHIP IN GROUP userid, ... } TO userid, ...
Syntax 3 - Grant database object permissionsGRANT 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 Syntax 5 - Grant integrated login Syntax 6 - Grant Kerberos login Syntax 7 - Grant connect permissionsGRANT CONNECT TO userid, ... [ AT starting-id ] [ IDENTIFIED BY password, ... ]
Syntax 8 - Grant creation permission on a dbspace Syntax 9 - Grant permission on a sequence ParametersAT 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 clause This authority grants the user the ability to back up the database. See BACKUP authority.
DBA authority clause 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 clause This authority grants the user the ability to perform profiling and diagnostic operations. See PROFILE authority.
READCLIENTFILE authority clause 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 clause 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 clause This authority grants the user the ability to create tables and views. ALL is a synonym for RESOURCE that is compatible with Adaptive Server Enterprise. See RESOURCE authority.
VALIDATE authority clause 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 clause 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 clause This permission grants ALTER, DELETE, INSERT, REFERENCES, SELECT, and UPDATE permissions. ALL is a synonym for RESOURCE.
ALTER permission clause This permission allows the user to alter the named table with the ALTER TABLE statement. This permission is not allowed for views.
DELETE permission clause This permission allows the user to delete rows from the named table or view.
INSERT permission clause This permission allows the user to insert rows into the named table or view.
REFERENCES permission clause 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 clause 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 clause 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.
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:
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.
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.
GRANT USAGE ON SEQUENCE clause Allows users to evaluate the current or next value in a sequence. You must have DBA authority or be the creator of the sequence to run this statement. If the sequence is part of a DEFAULT clause on a table, any user that inserts a row into the table must have permission on the sequence. See Using a sequence to generate unique values.
RemarksThe 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.
You can grant permissions on disabled objects. Permissions on disabled objects are stored in the database and become effective when the object is enabled.
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 Windows 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.
PermissionsSyntax 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.
Side effectsAutomatic commit.
See also Standards and compatibilitySQL/2008 Syntax 3 is a core feature of the SQL/2008 standard. With Syntax 3, the FROM clause is a vendor extension, as is the ALTER privilege. In the SQL/2008 standard, rather than the optional ALL PRIVILEGES syntax, the PRIVILEGES keyword is mandatory.
Syntax 4 is also a core feature of SQL/2008, used for granting EXECUTE permissions on stored procedures.
Syntax 9 is part of optional SQL/2008 language feature T176.
All other syntaxes are vendor extensions.
ExampleCreate 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.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |