
MySQL - database setup

There are three cases of identity to consider when providing
access to the MySQL system for the browser CGI binaries
and browser developers:

1.  A MySQL user that needs read-only access to the
	genome databases.  The browser CGI binaries
	require read-only access to the genome databases.
2.  A MySQL user that has write permissions to one database.
	The CGI binaries require write permissions to one particular
	database (hgcentral) for maintaining user's cart information to
	store the user's browser cookie settings.
3.  A MySQL user that has general write permissions to all
	browser and genome databases to be used by developers

The cgi-bin binaries obtain two MySQL identities from the text
file: $WEBROOT/cgi-bin/hg.conf

Developers of the browser databases obtain their MySQL identities
from a text file in their home directory: ~/.hg.conf
Note the initial dot in the name: .hg.conf
This file in a user's directory will specify a higher-privileged user
to allow read/write access to the MySQL databases.
This file must be set to mode 600 to provide security of the user
and password to the database:
	$ chmod 600 ~/.hg.conf
All kent source code commands use this file to access the MySQL
databases.  Since this file contains password information it
requires the permissions to be set at 600 to keep it secret.
The kent source code commands will enforce this access and not
function unless it is set at 600 permissions.

Therefore you will want to create three different MySQL users
for these purposes.

	(The examples listed below are implemented in the
	    shell script: ex.MySQLUserPerms.sh
	    You can execute that script to set up these example users.)

An example full read/write access user: "browser",  is created with
the following procedure.

For the following it is assumed that your root account
has access to the mysql database.  You should be able
to perform the following at a root login prompt:

$ export SQL_PASSWORD=mysql_root_password
$ mysql -u root -p${SQL_PASSWORD} -e "show tables;" mysql

Create a MySQL user called "browser" with password
"genome" and give access to selected MySQL commands
for the following list of databases.  When you add other
databases, you will need to add these permissions to your
databases.

for DB in cb1 hgcentral hgFixed hg17 proteins040315
do
    mysql -u root -p${SQL_PASSWORD} -e "GRANT SELECT, INSERT, UPDATE, DELETE, \
	FILE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES on ${DB}.* \
	TO browser@localhost \
	IDENTIFIED BY 'genome';" mysql
done

The above granted permissions are recommended for browser developers.
The WEB browser CGI binaries only need SELECT and CREATE TEMPORARY
TABLES permissions.  For example, you could create a special user for
the browser genome databases only.  In this example, user: "readonly"
with password: "access"

for DB in cb1 hgcentral hgFixed hg17 proteins040315
do
    mysql -u root -p${SQL_PASSWORD} -e "GRANT SELECT, CREATE TEMPORARY TABLES \
	on ${DB}.* TO \
	readonly@localhost IDENTIFIED BY 'access';" mysql
done

A third MySQL user can be created with read-write access to only
the hgcentral database.  For example, a user: "readwrite"
with password: "update"

for DB in hgcentral
do
    mysql -u root -p${SQL_PASSWORD} -e "GRANT SELECT, INSERT, UPDATE, DELETE, \
	CREATE, DROP, ALTER on ${DB}.* TO readwrite@localhost \
	IDENTIFIED BY 'update';" mysql
done

The cgi-bin binaries obtain their MySQL identities from
the hg.conf file in the cgi-bin directory.  The file in this
directory: ex.hg.conf
demonstrates the use of the "readonly" user for genome database
access and the "readwrite" user for hgcentral database access.

====================================================================

Developers can access the browser databases via the 'hgsql'
command which can be built in the source-tree at:
	kent/src/hg/hgsql/

This 'hgsql' command provides a convenient front-end to
the standard 'mysql' command by reading the user's ~/.hg.conf
file to provide access to the browser databases with the
appropriate identity.  Each user creates a ~/.hg.conf file
(same format as the above mentioned cgi-bin/hg.conf file)
and the specified database user identity is used for accesses
to the browser databases.

This same function of reading ~/.hg.conf for database access
is built into all the source-tree binaries which modify the genome
databases.

The above example hg.conf could be used as a user's ~/.hg.conf
file with the change of db.user, db.password, central.user,
and central.password to be the fully permitted read-write user:

db.user=browser
db.password=genome
central.user=browser
central.password=genome

To test this access with your ~/.hg.conf file in place:

$ hgsql -e "show tables;" hgcentral

====================================================================
This file last updated: $Date: 2010/12/02 09:32:56 $
====================================================================
