User rights management in MySQL implementation
User rights management in MySQL implementation of the Configuration database utilizes the access control mechanisms of the underlying MySQL database engine. MySQL access control is based on "grants" of the set of access rights to a particular user. With the big number of users and big number of dynamically created tables this system quickly becomes cumbersome and difficult to manage.
To simplify the task of managing grants in the MySQL database a system was developed on top of MySQL which reduces the complexity by organizing users and tables into groups and granting rights on the group basis. The system is not specific to Configuration database and can be used by other databases as well.
MySQL identifies user by the account name provided by the client application to the MySQL server. Usually this is a user account name on the client computer (login name, UNIX account, etc.) but some clients allow to specify alternative account name when connecting to the server. Accounts names which are registered in MySQL server can be granted special rights for accesing databses and/or tables. If the account name is not registered in the server then account is considered "anonymous". Anonymous account can also be given some rights, or can be disabled completely. Both registered accounts and anonymous account can be assigned non-empty password, in which case the client application has to provide this password when connecting to server.
Because of the big and changing number of the BaBar collaborators which need read-only access to configuration database it would be difficult to manage registration for every single user. Because of this it was decided to allow read-only access to the Configuration database to the anonymous account, so that everybody who can connect to the server can also access Configuration data. Connection to the server itself can be managed on per-host basis, so there is at least some control over who can see the data.
Fewer number of users will need to be able also to modify the data in the database. These users have to be registered in the MySQL users tables and assigned particular rights on particular tables. The applications which modify the content of the configuration database may not always be able to "ask" user her/his password. Because of this it was decided to allow password-free access to the users of Configuration database. This may sound risky, but alternatives are not significantly better. Carefully designed system of rights should limit the damage in case of unintentional incidents. The accounts with broader rights, like those who allowed to delete tables or register new users, wil be required to use passwords.
In our system we usually have several users who can be responsible for the same set of tasks, e.g. one subsystem configuration has few experts assigned to it. It makes sense to assign the manage rights of this group of people as a single "super-person". For this the concept of the user group was introduced in out management system. Basically the user group is a named entity which contains the set of account names. User group has corresponding grants (or rights) and every user added to a group receives the same set of grants. The user can belong to more than one group in which case user's grants are superposition of the groups' grants to which the user belongs. There is one special user group named "all-users" which include all users registered in the system.
The subsystem sub-database usually consists of a set of the MySQL tables. Some tables or a complete set of tables can share the same access rights for the users who can modify them.Like in case with users, the management could be easier if we could treat the set of tables as one "super-table" with respect to the rights assignment. There is a concept of table group in the managements system which does exactly that. The table group is a named entity which includes the set of the table names belonging to that group. The grants assigned to the table group are propagated to every table in the group.
There is one special case for the table name based on how MySQL grant system works. MySQL distinguises between table-level access rights and database-level access. To get access to a particular table one needs a grant for a particulat table name. To execute the database-level operations, such as creation of new tables, one needs database-level grant which does not have corresponding table name. In MySQL this grant is given to a pseudo-table with the name "*". This name is also used in our management system, so if database-level grants are needed one has to include this pseudo-table name "*" into corresponding table group.
Similarly to MySQL itself where the users are granted rights to execute particular set of operations on particular tables, in our system the user groups are granted rights to execute operations on the table groups. These rights are translated by the system into the normal MySQL rights, so that every user in a user group receives the same rights for every table in a table group. Effective user rights to a particvcular table is determined by the user and table participation in all user groups and table groups respecively.
When new user is added to a user group the system automatically assigns to this new account all grants that the user group has on every table group. Similarly, when new table name is added to some table group, the grants for all users on this new table are evaluated based on the table/user groups.
The following grant names are supported by the system:
- SELECT - allows use of 'SELECT ...' queries
- INSERT - allows use of 'INSERT ...' queries
- DELETE - allows use of 'DELETE ...' queries
- UPDATE - allows use of 'UPDATE ...' queries
- GRANT - allows use of 'GRANT ...' queries
- CREATE - allows use of 'CREATE TABLE' queries
- DROP - allows use of 'DROP TABLE' queries
There are some common-sense limitations on which grants should be used at which level, for details it is better to consult the technical description in the RdbMySQLAuthDB/README file (link is below).
There are both command-line and GUI tools which can perform all the management tasks on this user management system. For the brief description and the list of supported operations please consult the README file, linked below.
Below is the information specific to the Configuration database describing how users and tables are organized into groups and which rights are granted.
Configuration database server is running at IR2, its current host name is bfo-rdb101. The MySQL database name for the configuration database is CfgDB. You will see these in few example commands below.
Here is complete set of user groups defined today:
% RdbMySQLAuthCmd -s bfo-rdb101 -d CfgDB groups
bdb # Auth group for BDB
dch # Auth group for DCH
dct # Auth group for DCT
drc # Auth group for DRC
emc # Auth group for EMC
emt # Auth group for EMT
fct # Auth group for FCT
glt # Auth group for GLT
ifr # Auth group for IFR
l3d # Auth group for L3D
orc # Auth group for ORC
svt # Auth group for SVT
top # Auth group for TOP
trg # Auth group for TRG
all-users # Special system group which includes every user
container-makers # Users who can create new containers
As you see there is a group for each of the subsystems. Besides there is one special group already mentioned above, 'all-users', which includes everybody.
One other special group 'container-makers' is for the users who will be able to create new conatiners in the configuration database. That process involves creation of new tables in the database and need wider rights compared to the resto f the system. Consequence of this is that in MySQL, compared to the old Objectivity implementation, containers must be created explicitely by the dedicated person who is included into 'container-makers' group.
Here is the complete list of table groups defined today:
% RdbMySQLAuthCmd -s bfo-rdb101 -d CfgDB tgroups
bdb-tables # Table group for BDB
dch-tables # Table group for DCH
dct-tables # Table group for DCT
drc-tables # Table group for DRC
emc-tables # Table group for EMC
emt-tables # Table group for EMT
fct-tables # Table group for FCT
glt-tables # Table group for GLT
ifr-tables # Table group for IFR
l3d-tables # Table group for L3D
orc-tables # Table group for ORC
svt-tables # Table group for SVT
top-tables # Table group for TOP
trg-tables # Table group for TRG
bdb-alias-tables # Table group for BDB aliases
dch-alias-tables # Table group for DCH aliases
dct-alias-tables # Table group for DCT aliases
drc-alias-tables # Table group for DRC aliases
emc-alias-tables # Table group for EMC aliases
emt-alias-tables # Table group for EMT aliases
fct-alias-tables # Table group for FCT aliases
glt-alias-tables # Table group for GLT aliases
ifr-alias-tables # Table group for IFR aliases
l3d-alias-tables # Table group for L3D aliases
orc-alias-tables # Table group for ORC aliases
svt-alias-tables # Table group for SVT aliases
top-alias-tables # Table group for TOP aliases
trg-alias-tables # Table group for TRG aliases
common-tables # Tables which are common to all systems
mgmt-tables # Data management tables which need special rights
whole-db # Special group for datbase-level access
Every subsystem now has two table groups, one table group is for the "numeric" database table, and one for "alias" tables. We had to separate those into two groups because the numeric tables and alias tables have different update pattern. In particular in the numeric tables the rows are never deleted, while in alias tables information can be deleted. Separating numeric tables into separate group allows to have tighter rights for them, preventing inintetional loss of information in those tables.
Other table groups include: 'common-tables' which includes tables that can be updated by everybody (like alias history, or type information table); 'mgmt-tables' which includes tables related to the management operations (not used yet); and 'whole-db' which includes special pseudo-table "*".
Here is the complete set of group grants defined today:
% RdbMySQLAuthCmd -s bfo-rdb101 -d CfgDB ggrants
bdb bdb-alias-tables INSERT,DELETE
bdb bdb-tables INSERT
dch dch-alias-tables INSERT,DELETE
dch dch-tables INSERT
dct dct-alias-tables INSERT,DELETE
dct dct-tables INSERT
drc drc-alias-tables INSERT,DELETE
drc drc-tables INSERT
emc emc-alias-tables INSERT,DELETE
emc emc-tables INSERT
emt emt-alias-tables INSERT,DELETE
emt emt-tables INSERT
fct fct-alias-tables INSERT,DELETE
fct fct-tables INSERT
glt glt-alias-tables INSERT,DELETE
glt glt-tables INSERT
ifr ifr-alias-tables INSERT,DELETE
ifr ifr-tables INSERT
l3d l3d-alias-tables INSERT,DELETE
l3d l3d-tables INSERT
orc orc-alias-tables INSERT,DELETE
orc orc-tables INSERT
svt svt-alias-tables INSERT,DELETE
svt svt-tables INSERT
top top-alias-tables INSERT,DELETE
top top-tables INSERT
trg trg-alias-tables INSERT,DELETE
trg trg-tables INSERT
all-users whole-db SELECT
all-users common-tables INSERT
container-makers whole-db INSERT,DELETE,GRANT,CREATE
For more technical details check the README file in RdbMySQLAuthDB package.
|
