Advantage SQL Engine
Grant permissions to a user or user group
Syntax
GRANT <permission [, ...n]> [ON <object>] TO <grantee> [WITH GRANT]
permission ::= SELECT | SELECT( columnname ) | INSERT | INSERT( columnname ) | UPDATE | UPDATE( columnname ) | ACCESS | ALL | ALTER [DATABASE] | DELETE | DROP | EXECUTE | INHERIT [DATABASE] | <create permission>
create permission ::= [INHERIT] CREATE <create object>
create object ::= TABLE | USER [GROUP] | VIEW | PROCEDURE | LINK | PUBLICATION | SUBSCRIPTION | FUNCTION | PACKAGE
object ::= viewname | tablename | procedurename | linkname | publicationname | subscriptionname | packagename | functionname
grantee ::= username | groupname
Remarks
GRANT is used to grant permissions to a user or user group on a database object. The newly granted permission will be in addition to any existing permissions the user or the user group has on the specified database object. Permissions granted on views are not effected by the user’s permissions on the base tables used to construct the view. This behavior allows the use of views to control access to specific columns in the base tables. The following are valid permissions and a description of their effects on a user’s access to an object.
Object permissions such as SELECT, ACCESS, and INHERIT require the ON OBJECT clause to specify which object the permissions apply to. Database-wide permissions such as CREATE and ALTER DATABASE do not apply to any existing database object and thus must not include the ON OBJECT clause. Furthermore, object and database wide permissions cannot be granted within the same SQL statement. For example
GRANT CREATE TABLE, SELECT ON customers TO sales_group
is not a valid statement. These permissions must be broken up into two statements such as:
GRANT CREATE TABLE TO sales_group
GRANT SELECT ON customers TO sales_group
The INHERIT modifier for CREATE permissions will only grant the INHERIT permission for the specified CREATE permission. It does not affect the grantee’s CREATE permission itself.
In order to grant users or user groups’ permissions, the current user must have WITH GRANT permissions for the specified permissions to be granted. See Advantage Data Dictionary User Permissions for more information.
By specifying the WITH GRANT clause in the statement, the grantee will be able to grant the specified permissions to other users.
Note The ALL permission equates to all the permissions for which the current user has WITH GRANT permissions.
The following table describes the available permissions and valid usage in the statement.
Permission |
Applicable to Object Type |
Description |
SELECT |
Table, Table( column, … ) or View |
This permission allows the grantee to read data from the table or view. If TableName( column, … ) is specified, the read access is granted to the specified columns in the table. |
UPDATE |
Table, Table( column, … ) or View |
This permission allows the grantee to modify existing data in the table or view. If TableName( column, … ) is specified, the user is allowed to modify the specified columns in the table. |
INSERT |
Table, Table( column, … ) or View |
This permission allows the grantee to insert new records into the table or view. If the TableName( column, … ) is specified, the user is allowed to provide the initial value to the specified columns in the table. |
DELETE |
Table or View |
This permission allows the grantee to delete existing records from the table or view. |
EXECUTE |
Stored Procedure, User Defined Function or Package |
This permission allows the grantee to execute the specified stored procedure, function, or functions in the specified package. |
ACCESS |
Data Dictionary Link |
This permission allows the grantee to access the specified link. The permissions to the tables in the link are still verified by the target data dictionary against the credential of the linked user. |
ALTER |
Table, view, stored procedure, function, package, data dictionary link, publication, subscription, user or user group |
This permission allows the grantee to change the meta data associated with the specified object. It is one the administrative permissions. |
DROP |
Table, view, stored procedure, function, package, data dictionary link, publication, subscription, user or user group |
This permission allows the grantee to removed the specified object from the database. It is one of the administrative permissions. |
INHERIT |
Table, view, stored procedure, function, package, data dictionary link, publication, subscription, user or user group |
This permission can only be granted to a user. It is granted to the user by default. The permission allows the user to inherit permission on the specified object from the user groups that the user is a member of. See Effective Permissions. |
CREATE TABLE, CREATE VIEW, CREATE USER, CREATE USER GROUP, CREATE PROCEDURE, CREATE PUBLICATION, CREATE SUBSCRIPTION, CREATE FUNCTION, CREATE PACKAGE |
|
Each permission allows the grantee to create an object of the specified type in the database. These permissions should be considered administrative permissions. |
ALTER DATABASE |
|
This permission allows the grantee to modify the meta data of the database object, i.e., global settings of the database. It does not give permission to modify meta data of the individual objects in the database. |
Example(s)
GRANT SELECT ON customers TO sales_group
GRANT INSERT( accounts ) ON customers TO user1
GRANT ALL ON customers TO managers
GRANT CREATE TABLE TO super_users WITH GRANT
GRANT ALTER DATABASE to super_users
GRANT ALTER, UPDATE ON customers TO sales_group
GRANT SELECT( cust_name ), SELECT( address ), UPDATE( balance ) ON customers TO production
GRANT EXECUTE ON MyFunction TO user1
GRANT EXECUTE, ALTER ON MyConvPack TO super_users
See Also