![GOTO statement [T-SQL]](gif/backward.gif)
![GRANT CONSOLIDATE statement [SQL Remote]](gif/forward.gif)
ASA SQL Reference
SQL Statements
Use this statement to create new user IDs, to grant or deny permissions to specific users, and to create or change passwords.
GRANT CONNECT TO userid, ...
[ AT starting-id ]
IDENTIFIED BY password, ...
GRANT {
DBA,
GROUP,
MEMBERSHIP IN GROUP userid, ...,
[ RESOURCE | ALL ]
}
TO userid, ...
GRANT {
ALL [ PRIVILEGES ],
ALTER,
DELETE,
INSERT,
REFERENCES [ ( column-name, ... ) ],
SELECT [ ( column-name, ... ) ],
UPDATE [ ( column-name, ... ) ],
}
ON [ owner.]table-name
TO userid, ...
[ WITH GRANT OPTION ]
[ FROM userid ]
GRANT EXECUTE ON [ owner.]procedure-name TO userid, ...
GRANT INTEGRATED LOGIN TO user_profile_name, ... AS USER userid
CONNECT TO 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 if you are creating a group and do not want anyone to connect to the database using the group user ID. The password must be a valid identifier, as described in Identifiers.
AT starting-id 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 clause is implemented primarily for use by the Unload utility.
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.
For more information, see Managing groups.
MEMBERSHIP IN GROUP 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.
For more information, see Managing groups.
Syntax 3 of the GRANT statement is used to grant permission on individual tables or views. The table permissions can be specified individually, or you can use ALL to grant all six permissions at once.
RESOURCE Allows the user to create tables and views. In syntax 2, ALL is a synonym for RESOURCE that is compatible with Sybase Adaptive Server Enterprise.
ALL In Syntax 3, this grants all of the permissions outlined below.
ALTER The users will be allowed to alter the named table with the ALTER TABLE statement. This permission is not allowed for views.
DELETE The users will be allowed to delete rows from the named 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 table, and foreign keys which reference the named tables. If column names are specified, the users will be allowed to reference only those columns. REFERENCES permissions on columns cannot be granted for views, only for tables.
INDEX is a synonym for REFERENCES.
SELECT [(column-name, ...)] The users will be allowed to look at information in this view or table. If column names are specified, 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, the users will be allowed to update only those columns. UPDATE permissions on columns cannot be granted for views, only for tables.
FROM 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.
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.
If WITH GRANT OPTION is specified, then the named user ID is also given permission to GRANT the same permissions to other user IDs.
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 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 integrated logins.
Syntax 1 or 2 One of the following conditions must be met.
You are changing your own password using GRANT CONNECT.
You have DBA authority.
If you are changing another user's password (with DBA authority), the other user must not be connected to the database.
Syntax 3 If the FROM clause is specified you must have DBA authority. Otherwise, at least one of the following conditions must be met:
You own the table
You have been granted permissions on the table with GRANT OPTION
You have DBA authority
Syntax 4 One of the following conditions must be met:
You own the procedure
You have DBA authority
Syntax 5 The following condition must be met:
You have DBA authority
Automatic commit.
SQL/92 Syntax 3 is an entry-level feature. Syntax 4 is a Persistent Stored Module feature. Other syntaxes are vendor extensions.
SQL/99 Syntax 3 is a core feature. Syntax 4 is a Persistent Stored Module feature. Other syntaxes are vendor extensions.
Sybase Syntaxes 2 and 3 are supported in Adaptive Server Enterprise. The security model is different in Adaptive Server Enterprise and Adaptive Server Anywhere, so other syntaxes differ.
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
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