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.
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.
WITH ADMIN OPTION
ClauseThe optional WITH ADMIN OPTION
clause allows the users specified in the user list to grant the role(s) specified to other users or roles.
Caution
|
It is possible to assign this option to |
For cumulative roles, a user can only exercise the WITH ADMIN OPTION
of a secondary role if all intermediate roles are also granted WITH ADMIN OPTION
.That is, GRANT ROLEA TO ROLE ROLEB WITH ADMIN OPTION
, GRANT ROLEB TO ROLE ROLEC
, GRANT ROLEC TO USER USER1 WITH ADMIN OPTION
only allows USER1
to grant ROLEC
to other users or roles, while using GRANT ROLEB TO ROLE ROLEC WITH ADMIN OPTION
allows USER1
to grant ROLEA
, ROLEB
and ROLEC
to other users.
Assigning the DIRECTOR
and MANAGER
roles to the user IVAN
:
GRANT DIRECTOR, MANAGER
TO USER IVAN;
Assigning the MANAGER
role to the user ALEX
with the authority to assign this role to other users:
GRANT MANAGER
TO USER ALEX WITH ADMIN OPTION;
Assigning the DIRECTOR
role to user ALEX
as a default role:
GRANT DEFAULT DIRECTOR
TO USER ALEX;
Assigning the MANAGER
role to role DIRECTOR
:
GRANT MANAGER
TO ROLE DIRECTOR;
WITH GRANT OPTION
ClauseThe 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 |
GRANTED BY
ClauseBy 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.
When using the REVOKE
statement, it will fail if the current user is not the user that was named in the GRANTED BY
clause.
The GRANTED BY
(and AS
) clause can be used only by the database owner and other administrators.The object owner cannot use GRANTED BY
unless they also have administrator privileges.
AS username
The non-standard AS
clause is supported as a synonym of the GRANTED BY
clause to simplify migration from other database systems.
For tables and views, unlike other metadata objects, it is possible to grant several privileges at once.
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
GRANT <privilege>
on TablesSELECT
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;
EXECUTE
PrivilegeThe 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
PrivilegeGranting the EXECUTE
privilege on a stored procedure to a role:
GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ
TO ROLE MANAGER;
Granting the EXECUTE
privilege on a stored function to a role:
GRANT EXECUTE ON FUNCTION GET_BEGIN_DATE
TO ROLE MANAGER;
Granting the EXECUTE
privilege on a package to user PUBLIC
:
GRANT EXECUTE ON PACKAGE APP_VAR
TO USER PUBLIC;
Granting the EXECUTE
privilege on a function to a package:
GRANT EXECUTE ON FUNCTION GET_BEGIN_DATE
TO PACKAGE APP_VAR;
USAGE
PrivilegeTo 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 |
Note
|
For sequences (generators), the |
USAGE
PrivilegeGranting the USAGE
privilege on a sequence to a role:
GRANT USAGE ON SEQUENCE GEN_AGE
TO ROLE MANAGER;
Granting the USAGE
privilege on a sequence to a trigger:
GRANT USAGE ON SEQUENCE GEN_AGE
TO TRIGGER TR_AGE_BI;
Granting the USAGE
privilege on an exception to a package:
GRANT USAGE ON EXCEPTION
TO PACKAGE PKG_BILL;
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.
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 |
Allow user JOE
to create tables
GRANT CREATE TABLE
TO USER Joe;
Allow user JOE
to alter any procedure
GRANT ALTER ANY PROCEDURE
TO USER Joe;
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.
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
|
|
Granting SUPERUSER
the privilege to create databases:
GRANT CREATE DATABASE
TO USER Superuser;
Granting JOE
the privilege to execute ALTER DATABASE
for the current database:
GRANT ALTER DATABASE
TO USER Joe;
Granting FEDOR
the privilege to drop the current database:
GRANT DROP DATABASE
TO USER Fedor;