refdb access control builds directly on the MySQL access control system. The necessary information is stored in the database mysql that MySQL maintains.
Access control is at least a two-stage process. First the user must be able to access the database server at all; this is controlled by entries in the mysql.user table. Second, he needs certain access rights to the specific database he has to access as well as to two refdb databases (refdb and refdbvar); this is controlled by entries in the mysql.db table. refdb currently does not support access control on the level of tables or columns.
refdb automatically determines which additional access rights a user needs if you grant a user access to a specific database. However, if you remove access rights for a particular database, refdb will in no case automatically remove access rights for other databases or the database server itself. If you want to deny access to a previous user completely, you will have to do this manually with the mysql utility.
Use the adduser command of refdba to grant access to a database with the default access rights (Table 5-1). Use the following command to grant access rights on the database foo to the users "jack" and "jill", connecting from any host:
refdba: adduser -d foo -H % jack jill |
You'll have to add more of these commands if you have multiple reference databases that these users have to access. In this example we use the wildcard "%" as hostname to allow access from all hosts (i.e. all hosts which are not restricted by other means from even connecting to the box that runs the database server). You may as well specify a single host (e.g. foo.med.uth.tmc.edu) or a subnet (e.g. 192.168.1.%).
If the user does not yet have access rights to the database server, an entry will be added to mysql.user with the default access rights (none) and the provided host information. This will allow the new user to do nothing except what is explicitly granted for specific databases.
If the user does not yet have access rights to the refdb databases refdb and refdbvar for the given host, entries will be added to the mysql.db table with the default access rights (Table 5-1).
This is again done with the adduser. The access rights will be revoked for the specified host, username, and database. No other access rights will be modified. The following command will revoke the access rights we granted to user "jack" above.
refdba: adduser -d foo -H % -r jack |
Keep in mind that user "jack" still can access the database server as well as the databases refdb and refdbvar (and any other databases he was granted access to).
If the default access rights that refdb set don't serve your purposes, you can of course change them. As noted previously, there are two tables in the mysql databases to edit. First we'll look at the mysql.user table which grants access to the database server.
Note: All commands mentioned below assume that you have appropriate database server access rights, i.e. you must provide the database administrator username and password if your login name is not recognized as a database administrator name by MySQL.
The general philosophy of the MySQL access control is that more specific access rules can only add privileges to the more general rules; they can never take privileges away. This means that whatever you grant a user in the mysql.user table, he will be granted for every database, regardless of the more specific settings in the mysql.db table. Therefore it is generally a good idea to not grant the users anything in mysql.user (the mere existence of an entry will allow a user to connect to the database server from the specified host) and grant them acess rights to individual databases in mysql.db. This is what refdb does by default.
A good reason to do this differently is e.g. if you're a single user running refdb and MySQL on a standalone computer. You may be able to get along without any security at all. In this case you (with your administrator hat on) can grant users (you with your user hat on) sufficient access rights in mysql.user and you'll never have to think about user administration again.
Currently refdb has no tool to do fine-tune database server access rights. Use the command line tool mysql instead, which is part of the MySQL package. Open the internal security database with the command mysql mysql. At the mysql command line prompt, run the command:
mysql> insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv)
values ('%', 'new_user', password('new_pass'), 'Y', 'Y', 'Y', 'Y');
|
To let these changes take effect, run the following command in bash, substituting your real username and hostname:
#~ mysqladmin -h host -u username -p reload |
This will grant the user new_user access to the database server from all hosts with his new password new_pass. The user will be able to select, insert, update, and delete entries in all available databases. If you don't need password protection, pass an empty string at that position.
| Warning |
|
This includes access rights to the mysql database itself which is the key part of the access control. You should never use these settings in a networked system or a system with more than one user. |
You can modify all these settings to the security needs of your local installation. Consult the excellent online help at mysql.com or peruse one of the books about MySQL.
| Caution |
|
Due to the client-server design of refdb you must enter the location(s) of the application server refdbd into the Host field. It is irrelevant from which machine the user accesses the application server. If the application server runs on the same machine as the MySQL database server, you can use localhost in the Host field. |
The mysql.db table controls the access to individual databases. The default settings (Table 5-1) allow all users that were granted access to a particular database to read and modify entries in that database. All other operations, like creating or deleting tables, are not permitted.
The same holds true for the database refdb which contains common information like bibliography styles. Currently this is not of much use for the users, but in a future version of refdb users will be able to manage their own bibliography styles.
The database refdbvar is treated differently. This database is used to generate bibliographies which involves creating and deleting temporary tables. By default, users are granted the same access rights as mentioned above plus create and drop privileges.
Again, if these default settings don't suit your needs, just change them:
mysql> insert into db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) values ('%', 'foo', 'jill', 'Y', 'N', 'N', 'N');
|
To let these changes take effect, run the following command in bash, substituting your real database administrator username and hostname:
#~ mysqladmin -h host -u username -p reload |
This command makes the database foo "read-only" for the user "jill", i.e. she can run selects, but she can't change the contents of foo. This is a simple way to open up your reference database to a large number of "readers" while keeping the number of "contributors" limited.