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 table and view 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 permissions 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 This authority grants the user the ability to back up the database.
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.
PROFILE authority This authority grants the user the ability to perform profiling and diagnostic operations.
READCLIENTFILE authority This authority grants the user the ability to read from a file on the client computer, for example when loading data.
READFILE authority This authority grants the user the ability to execute a SELECT statement on a file using the OPENSTRING clause.
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 Adaptive Server Enterprise.
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.
WRITECLIENTFILE authority This authority grants the user the ability to write to a file on the client computer, for example when unloading data.
GROUP clause This permission allows the user(s) to have members.
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.
GRANT permission clause The GRANT permission clause allows you to grant permission on individual tables or views. The table and view permissions can be specified individually, or you can use ALL to grant all permissions at once. The following is a list of permissions that can be granted:
ALL permission This permission grants ALTER, DELETE, INSERT, REFERENCES, SELECT, and UPDATE permissions on tables. This permission grants DELETE, INSERT, and UPDATE permissions on views.
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 on the 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.
WITH GRANT OPTION clause 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.
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.
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 cannot:
Passwords are case-sensitive and they 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.
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.
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 execute 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.
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.
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.
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).
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 © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |