Debian Codegraph @

doc/sql.extensions/README.user_management

raw
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.