| 1 |
SQL Language Extension: CREATE/ALTER/CREATE_OR_ALTER/DROP USER |
| 2 |
|
| 3 |
Implements capability to manage users from regular database attachment. |
| 4 |
|
| 5 |
|
| 6 |
Author: |
| 7 |
Alex Peshkoff <[email protected]> |
| 8 |
|
| 9 |
|
| 10 |
Syntax is: |
| 11 |
|
| 12 |
CREATE USER name [ options ] [ TAGS ( tag [, tag [, tag ...]] ) ] |
| 13 |
ALTER USER name [ SET ] [ options ] [ TAGS ( tag [, tag [, tag ...]] ) ] |
| 14 |
ALTER CURRENT USER [ SET ] [ options ] [ TAGS ( tag [, tag [, tag ...]] ) ] |
| 15 |
CREATE OR ALTER USER name [ SET ] [ options ] [ TAGS ( tag [, tag [, tag ...]] ) ] |
| 16 |
DROP USER name [ USING PLUGIN name ]; |
| 17 |
|
| 18 |
where OPTIONS is a list of following options: |
| 19 |
- PASSWORD 'password' |
| 20 |
- FIRSTNAME 'firstname' |
| 21 |
- MIDDLENAME 'middlename' |
| 22 |
- LASTNAME 'lastname' |
| 23 |
- ACTIVE |
| 24 |
- INACTIVE |
| 25 |
- USING PLUGIN name |
| 26 |
|
| 27 |
and each TAG may have one of two forms: |
| 28 |
name = 'string value' |
| 29 |
or: |
| 30 |
DROP name |
| 31 |
where NAME is any valid SQL identifier. |
| 32 |
|
| 33 |
|
| 34 |
Description: |
| 35 |
|
| 36 |
Makes it possible to add, modify and delete users in security database using SQL language. |
| 37 |
|
| 38 |
Firebird since version 3.0 supports multiple security databases. gsec utility and services API |
| 39 |
do not support it and use of them to manage users is deprecated. |
| 40 |
|
| 41 |
CREATE and DROP clauses are available only for SYSDBA (or other user, granted RDB$ADMIN role in |
| 42 |
security database). Ordinary user can ALTER his own password, wide names and tags. Attempt to modify |
| 43 |
another user will fail. Also will fail an attempt to make yourself inactive or active. In order to |
| 44 |
avoid typing your name each time simplified form ALTER CURRENT USER is present. |
| 45 |
|
| 46 |
At least one of PASSWORD, FIRSTNAME, MIDDLENAME, LASTNAME, ACTIVE, INACTIVE or TAGS must be present |
| 47 |
in ALTER USER statement. Also notice that PASSWORD clause is required when creating new user. |
| 48 |
|
| 49 |
PASSWORD clause is enough self-descripting. Clauses FIRSTNAME, MIDDLENAME and LASTNAME too, but may |
| 50 |
be also used to store any short information about user. Clauses INACTIVE/ACTIVE are used to disable |
| 51 |
user's login to server not dropping it from the list and restoring that ability. USING PLUGIN clause |
| 52 |
makes it possible to work with users not only with default management plugin (first listed in |
| 53 |
UserManager parameter in firebird.conf) but also with others. gsec utility and services API do not |
| 54 |
support multiple user management plugins - default one is always used. |
| 55 |
|
| 56 |
TAGS is a list of end-user defined attributes. Length of the value should not exceed 255 bytes. |
| 57 |
Assigning name some value sets new or modifies existing tag. To remove the tag use DROP option. |
| 58 |
Setting a list of tags for the user keeps earlier set tags if they are not mentioned currently. |
| 59 |
Notice - UID/GID, entered by deprecated gsec, are treated as tags in SQL interface. |
| 60 |
|
| 61 |
To access list of users please select from virtual tables SEC$USERS and SEC$USER_ATTRIBUTES. |
| 62 |
|
| 63 |
|
| 64 |
Samples (suppose UserManager=Srp,Legacy_UserManager in firebird.conf): |
| 65 |
|
| 66 |
Generic: |
| 67 |
CREATE USER alex PASSWORD 'test'; |
| 68 |
ALTER USER alex SET FIRSTNAME 'Alex' LASTNAME 'Peshkoff'; |
| 69 |
CREATE OR ALTER USER alex SET PASSWORD 'IdQfA'; |
| 70 |
DROP USER alex; |
| 71 |
ALTER CURRENT USER SET PASSWORD 'SomethingLongEnough' USING PLUGIN srp; |
| 72 |
|
| 73 |
Working with tags: |
| 74 |
ALTER USER alex SET TAGS (a='a', b='b'); |
| 75 |
NAME VALUE |
| 76 |
================ ============================== |
| 77 |
A a |
| 78 |
B b |
| 79 |
|
| 80 |
ALTER USER alex SET TAGS (b='x', c='d'); |
| 81 |
NAME VALUE |
| 82 |
================ ============================== |
| 83 |
A a |
| 84 |
B x |
| 85 |
C d |
| 86 |
|
| 87 |
ALTER USER alex SET TAGS (drop a, c='sample'); |
| 88 |
NAME VALUE |
| 89 |
================ ============================== |
| 90 |
B x |
| 91 |
C sample |
| 92 |
|
| 93 |
Displaying users' list: |
| 94 |
SELECT CAST(U.SEC$USER_NAME AS CHAR(20)) LOGIN, |
| 95 |
CAST(A.SEC$KEY AS CHAR(10)) TAG, |
| 96 |
CAST(A.SEC$VALUE AS CHAR(20)) "VALUE", |
| 97 |
U.SEC$PLUGIN "PLUGIN" |
| 98 |
FROM SEC$USERS U LEFT JOIN SEC$USER_ATTRIBUTES A |
| 99 |
ON U.SEC$USER_NAME = A.SEC$USER_NAME |
| 100 |
AND U.SEC$PLUGIN = A.SEC$PLUGIN; |
| 101 |
|
| 102 |
LOGIN TAG VALUE PLUGIN |
| 103 |
==================== ========== ==================== =============================== |
| 104 |
SYSDBA <null> <null> Srp |
| 105 |
ALEX B x Srp |
| 106 |
ALEX C sample Srp |
| 107 |
SYSDBA <null> <null> Legacy_UserManager |
| 108 |
|
| 109 |
Notice! |
| 110 |
This feature highly depends upon user management plugin. Take into an account that some options |
| 111 |
are ignored when using legacy user management plugin. |