GRANT

Go Up to Statement and Function Reference (Language Reference Guide)

Assigns privileges to users for specified database objects. Available in gpre, DSQL, and isql.

GRANT <privileges> ON [TABLE] {<tablename> | <viewname}>
TO {object|userlist [WITH GRANT OPTION]|GROUP <UNIX_group}>
| EXECUTE ON PROCEDURE procname TO {object | userlist}
| <role_granted> TO {PUBLIC | <role_grantee_list}>[WITH ADMIN OPTION];

privileges = ALL [PRIVILEGES] | privilege_list
privilege_list = {
  SELECT
| DELETE
| INSERT
| ENCRYPT ON ENCRYPTION
| DECRYPT
| UPDATE [(col [, col …])]
| REFERENCES [(col [, col …])]
}[, privilege_list …]
object = {
  PROCEDURE procname
| TRIGGER trigname
| VIEW viewname
| PUBLIC
}[, object …]
userlist = {
  [USER] username
| rolename

 | UNIX_user
}[,userlist …]
role_granted = rolename [, rolename …]
role_grantee_list = [USER] username [, [USER] username …]

Important:
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Argument	Description
<privilege_list>	Name of privilege to be granted; valid options are `SELECT`, `DELETE`, `INSERT`, `UPDATE`, `ENCRYPT ON ENCRYPTION, DECRYPT,` and `REFERENCES`.
<col>	Column to which the granted privileges apply
<tablename>	Name of an existing table for which granted privileges apply
<viewname>	Name of an existing view for which granted privileges apply
`GROUP` <unix_group>	On a `UNIX` system, the name of a group defined in `/etc/group`
<object>	Name of an existing procedure, trigger, or view; `PUBLIC` is also a permitted value.
<userlist>	A user in the InterBase security database (`admin.ib` by default) or a rolename created with `CREATE ROLE`
`WITH GRANT OPTION`	Passes `GRANT` authority for privileges listed in the `GRANT` statement to userlist.
<rolename>	An existing role created with the `CREATE ROLE` statement
<role_grantee_list>	A list of users to whom <rolename> is granted; users must be in the InterBase security database.
`WITH ADMIN OPTION`	Passes grant authority for roles listed to <role_grantee_list>.

Description: GRANT assigns privileges and roles for database objects to users, roles, or other database objects. When an object is first created, only its creator has privileges to it and only its creator can GRANT privileges for it to other users or objects.

The following table summarizes available privileges:

Privilege	Enables users to …
`ALL`	Perform `SELECT`, `DELETE`, `INSERT`, `UPDATE`, and `REFERENCES`
`SELECT`	Retrieve rows from a table or view
`DELETE`	Remove rows from a table or view
DECRYPT	After encrypting a column, the database owner or the individual table owner can grant decrypt permission to users who need to access the values in an encrypted column.
ENCRYPT ON ENCRYPTION	Enables the database owner or individual table owner to use a specific encryption key to encrypt a database or column. Only the SYSDSO (Data Security Owner) can grant encrypt permission.
`INSERT`	Store new rows in a table or view
`UPDATE`	Change the current value in one or more columns in a table or view; can be restricted to a specified subset of columns.
`EXECUTE`	Execute a stored procedure
`REFERENCES`	Reference the specified columns with a foreign key; at a minimum, this must be granted to all the columns of the primary key if it is granted at all.

Note:
ALL does not include REFERENCES in code written for InterBase 4.0 or earlier.

To access a table or view, a user or object needs the appropriate SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges for that table or view. SELECT, INSERT, UPDATE, DELETE, and REFERENCES privileges can be assigned as a unit with ALL.
A user or object must have EXECUTE privilege to call a stored procedure in an application.
For more information about the GRANT ENCRYPT ON ENCRYPTION and GRANT DECRYPT permissions, see “Encrypting Your Data” in the Data Definition Guide.
To grant privileges to a group of users, create a role using CREATE ROLE. Then use GRANT <privilege> TO <rolename> to assign the desired privileges to that role and use GRANT <rolename> TO <user> to assign that role to users. Users can be added or removed from a role on a case-by-case basis using GRANT and REVOKE. A user must specify the role at connection time to actually have those privileges. See “ANSI SQL 3 roles” in the Operations Guide for more information about invoking a role when connecting to a database.
On UNIX systems, privileges can be granted to groups listed in /etc/groups and to any UNIX user listed in /etc/passwd on both the client and server, as well as to individual users and to roles.
To allow another user to reference a column from a foreign key, grant REFERENCES privileges on the primary key table or on the primary key columns of the table to the owner of the foreign key table. You must also grant REFERENCES or SELECT privileges on the primary key table to any user who needs to write to the foreign key table.

Tip:
Make it easy, if read security is not an issue, GRANT REFERENCES on the primary key table to PUBLIC.

If you grant the REFERENCES privilege, it must, at a minimum, be granted to all columns of the primary key. When REFERENCES is granted to the entire table, columns that are not part of the primary key are not affected in any way.
When a user defines a foreign key constraint on a table owned by someone else, InterBase checks that the user has REFERENCES privileges on the referenced table.
The privilege is used at run time to verify that a value entered in a foreign key field is contained in the primary key table.
You can grant REFERENCES privileges to roles.
To give users permission to grant privileges to other users, provide a userlist that includes the WITH GRANT OPTION. Users can grant to others only the privileges that they themselves possess.
To grant privileges to all users, specify PUBLIC in place of a list of user names. Specifying PUBLIC grants privileges only to users, not to database objects.

Privileges can be removed only by the user who assigned them, using REVOKE. If ALL privileges are assigned, then ALL privileges must be revoked. If privileges are granted to PUBLIC, they can be removed only for PUBLIC.

Examples: The following isql statement grants SELECT and DELETE privileges to a user. The WITH GRANT OPTION gives the user GRANT authority.

GRANT SELECT, DELETE ON COUNTRY TO CHLOE WITH GRANT OPTION;

The next embedded SQL statement, from an embedded program, grants SELECT and UPDATE privileges to a procedure for a table:

EXEC SQL
GRANT SELECT, UPDATE ON JOB TO PROCEDURE GET_EMP_PROJ;

This embedded SQL statement grants EXECUTE privileges for a procedure to another procedure and to a user:

EXEC SQL
GRANT EXECUTE ON PROCEDURE GET_EMP_PROJ
TO PROCEDURE ADD_EMP_PROJ, LUIS;

The following example creates a role called “administrator”, grants UPDATE privileges on table1 to that role, and then grants the role to user1, user2, and user3. These users then have UPDATE and REFERENCES privileges on table1.

CREATE ROLE administrator;
GRANT UPDATE ON table1 TO administrator;
GRANT administrator TO user1, user2, user3;

Advance To:

GRANT SUBSCRIBE

GRANT

See Also

Advance To: