SQL Privileges

Table of Contents

The Object Owner
Statements for Granting Privileges
Statements for Revoking Privileges

The second level of Firebird's security model is SQL privileges. Whilst a successful login—the first level—authorises a user's access to the server and to all databases under that server, it does not imply that he has access to any objects in any databases. When an object is created, only the user that created it (its owner) and administrators have access to it. The user needs privileges on each object he needs to access. As a general rule, privileges must be granted explicitly to a user by the object owner or an administrator of the database.

A privilege comprises a DML access type (SELECT, INSERT, UPDATE, DELETE, EXECUTE and REFERENCES), the name of a database object (table, view, procedure, role) and the name of the user (user, procedure, trigger, role) to which it is granted. Various means are available to grant multiple types of access on an object to multiple users in a single GRANT statement. Privileges may be withdrawn from a user with REVOKE statements.

Privileges are are stored in the database to which they apply and are not applicable to any other database.

The Object Owner

The user who creates a database object becomes its owner. Only the owner of an object and users with administrator privileges in the database, including the database owner, can alter or drop the database object.

	Some Ownership Drawbacks
Any authenticated user can access any database and create any valid database object. Up to and including this release, the issue is not controlled. Because not all database objects are associated with an owner—domains, external functions (UDFs), BLOB filters, generators (sequences) and exceptions—ownerless objects must be regarded as vulnerable on a server that is not adequately protected.

Some Ownership Drawbacks

Any authenticated user can access any database and create any valid database object. Up to and including this release, the issue is not controlled.

Because not all database objects are associated with an owner—domains, external functions (UDFs), BLOB filters, generators (sequences) and exceptions—ownerless objects must be regarded as vulnerable on a server that is not adequately protected.

SYSDBA, the database owner or the object owner can grant privileges to and revoke them from other users, including privileges to grant privileges to other users. The process of granting and revoking SQL privileges is implemented with two statements of the general form:

GRANT <privilege> ON <OBJECT-TYPE> <object-name> 
  TO { <user-name> | ROLE <role-name> }

REVOKE <privilege> ON <OBJECT-TYPE> <object-name> 
  FROM { <user-name> | ROLE <role-name> }

The <OBJECT-TYPE> is not required for every type of privilege. For some types of privilege, extra parameters are available, either as options or as requirements.

Statements for Granting Privileges

Table of Contents

GRANT

A GRANT statement is used for granting privileges—including roles—to users and other database objects.

GRANT

Used for: Granting privileges and assigning roles

Available in: DSQL, ESQL

Syntax:

GRANT {
  <privileges> ON [TABLE] {tablename | viewname} 
  | EXECUTE ON PROCEDURE procname
      }
TO <grantee_list>
  [WITH GRANT OPTION]} | [{GRANTED BY | AS} [USER] grantor];

GRANT <role_granted>
TO <role_grantee_list> [WITH ADMIN OPTION]
[{GRANTED BY | AS} [USER] grantor]

<privileges> ::= ALL [PRIVILEGES] | <privilege_list>

<privilege_list> ::= {<privilege> [, <privilege> [, … ] ] }

<privilege> ::=
  SELECT |
  DELETE |
  INSERT |
  UPDATE [(col [, col [, …] ] ) ] |
  REFERENCES (col [, …])

<grantee_list> ::= {<grantee> [, <grantee> [, …] ]}

<grantee>  ::=
  [USER] username | [ROLE] rolename |  GROUP Unix_group
  | PROCEDURE procname | TRIGGER trigname | VIEW viewname | PUBLIC

<role_granted> ::= rolename [, rolename …]

<role_grantee_list> ::= [USER] <role_grantee> [,[USER] <role_grantee> [, …]]

<role_grantee> ::= {username | PUBLIC }

Table 10.6. GRANT Statement Parameters

Parameter	Description
tablename	The name of the table the privilege applies to
viewname	The name of the view the privilege applies to
procname	The name of the stored procedure the EXECUTE privilege applies to; or the name of the procedure to be granted the privilege[s]
col	The table column the privilege is to apply to
Unix_group	The name of a user group in a POSIX operating system
username	The user name to which the privileges are granted to or to which the role is assigned
rolename	Role name
trigname	Trigger name
grantor	The user granting the privilege[s]

A GRANT statement grants one or more privileges on database objects to users, roles, stored procedures, triggers or views.

A regular, authenticated user has no privileges on any database object until they are explicitly granted, either to that individual user or to all users bundled as the user PUBLIC. When an object is created, only the user who has created it (the owner) and administrators have privileges for it and can grant privileges to other users, roles or objects.

Different sets of privileges apply to different types of metadata objects. The different types of privileges will be described separately later.

The TO Clause

The TO clause is used for listing the users, roles and database objects (procedures, triggers and views) that are to be granted the privileges enumerated in <privileges>. The clause is mandatory.

The optional USER and ROLE keywords in the TO clause allow you to specify exactly who or what is granted the privilege. If a USER or ROLE keyword is not specified, the server checks for a role with this name and, if there is none, the privileges are granted to the user without further checking.

Packaging Privileges in a ROLE Object

A role is a “container” object that can be used to package a collection of privileges. Use of the role is then granted to each user that requires those privileges. A role can also be granted to a list of users.

The role must exist before privileges can be granted to it. See CREATE ROLE in the DDL chapter for the syntax and rules. The role is maintained by granting privileges to it and, when required, revoking privileges from it. If a role is dropped (see DROP ROLE), all users lose the privileges acquired through the role. Any privileges that were granted additionally to an affected user by way of a different grant statement are retained.

A user that is granted a role must supply that role with his login credentials in order to exercise the associated privileges. Any other privileges granted to the user are not affected by logging in with a role.

More than one role can be granted to the same user but logging in with multiple roles simultaneously is not supported.

A role can be granted only to a user.

	Please note:
When a GRANT statement is executed, the security database is not checked for the existence of the grantee user. This is not a bug: SQL permissions are concerned with controlling data access for authenticated users, both native and trusted, and trusted operating system users are not stored in the security database. When granting a privilege to a database object, such as a procedure, trigger or view, you must specify the object type between the keyword TO and the object name. Although the USER and ROLE keywords are optional, it is advisable to use them, in order to avoid ambiguity.

Please note:

When a GRANT statement is executed, the security database is not checked for the existence of the grantee user. This is not a bug: SQL permissions are concerned with controlling data access for authenticated users, both native and trusted, and trusted operating system users are not stored in the security database.
When granting a privilege to a database object, such as a procedure, trigger or view, you must specify the object type between the keyword TO and the object name.
Although the USER and ROLE keywords are optional, it is advisable to use them, in order to avoid ambiguity.

The User PUBLIC

Firebird has a predefined user named PUBLIC, that represents all users. Privileges for operations on a particular object that are granted to the user PUBLIC can be exercised by any user that has been authenticated at login.

	Important
	If privileges are granted to the user PUBLIC, they should be revoked from the user PUBLIC as well.

The WITH GRANT OPTION Clause

The optional WITH GRANT OPTION clause allows the users specified in the user list to grant the privileges specified in the privilege list to other users.

	Caution
	It is possible to assign this option to the user PUBLIC. Do not do it!

The GRANTED BY Clause

By default, when privileges are granted in a database, the current user is recorded as the grantor. The GRANTED BY clause enables the current user to grant those privileges as another user.

If the REVOKE statement is used, it will fail if the current user is not the user that was named in the GRANTED BY clause.

Alternative Syntax Using AS <username>

The non-standard AS clause is supported as a synonym of the GRANTED BY clause to simplify migration from other database systems.

The clauses GRANTED BY and AS can be used only by the database owner and administrators. The object owner cannot use it unless he also has administrator privileges.

Privileges on Tables and Views

In theory, one GRANT statement grants one privilege to one user or object. In practice, the syntax allows multiple privileges to be granted to multiple users in one GRANT statement.

Syntax extract:

...
<privileges> ::= ALL [PRIVILEGES] | <privilege_list>

<privilege_list> ::= {<privilege> [, <privilege> [, … ] ] }

<privilege> ::= {
  SELECT |
  DELETE |
  INSERT |
  UPDATE [(col [,col [, …])] ] ) ] |
  REFERENCES (col [, col [, …] ] )
                      }

Table 10.7. List of Privileges on Tables

Privilege	Description
SELECT	Permits the user or object to SELECT data from the table or view
INSERT	Permits the user or object to INSERT rows into the table or view
UPDATE	Permits the user or object to UPDATE rows in the table or view, optionally restricted to specific columns
`col`	(Optional) name of a column to which the user's UPDATE privilege is restricted
DELETE	Permits the user or object to DELETE rows from the table or view
REFERENCES	Permits the user or object to reference the specified column[s] of the table via a foreign key. If the primary or unique key referenced by the foreign key of the other table is composite then all columns of the key must be specified.
`col`	(Mandatory) name of one column in the referenced foreign key
ALL	Combines SELECT, INSERT, UPDATE, DELETE and REFERENCES privileges in a single package

Examples of GRANT <privilege> on Tables:

SELECT and INSERT privileges to the user ALEX:

GRANT SELECT, INSERT ON TABLE SALES
  TO USER ALEX;

The SELECT privilege to the MANAGER, ENGINEER roles and to the user IVAN:

GRANT SELECT ON TABLE CUSTOMER
  TO ROLE MANAGER, ROLE ENGINEER, USER IVAN;

All privileges to the ADMINISTRATOR role, together with the authority to grant the same privileges to others:
```
GRANT ALL ON TABLE CUSTOMER
  TO ROLE ADMINISTRATOR
  WITH GRANT OPTION;
                
```
The SELECT and REFERENCEs privileges on the NAME column to all users and objects:
```
GRANT SELECT, REFERENCES (NAME) ON TABLE COUNTRY
TO PUBLIC;
                
```
The SELECT privilege being granted to the user IVAN by the user ALEX:
```
GRANT SELECT ON TABLE EMPLOYEE
  TO USER IVAN
  GRANTED BY ALEX;
                
```
Granting the UPDATE privilege on the FIRST_NAME, LAST_NAME columns:
```
GRANT UPDATE (FIRST_NAME, LAST_NAME) ON TABLE EMPLOYEE
  TO USER IVAN;
                
```
Granting the INSERT privilege to the stored procedure ADD_EMP_PROJ:
```
GRANT INSERT ON EMPLOYEE_PROJECT
  TO PROCEDURE ADD_EMP_PROJ;
                
```

The EXECUTE Privilege

The EXECUTE privilege applies to stored procedures. It allows the grantee to execute the stored procedure and, if applicable, to retrieve its output. In the case of selectable stored procedures, it acts somewhat like a SELECT privilege, insofar as this style of stored procedure is executed in response to a SELECT statement.

Example: Granting the EXECUTE privilege on a stored procedure to a role:

GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ
  TO ROLE MANAGER;

Assigning Roles

Assigning a role is similar to granting a privilege. One or more roles can be assigned to one or more users, including the user PUBLIC, using one GRANT statement.

The WITH ADMIN OPTION Clause

The optional WITH ADMIN OPTION clause allows the users specified in the user list to grant the role[s] specified to other users.

	Caution
	It is possible to assign this option to PUBLIC. Do not do it!

Examples of Role Assignment:

Assigning the DIRECTOR and MANAGER roles to the user IVAN:

GRANT DIRECTOR, MANAGER TO USER IVAN;

Assigning the ADMIN role to the user ALEX with the authority to assign this role to other users:
```
GRANT MANAGER TO USER ALEX WITH ADMIN OPTION;
                
```

Statements for Revoking Privileges

Table of Contents

REVOKE

A REVOKE statement is used for revoking privileges—including roles—from users and other database objects.

REVOKE

Used for: Revoking privileges or role assignments

Available in: DSQL, ESQL

Syntax:

REVOKE [GRANT OPTION FOR] {
  <privileges> ON [TABLE] {tablename | viewname} |
  EXECUTE ON PROCEDURE procname }
FROM <grantee_list>
[{GRANTED BY | AS} [USER] grantor];

REVOKE [ADMIN OPTION FOR] <role_granted>
FROM {PUBLIC | <role_grantee_list>}
[{GRANTED BY | AS} [USER] grantor];

REVOKE ALL ON ALL FROM <grantee_list>

<privileges> ::= ALL [PRIVILEGES] | <privilege_list>

<privilege_list> ::= {<privilege> [, <privilege> [, … ] ] }

<privilege> ::=
  SELECT |
  DELETE |
  INSERT |
  UPDATE [(col [, col [, col [,…] ] ] ) ] |
  REFERENCES (col [, col [, …] ] )

<grantee_list> ::= {<grantee> [, <grantee> [, …] ]}

<grantee>  ::=
  [USER] username | [ROLE] rolename |  GROUP Unix_group
  | PROCEDURE procname | TRIGGER trigname | VIEW viewname | PUBLIC

<role_granted> ::= rolename [, rolename …]

<role_grantee_list> ::= [USER] <role_grantee> [,[USER] <role_grantee> [, …]]

<role_grantee> ::= {username | PUBLIC }

Table 10.8. REVOKE Statement Parameters

Parameter	Description
tablename	The name of the table the privilege is to be revoked from
viewname	The name of the view the privilege is to be revoked from
procname	The name of the stored procedure the EXECUTE privilege is to be revoked from; or the name of the procedure that is to have the privilege[s] revoked
trigname	Trigger name
col	The table column the privilege is to be revoked from
username	The user name from which the privileges are to be revoked from or the role is to be removed from
rolename	Role name
Unix_group	The name of a user group in a POSIX operating system
grantor	The grantor user on whose behalf the the privilege[s] are being revoked

The REVOKE statement is used for revoking privileges from users, roles, stored procedures, triggers and views that were granted using the GRANT statement. See GRANT for detailed descriptions of the various types of privileges.

Only the user who granted the privilege can revoke it.

The FROM Clause

The FROM clause is used to specify the list of users, roles and database objects (procedures, triggers and views) that will have the enumerated privileges revoked. The optional USER and ROLE keywords in the FROM clause allow you to specify exactly which type is to have the privilege revoked. If a USER or ROLE keyword is not specified, the server checks for a role with this name and, if there is none, the privileges are revoked from the user without further checking.

	Tips
	Although the USER and ROLE keywords are optional, it is advisable to use them in order to avoid ambiguity. The GRANT statement does not check for the existence of the user from which the privileges are being revoked. When revoking a privilege from a database object, you must specify its object type

	Revoking Privileges from user PUBLIC
	Privileges that were granted to the special user named PUBLIC must be revoked from the user PUBLIC. User PUBLIC provides a way to grant privileges to all users at once but it is not “a group of users”.

Revoking the GRANT OPTION

The optional GRANT OPTION FOR clause revokes the user's privilege to grant privileges on the table, view, trigger or stored procedure to other users or to roles. It does not revoke the privilege with which the grant option is associated.

Removing the Privilege to One or More Roles

One usage of the REVOKE statement is to remove roles that were assigned to a user, or a group of users, by a GRANT statement. In the case of multiple roles and/or multiple grantees, the REVOKE verb is followed by the list of roles that will be removed from the list of users specified after the FROM clause.

The optional ADMIN OPTION FOR clause provides the means to revoke the grantee's “administrator” privilege, the ability to assign the same role to other users, without revoking the grantee's privilege to the role.

Multiple roles and grantees can be processed in a single statement.

Revoking Privileges That Were GRANTED BY

A privilege that has been granted using the GRANTED BY clause is internally attributed explicitly to the grantor designated by that original GRANT statement. To revoke a privilege that was obtained by this method, the current user must be logged in either with full administrative privileges or as the user designated as <grantor> by that GRANTED BY clause.

	Note
	The same rule applies if the syntax used in the original GRANT statement used the synonymous AS form to introduce the clause, instead of the standard GRANTED BY form.

Revoking ALL ON ALL

If the current user is logged in with full administrator privileges in the database, the statement

REVOKE ALL ON ALL FROM <grantee_list>

can be used to revoke all privileges (including role memberships) on all objects from one or more users and/or roles. All privileges for the user will be removed, regardless of who granted them. It is a quick way to “clear” privileges when access to the database must be blocked for a particular user or role.

If the current user is not logged in as an administrator, the only privileges revoked will be those that were granted originally by that user.

The REVOKE ALL ON ALL statement cannot be used to revoke privileges that have been granted TO stored procedures, triggers or views.

	Note
	The GRANTED BY clause is not supported.

Examples using REVOKE

Revoking the privileges for reading and inserting into the SALES

REVOKE SELECT, INSERT ON TABLE SALES FROM USER ALEX;

Revoking the privilege for reading the CUSTOMER table from the MANAGER and ENGINEER roles and from the user IVAN:
```
REVOKE SELECT ON TABLE CUSTOMER
FROM ROLE MANAGER, ROLE ENGINEER, USER IVAN;
            
```
Revoking from the ADMINISTRATOR role the authority to grant any privileges on the CUSTOMER table to other users or roles:
```
REVOKE GRANT OPTION FOR ALL ON TABLE CUSTOMER
FROM ROLE ADMINISTRATOR;
            
```
Revoking the privilege for reading the COUNTRY table and the authority to reference the NAME column of the COUNTRY table from any user, via the special user PUBLIC:
```
REVOKE SELECT, REFERENCES (NAME) ON TABLE COUNTRY
  FROM PUBLIC;
            
```
Revoking the privilege for reading the EMPLOYEE table from the user IVAN, that was granted by the user ALEX:
```
REVOKE SELECT ON TABLE EMPLOYEE
  FROM USER IVAN GRANTED BY ALEX;
            
```
Revoking the privilege for updating the FIRST_NAME and LAST_NAME columns of the EMPLOYEE table from the user IVAN:
```
REVOKE UPDATE (FIRST_NAME, LAST_NAME) ON TABLE EMPLOYEE
  FROM USER IVAN;
            
```
Revoking the privilege for inserting records into the EMPLOYEE_PROJECT table from the ADD_EMP_PROJ procedure:
```
REVOKE INSERT ON EMPLOYEE_PROJECT
  FROM PROCEDURE ADD_EMP_PROJ;
            
```
Revoking the privilege for executing the procedure ADD_EMP_PROJ from the MANAGER role:
```
REVOKE EXECUTE ON PROCEDURE ADD_EMP_PROJ
  FROM ROLE MANAGER;
            
```

Revoking the DIRECTOR and MANAGER roles from the user IVAN:

REVOKE DIRECTOR, MANAGER FROM USER IVAN;

Revoke from the user ALEX the authority to assign the MANAGER role to other users:
```
REVOKE ADMIN OPTION FOR MANAGER FROM USER IVAN;
            
```
Revoking all privileges (including roles) on all objects from the user IVAN:
```
REVOKE ALL ON ALL FROM IVAN;
            
```
After this statement is executed, the user IVAN will have no privileges whatsoever.