# $Id: README,v 1.4 2005/06/23 22:54:22 salnikov Exp $

Package: RdbMySQLAuthDB
Date   : 2005-06-15
Author : Andy Salnikov


This package contains code for managing authorization groups in MySQL 
database. MySQL has limited support for the access control already, 
the idea is to use it and extend it to make things more manageable. 
One obvious improvement is to control access at the groups level, then 
instead of granting access to individual users it should be possible 
to add user to a group, the rest of the tedious tasks would be done by 
the system. This package tries to do just that.

There are two management utilities in this package - RdbMySQLAuthCmd 
and RdbMySQLAuthGUI - and one C++ class for accessing the limited set 
of the features from C++ code. 


                                Users

Users in MySQL database are identified by the name which are usually 
the standard UNIX account name. More precisely, the name of the user 
is the name supplied by the client application and could in fact be 
anything. Most BaBar applications use standard UNIX account name as 
the user name, and most users should not care about this at all.

Management applications in this package are the ones to which you can 
give different user name and non-empty password. Many management tasks 
that they can do need higher privileges that most normal users have, 
so you probably want to setup a separate MySQL user account with 
sufficient privileges and user name separate from any real users, and 
of course non-empty password. The privileges which are needed for that 
account should probably include complete access to the whole database 
where the authorization database lives. You can create such an account 
using a command like this (you probably have to be root user to issue 
such a command):

GRANT ALL PRIVILEGES ON CfgDB.* TO 'cfgdb-admin'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION

Replace 'CfgDB', 'cfgdb-admin' and a 'password' with something 
different in this command.

If a user is not registered in MySQL server, she could only connect as 
an anonymous user, which usually means very limited access (like 
read-only access or no access at all.) Users have to be registered in 
the database before you can give them any other privileges, look below 
for 'ucreate' command.


                            User passwords

The applications which we are building for use with the 
configuration/conditions databases do not allow us to specify the user 
passwords for accessing MySQL database. Many of them run in 
batch/detached mode so they can't request input from a user at all. It 
could be possible to specify a user password in the user-level MySQL 
options file (~/.my.cnf), but management of these file would be 
extremely difficult in such distributed environment. As a result we 
have decided that regular users accessing MySQL through regular BaBar 
applications will have empty passwords. This poses a kind of security 
threat, but through the use of well-defined policies and limiting what 
ordinary users can do, we hope to reduce the potential damage to 
acceptable limit.


                             User groups

MySQL does not have any notion of user groups. If you use only 
standard MySQL features, you have to grant privileges to individual 
users which can quickly become very problematic task.

This package introduces the idea of the groups. Group is just a name 
like "orc" or "admins". You can add users to a group, and user can be 
a member of more than one group. When you grant access to a table for 
a group, it means that every member of this group will be granted the 
same privileges.

There is also one special user group which includes every user 
registered in the database, with the name 'all-users'. Users are 
included into this group automatically when you create new user 
records.


                             Table groups

To simplify management of the access right for individual tables, the 
tables are logically organized in the table groups too. Again, a table 
group is just a name like 'orc-tables' or 'whole-db'. You can add 
tables to the groups and any table can be a member of several groups 
too.


                         MySQL access control

MySQL controls the access to the data at several levels:

    1. global level
    2. database level
    3. table level
    4. column level

(We do not consider column-level access here, so you can just forget 
about it.) When checking for user privileges for some operation server 
goes from first to last and stops when it finds that access is allowed 
at that level. 

It's probably not advisable to grant any special access rights at the 
global level, because it could allow access to every database, 
including very special 'mysql' database. The only thing that global 
level should allow is the ability to connect to the server at all 
('USAGE' grant).

Database level controls access to all tables in the database and some 
database-level operations (like create new table). So if you know 
that a user should have specific access, e.g. 'SELECT' on any table, 
even not existing yet, you should grant access at database level. 

Table-level access allows further fine-tuning of the privileges. If 
you know that some users will need a special access to some tables in 
the database you should give them specific rights for those tables 
only. One of the drawbacks here is that, at least in MySQL, it is not 
possible to grant access for the table which does not exist yet. This 
means that every time a new table is created its grants need to be 
adjusted, and this imposes some limitation on who is allowed to create 
new tables.

At the SQL level the commands which grant rights to user look very 
similar for database level and table level. Compare this:

Table:     GRANT INSERT ON ATable TO 'some-user'@'%'
Database:  GRANT INSERT ON * TO 'some-user'@'%'

One can say that database grants are logically the same as table 
grants except that a table has special name '*'. You can use this 
special name as a table name when working with the command line tool 
from this package. The GUI tool displays '[database]' string instead 
of '*', but it is internally translated to '*' too.


                             Group grants

Using the tools from this package you can grant rights for specific 
user group on specific table group. When you do this the tools will 
grant corresponding grants for every user in a user group to every 
table in a table group. Same would happen when you add new user to a 
user group, or when you add a table to table group.

One particular note that when you do any of the above, the 
corresponding 'GRANT ...' command are executed on every user/table. 
This implies that the account which does these operations needs to 
have 'GRANT OPTION' right on every table or a whole database, and also 
it needs to have all rights which are being granted too (users in 
MySQL can only grant rights which she they themselves.)

                             Grant names

This package support following grant names:

    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

'CREATE' right should be granted at the database level, rest could be 
granted at both database/table level. 'GRANT' should probably always 
accompany 'CREATE', because once you have created a table, the next 
step would be to add it to one or more table groups, which executes 
all corresponding 'GRANT ...' queries. Also as it was mentioned above, 
the user needs to have all rights which are granted to other users for 
this table. This implies that the user group which is allowed to 
create tables should have many database-level privileges. You probably 
want to limit the number of users in that group to absolute minimum 
(one.)


                          Management tables

The tools in this package use a set of tables to keep 
user/table/group/rights information. Here is the complete list:

    RdbAuthUsers         - list of registred users and their names
    RdbAuthUGroups       - list of all user groups
    RdbAuthTGroups       - list of all table groups
    RdbAuthUser2UGroup   - users <-> groups map
    RdbAuthTable2TGroup  - tables <-> groups map
    RdbAuthGrants        - granted rights for user/table groups

These tables are modified when you do some particular operations. For 
example, when you add new table to a group, RdbAuthTable2TGroup is 
modified. So you have to manage access to these tables too, and add 
them to the corresponding table groups, maybe along with the other 
tables.


                          Command line tool

The command line tool - RdbMySQLAuthCmd - can perform all management 
tasks and has many subcommands. You can get the list of the supported 
options as usual starting it with the -h key:

% RdbMySQLAuthCmd -h

The list of the supported subcommands can be obtained with this command:

% RdbMySQLAuthCmd help

If you only want to browse the authorization information, you probably 
don't need any special access, chances are that ordinary users are 
already granted 'SELECT' access on the corresponding database. But you 
would still need to know the name of the server where MySQL is running 
and the database name. Alternatively you could use 'domain' name such 
as 'Configuration' to specify which database you want to look at. Here 
are couple of examples:

% RdbMySQLAuthCmd -D Configuration users
% RdbMySQLAuthCmd -s bfo-rdb100 -d CfgDB users

Note that if you do not use any of the above options, the application 
will try to connect to a server which runs locally. 

If you want to manage the authorization database and want to change 
something in it, you would need sufficient privileges for that. 
Details depend on your particular setup, but most probably you would 
need to use a special MySQL account for that, and that account would 
need a password. You would need to specify both on the command line 
like this:

% RdbMySQLAuthCmd -D Configuration -u cfgdb-admin -p password init

Note that password is specified in a clear text, so if you decide to 
store the commands in the script, at least don't make that script 
readable for everybody.

Here is the list of the subcommands supported by this tool:

  1. Informational commands (do not need special rights)

    users      - list all registered users
    groups     - list all user groups
    tgroups    - list all table groups
    members    - list the members of a group
    membership - list the groups having a user as a member
    tables     - list tables - the members of a group
    grants     - list all user/table grants
    ggrants    - list all user group/table group grants

  2. Management commands (need special MySQL privileges on a database)

    init       - initialize database
    destroy    - delete authorization database
    ucreate    - create new user record
    udelete    - delete existing user record
    gcreate    - create new user group
    gdelete    - delete existing user group
    tgcreate   - create new table group
    tgdelete   - delete existing table group
    uinclude   - include user into group
    uexclude   - exclude user from group
    tinclude   - include table into group
    texclude   - exclude table from group
    grant      - grant access to tables for a group
    revoke     - revoke access to tables from a group

Usage summary for each of the above commands can be obtained by 
executing the command with 'help' as an argument, like in this 
example:

% RdbMySQLAuthCmd -D Configuration ucreate help

In principle I planned to produce detailed description of each command 
here before the year 2008, so stay tuned, I have 2 more years :)


                               GUI tool

Another application in this package - RdbMySQLAuthGUI - allows you to 
perform a subset of all possible the operations using the graphical 
interface. When started it first prompts for the name of the server 
host/database name, and you also can give it different account name 
and a password. After that it connects to a database and reads the 
authorization information from it. This information is presented on 
three separate panels: Users panel, Tables panel, and Grants panel.

Users panel shows the names of the registered users, user groups and 
members of the groups. If you have enough privileges, you can also 
register new users, delete users, create or delete groups, and add or 
delete users to/from groups.

Tables panel shows all tables in the database, table groups and tables 
in a group. You can create or delete groups, and add or remove tables 
from a groups. You cannot create new tables here, this is the 
application-specific task.

On the Grants panel you can see currently assigned rights for any user 
group/table group combination, and you can also easily modify granted 
rights set.


                            C++ interface

There is also a very limited interface to the authorization database 
for C++ (the main interface and the tools are written in Python.) It 
only has the features that are needed for typical C++ applications. 
The interface consists of a single class RdbMySQLAuthDB with a small 
number of methods. 

C++ code will need occasionally to create new tables in the database, 
and after making a new table it should take care of adding a new table 
to a correct table group. The C++ interface has a method for that, 
called addTableToGroup() which takes a table name and a group name and 
returns bool (true for success.) Note that the user of a code which 
calls this method, and also of the code which makes new table, 
probably needs some special rights, like 'CREATE' and 'GRANT', and also 
'INSERT' on RdbAuthTable2TGroup table.

Typical usage of this method in the C++ code would be:

  RdbMySQLAuthDB authDb ( connection ) ;
  // execute a query to create new table
  res = query.execute ( "CREATE TABLE ATableForDch ( ... )" ) ;
  // you should check returned result, but I skip it here

  if ( ! authDb.addTableToGroup ( "ATableForDch", "dch-tables" ) ) {
    ErrMsg(error) << "Failed to add table ATableForDch to group dch-tables, check your privileges" << endmsg ;
    // transaction should be rolled back, but this is trickier than you'd think
  }


Before adding table to a group, it would be a good idea to check if 
there is such a group and create it if it does not exist yet. There 
are two methods in the C++ interface which allow you to do this: 
hasTableGroup() and newTableGroup(). The typical use if these two 
could be like this:

  RdbMySQLAuthDB authDb ( connection ) ;
  if ( ! authDB.hasTableGroup ( "dch-tables" ) ) {
    if ( ! authDB.newTableGroup ( "dch-tables", "This group is fo some tables" ) ) {
      ErrMsg(error) << fnName << " -- failed to create new table group: dch-tables" << endmsg ;
      return false ;
    }
  }

One more method - isGroupMember() - is used to check membership of a 
user. In principle this is not required for application, instead the 
application can rely on MySQL server which should return error code if 
the user is not authorized to do a particular operation. But for 
optimization purposes it should be possible to check if the user 
belongs to a particular group before trying to run a bunch of complex 
queries potentially involving many tables.

One possible example of this:

  RdbMySQLAuthDB authDb ( connection ) ;
  if ( ! authDb.isGroupMember( "dch-users" ) ) {
    ErrMsg(error) << "You are not a member of dch-users group!" << endmsg ;
    return false ;
  }

  // execute a query which messes up a database
  res = query.execute ( "INSERT INTO ATableForDch ..." ) ;