FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 ROLEStatements for Revoking Privileges 

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.

Privileges on Tables and Views

For tables and views, unlike other metadata objects, it is possible to grant several privileges at once.

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

DELETE

Permits the user or object to DELETE rows from the table or view

UPDATE

Permits the user or object to UPDATE rows in the table or view, optionally restricted to specific columns

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 [PRIVILEGES]

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

docnext count = 9

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;

The EXECUTE Privilege

The EXECUTE privilege applies to stored procedures, stored functions (including UDFs), and packages.It allows the grantee to execute the specified object, 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.

Note

For packages, the EXECUTE privilege can only be granted for the package as a whole, not for individual subroutines.

Examples of Granting the EXECUTE Privilege

  1. Granting the EXECUTE privilege on a stored procedure to a role:

    GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ
  TO ROLE MANAGER;

  2. Granting the EXECUTE privilege on a stored function to a role:

    GRANT EXECUTE ON FUNCTION GET_BEGIN_DATE
  TO ROLE MANAGER;

  3. Granting the EXECUTE privilege on a package to user PUBLIC:

    GRANT EXECUTE ON PACKAGE APP_VAR
  TO USER PUBLIC;

  4. Granting the EXECUTE privilege on a function to a package:

    GRANT EXECUTE ON FUNCTION GET_BEGIN_DATE
  TO PACKAGE APP_VAR;

The USAGE Privilege

To be able to use metadata objects other than tables, views, stored procedures or functions, triggers and packages, it is necessary to grant the user (or database object like trigger, procedure or function) the USAGE privilege on these objects.

By default, Firebird executes PSQL modules with the privileges of the caller, so it is necessary that either the user or otherwise the routine itself has been granted the USAGE privilege.This can be changed with the SQL SECURITY clause of the DDL statements of those objects.

Note

The USAGE privilege is currently only available for exceptions and sequences (in gen_id(gen_name, n) or next value for gen_name).Support for the USAGE privilege for other metadata objects may be added in future releases.
Note

For sequences (generators), the USAGE privilege only grants the right to increment the sequence using the GEN_ID function or NEXT VALUE FOR.The SET GENERATOR statement is a synonym for ALTER SEQUENCE …​ RESTART WITH …​, and is considered a DDL statement.By default, only the owner of the sequence and administrators have the rights to such operations.The right to set the initial value of any sequence can be granted with GRANT ALTER ANY SEQUENCE, which is not recommend for general users.

Examples of Granting the USAGE Privilege

  1. Granting the USAGE privilege on a sequence to a role:

    GRANT USAGE ON SEQUENCE GEN_AGE
  TO ROLE MANAGER;

  2. Granting the USAGE privilege on a sequence to a trigger:

    GRANT USAGE ON SEQUENCE GEN_AGE
  TO TRIGGER TR_AGE_BI;

  3. Granting the USAGE privilege on an exception to a package:

    GRANT USAGE ON EXCEPTION
  TO PACKAGE PKG_BILL;

DDL Privileges

By default, only administrators can create new metadata objects.Altering or dropping these objects is restricted to the owner of the object (its creator) and administrators.DDL privileges can be used to grant privileges for these operations to other users.

Available DDL Privileges
CREATE

Allows creation of an object of the specified type

ALTER ANY

Allows modification of any object of the specified type

DROP ANY

Allows deletion of any object of the specified type

ALL [PRIVILEGES]

Combines the CREATE, ALTER ANY and DROP ANY privileges for the specified type

Note

There are no separate DDL privileges for triggers and indexes.The necessary privileges are inherited from the table or view.Creating, altering or dropping a trigger or index requires the ALTER ANY TABLE or ALTER ANY VIEW privilege.

Examples of Granting DDL Privileges

  1. Allow user JOE to create tables

    GRANT CREATE TABLE
  TO USER Joe;

  2. Allow user JOE to alter any procedure

    GRANT ALTER ANY PROCEDURE
  TO USER Joe;

Database DDL Privileges

The syntax for granting privileges to create, alter or drop a database deviates from the normal syntax of granting DDL privileges for other object types.

Available Database DDL Privileges
CREATE

Allows creation of a database

ALTER

Allows modification of the current database

DROP

Allows deletion of the current database

ALL [PRIVILEGES]

Combines the ALTER and DROP privileges.ALL does not include the CREATE privilege.

The ALTER DATABASE and DROP DATABASE privileges apply only to the current database, whereas DDL privileges ALTER ANY and DROP ANY on other object types apply to all objects of the specified type in the current database.The privilege to alter or drop the current database can only be granted by administrators.

The CREATE DATABASE privilege is a special kind of privilege as it is saved in the security database.A list of users with the CREATE DATABASE privilege is available from the virtual table SEC$DB_CREATORS.Only administrators in the security database can grant the privilege to create a new database.

Note

SCHEMA is currently a synonym for DATABASE;this may change in a future version, so we recommend to always use DATABASE

Examples of Granting Database DDL Privileges

  1. Granting SUPERUSER the privilege to create databases:

    GRANT CREATE DATABASE
  TO USER Superuser;

  2. Granting JOE the privilege to execute ALTER DATABASE for the current database:

    GRANT ALTER DATABASE
  TO USER Joe;

  3. Granting FEDOR the privilege to drop the current database:

    GRANT DROP DATABASE
  TO USER Fedor;