Firebird Documentation Index → Firebird 3.0.6 Release Notes → Security → SQL Features for Managing Access |
Changes in architecture, stiffening of rules for security and data integrity, along with a bucket list of feature requests, have given rise in this release to a number of new SQL commands for managing users and access to objects.
The SQL set of DDL commands for managing user accounts has been enhanced in Firebird 3, thus improving the capability to manage (add, modify or delete) users in a security database from a regular database attachment.
Syntax Forms
CREATE USERusername
[options_list
] [ TAGS ( tag [, tag [, tag ...]] ) ] ALTER USERusername
[ SET ] [options_list
] [ TAGS ( tag [, tag [, tag ...]] ) ] ALTER CURRENT USER [ SET ] [options_list
] [ TAGS ( tag [, tag [, tag ...]] ) ] CREATE OR ALTER USERusername
[ SET ] [ options ] [ TAGS ( tag [, tag [, tag ...]] ) ] DROP USERusername
[ USING PLUGINplugin_name
]
OPTIONS is a (possibly empty) list with the following options:
PASSWORD 'password'
FIRSTNAME 'string value'
MIDDLENAME 'string value'
LASTNAME 'string value'
ACTIVE
INACTIVE
USING PLUGIN plugin_name
Each TAG may have one of two forms:
TAGNAME
= 'string value'
or the DROP TAGNAME
tag form to remove a user-defined attribute entirely:
DROP TAGNAME
The tagname
side of the name/value pair can be any valid SQL identifier.
From Firebird 3.0, multiple security databases are supported. This capability is not supported by either the gsec utility or the Services API. Use of both of these methods is deprecated.
The CREATE USER, CREATE OR ALTER USER and DROP USER clauses are available only for SYSDBA or another user granted the RDB$ADMIN role in the security database (and logged in under that role, of course.)
The PASSWORD clause is required when creating a new user.
An ordinary user can ALTER his own password, real name attributes and tags. Any attempt to modify another user will fail, as will an attempt to make “self” inactive or active.
If you want to modify “self”, you can use the simplified form ALTER CURRENT USER.
At least one of PASSWORD, FIRSTNAME, MIDDLENAME, LASTNAME, ACTIVE, INACTIVE or TAGS must be present in an ALTER USER or CREATE OR ALTER USER statement.
It is not a requirement to use any of the clauses FIRSTNAME, MIDDLENAME and LASTNAME. Any of them may be left empty or used to store short information about the user.
The INACTIVE clause is used to disable the user's login capability without dropping it. The ACTIVE clause restores that login ability.
From v.3.0.1, the statement
CREATE OR ALTER USER SYSDBA PASSWORD <password>
can be used
to initialize an empty securityN.fdb
security database.
TAGS is a list of end-user defined attributes. The length of the string value should not exceed 255 bytes.
Setting a list of tags for the user retains previously set tags if they are not mentioned in the current list.
A UID or GID that was entered by the deprecated gsec utility is treated as a tag in the SQL interface.
Examples
Generic:
CREATE USER superhero PASSWORD 'test'; ALTER USER superhero SET FIRSTNAME 'Clark' LASTNAME 'Kent'; CREATE OR ALTER USER superhero SET PASSWORD 'IdQfA'; DROP USER superhero; ALTER CURRENT USER SET PASSWORD 'SomethingLongEnough';
Working with tags:
ALTER USER superhero SET TAGS (a='a', b='b'); NAME VALUE ================ ============================== A a B b ALTER USER superhero SET TAGS (b='x', c='d'); NAME VALUE ================ ============================== A a B x C d ALTER USER superhero SET TAGS (drop a, c='sample'); NAME VALUE ================ ============================== B x C sample
Displaying the list of users:
SELECT CAST(U.SEC$USER_NAME AS CHAR(20)) LOGIN, CAST(A.SEC$KEY AS CHAR(10)) TAG, CAST(A.SEC$VALUE AS CHAR(20)) "VALUE", SEC$PLUGIN "PLUGIN" FROM SEC$USERS U LEFT JOIN SEC$USER_ATTRIBUTES A ON U.SEC$USER_NAME = A.SEC$USER_NAME; LOGIN TAG VALUE PLUGIN ==================== ========== ==================== =============================== SYSDBA <null> <null> Srp SUPERHERO B x Srp SUPERHERO C sample Srp SYSDBA <null> <null> Legacy_UserManager
Output depends upon the user management plug-in. If the legacy plug-in is used, bear in mind that some options are not supported and will simply be ignored.
See Tracker item CORE-1377.
The SQL2008-compliant operator SET ROLE allows the CURRENT_ROLE context variable to be set to one that has been granted to the CURRENT_USER or to a user assigned to the database attachment as trusted (SET TRUSTED ROLE).
Syntax Pattern for SET ROLE
Enable CURRENT_USER access to a role that has been previously granted:
SET ROLE <rolename>
Example of SET ROLE Usage
SET ROLE manager; select current_role from rdb$database;
Displays:
ROLE =============================== MANAGER
The idea of a separate SET TRUSTED ROLE command is that, when the trusted user attaches to a database without providing any role info, SET TRUSTED ROLE makes a trusted role (if one exists) the CURRENT_ROLE without any additional activity, such as setting it in the DPB.
A trusted role is not a specific type of role but may be any role that was created using CREATE ROLE, or a predefined system role such as RDB$ADMIN. It becomes a trusted role for an attachment when the security objects mapping subsystem finds a match between the authentication result passed from the plug-in and a local or global mapping for the current database. The role may be one that is not even granted explicitly to that trusted user.
A trusted role is not assigned to the attachment by default. It is possible to change this behaviour using an appropriate authentication plug-in and a CREATE/ALTER MAPPING command.
Whilst the CURRENT_ROLE can be changed using SET ROLE, it is not always possible to revert using the same command, because it performs an access rights check.
Syntax Pattern
Enable access to a trusted role, if the CURRENT_USER is logged in under Trusted User authentication and the role is available:
SET TRUSTED ROLE
An example of the use of a trusted role is assigning the system role RDB$ADMIN to a Windows administrator when Windows trusted authentication is in use.
Previously, the grantor or revoker of SQL privileges was always the current user. This change makes it so that a different grantor or revoker can be specified in GRANT and REVOKE commands.
Syntax Pattern
grant <right> to <object> [ { granted by | as } [ user ] <username> ] revoke <right> from <object> [ { granted by | as } [ user ] <username> ]
The GRANTED BY clause form is recommended by the SQL standard. The alternative form using AS is supported by Informix and possibly some other servers and is included for better compatibility.
Example (working as SYSDBA)
create role r1; grant r1 to user1 with admin option; grant r1 to public granted by user1; -- (in isql) show grant; /* Grant permissions for this database */ GRANT R1 TO PUBLIC GRANTED BY USER1 GRANT R1 TO USER1 WITH ADMIN OPTION
When a user is removed from the security database or another authentication source, this new command is useful for revoking its access to all objects in the database.
Syntax Pattern
REVOKE ALL ON ALL FROM [USER] username REVOKE ALL ON ALL FROM [ROLE] rolename
Example
# gsec -del guest # isql employee fbs bin # ./isql employee Database: employee SQL> REVOKE ALL ON ALL FROM USER guest; SQL>
In Firebird 3, the system tables are read-only. This SQL syntax provides the means to assign metadata write privileges to specified users or roles for specified objects. See Tracker item CORE-735.
Some people have been applying the nickname “DDL privileges” to this feature. Don't confuse it with “DDL triggers”! A more useful nickname would be “Metadata privileges”.
Syntax Patterns
Granting metadata privileges:
GRANT CREATE <object-type> TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION]; GRANT ALTER ANY <object-type> TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION]; GRANT DROP ANY <object-type> TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION];
Revoking metadata privileges:
REVOKE [GRANT OPTION FOR] CREATE <object-type> FROM [USER | ROLE] <user-name> | <role-name>; REVOKE [GRANT OPTION FOR] ALTER ANY <object-type> FROM [USER | ROLE] <user-name> | <role-name>; REVOKE [GRANT OPTION FOR] DROP ANY <object-type> FROM [USER | ROLE] <user-name> | <role-name>;
Special form for database access:
GRANT CREATE DATABASE TO [USER | ROLE] <user-name> | <role-name>; GRANT ALTER DATABASE TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION]; GRANT DROP DATABASE TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION]; REVOKE CREATE DATABASE FROM [USER | ROLE] <user-name> | <role-name>; REVOKE [GRANT OPTION FOR] ALTER DATABASE FROM [USER | ROLE] <user-name> | <role-name>; REVOKE [GRANT OPTION FOR] DROP DATABASE FROM [USER | ROLE] <user-name> | <role-name>;
Notes on Usage
<object-type> can be any of the following:
CHARACTER SET | COLLATION | DOMAIN | EXCEPTION |
FILTER | FUNCTION | GENERATOR | PACKAGE |
PROCEDURE | ROLE | SEQUENCE | TABLE |
VIEW |
The metadata for triggers and indices are accessed through the privileges for the table that owns them.
If the ANY option is used, the user will be able to perform any operation on any object
If the ANY option is absent, the user will be able to perform operations on the object only if he owns it
If the ANY option was acquired via a GRANT operation then, to revoke it, the REVOKE operation must accord with that GRANT operation
Example
GRANT CREATE TABLE TO Joe; GRANT ALTER ANY TABLE TO Joe; REVOKE CREATE TABLE FROM Joe;
CORE-2554: EXECUTE permission is now supported for UDFs (both legacy and PSQL based ones).
Syntax Pattern
GRANT EXECUTE ON FUNCTION <name> TO <grantee list> [<grant option> <granted by clause>] -- REVOKE EXECUTE ON FUNCTION <name> FROM <grantee list> [<granted by clause>]
The initial EXECUTE permission is granted to the function owner (user who created or declared the function).
A recursive stored procedure no longer requires the EXECUTE privilege to call itself. See Tracker item CORE-3242.
New SQL-2008 compliant USAGE permission is introduced to protect metadata objects other than tables, views, procedures and functions.
Syntax Pattern
GRANT USAGE ON <object type> <name> TO <grantee list> [<grant option> <granted by clause>] -- REVOKE USAGE ON <object type> <name> FROM <grantee list> [<granted by clause>] -- <object type> ::= {DOMAIN | EXCEPTION | GENERATOR | SEQUENCE | CHARACTER SET | COLLATION}
The initial USAGE permission is granted to the object owner (user who created the object).
In Firebird 3.0.0, only USAGE permissions for exceptions (CORE-2884) and generators/sequences (gen_id, next value for: CORE-2553) are enforced. Granting privileges for character sets, collations and domains is disabled, making these object types unavailable for any type of GRANT or REVOKE commands. Access to them is not subject to any form of enforcement, although this could change in future releases if it is deemed appropriate.
Firebird Documentation Index → Firebird 3.0.6 Release Notes → Security → SQL Features for Managing Access |