11.2SQL 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.

11.2.1The 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.

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.

11.2.2Statements for Granting Privileges

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

11.2.2.1GRANT

Used forGranting privileges and assigning roles

Available inDSQL, 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 [, 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 11.6GRANT Statement Parameters
ParameterDescription

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.

11.2.2.1.1The 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.

11.2.2.1.1.1Packaging 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.

11.2.2.1.2The 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.

11.2.2.1.3The 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 this!

11.2.2.1.4The 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.

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.

11.2.2.1.4.1Alternative 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.

11.2.2.1.5Privileges 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 11.7List of Privileges on Tables
PrivilegeDescription

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 or REFERENCES 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 table via a foreign key, optionally restricted to the specified columns. 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.

ALL

Combines SELECT, INSERT, UPDATE, DELETE and REFERENCES privileges in a single package

Examples of GRANT <privilege> on Tables
  1. SELECT and INSERT privileges to the user ALEX:

      |GRANT SELECT, INSERT ON TABLE SALES
      |  TO USER ALEX;
    
  2. 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;
    
  3. 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;
    
  4. The SELECT and REFERENCES privileges on the NAME column to all users and objects:

      |GRANT SELECT, REFERENCES (NAME) ON TABLE COUNTRY
      |TO PUBLIC;
    
  5. The SELECT privilege being granted to the user IVAN by the user ALEX:

      |GRANT SELECT ON TABLE EMPLOYEE
      |  TO USER IVAN
      |  GRANTED BY ALEX;
    
  6. Granting the UPDATE privilege on the FIRST_NAME, LAST_NAME columns:

      |GRANT UPDATE (FIRST_NAME, LAST_NAME) ON TABLE EMPLOYEE
      |  TO USER IVAN;
    
  7. Granting the INSERT privilege to the stored procedure ADD_EMP_PROJ:

      |GRANT INSERT ON EMPLOYEE_PROJECT
      |  TO PROCEDURE ADD_EMP_PROJ;
    
11.2.2.1.6The 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.

ExampleGranting the EXECUTE privilege on a stored procedure to a role:

  |GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ
  |  TO ROLE MANAGER;
11.2.2.1.7Assigning 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.

11.2.2.1.7.1The 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 this!

11.2.2.1.7.2Examples of Role Assignment
  1. Assigning the DIRECTOR and MANAGER roles to the user IVAN:

      |GRANT DIRECTOR, MANAGER TO USER IVAN;
    
  2. 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;
    

See alsoSection 11.2.3.1, “REVOKE

11.2.3Statements for Revoking Privileges

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

11.2.3.1REVOKE

Used forRevoking privileges or role assignments

Available inDSQL, 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 …])]
   |  | 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 11.8REVOKE Statement Parameters
ParameterDescription

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 Section 11.2.2.1, “GRANT for detailed descriptions of the various types of privileges.

Only the user who granted the privilege can revoke it.

11.2.3.1.1The 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.

11.2.3.1.2Revoking 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.

11.2.3.1.3Removing 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.

11.2.3.1.4Revoking 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.

11.2.3.1.5Revoking 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.

11.2.3.1.6Examples using REVOKE
  1. Revoking the privileges for reading and inserting into the SALES

      |REVOKE SELECT, INSERT ON TABLE SALES FROM USER ALEX;
    
  2. 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;
    
  3. 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;
    
  4. 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;
    
  5. 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;
    
  6. 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;
    
  7. 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;
    
  8. Revoking the privilege for executing the procedure ADD_EMP_PROJ from the MANAGER role:

      |REVOKE EXECUTE ON PROCEDURE ADD_EMP_PROJ
      |  FROM ROLE MANAGER;
    
  9. Revoking the DIRECTOR and MANAGER roles from the user IVAN:

      |REVOKE DIRECTOR, MANAGER FROM USER IVAN;
    
  10. Revoke from the user ALEX the authority to assign the MANAGER role to other users:

      |REVOKE ADMIN OPTION FOR MANAGER FROM USER IVAN;
    
  11. 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.

See alsoSection 11.2.2.1, “GRANT