Size: 2706
Comment:
|
Size: 4012
Comment:
|
Deletions are marked like this. | Additions are marked like this. |
Line 22: | Line 22: |
1. Create directory structure with proper permission within the AFS volume | 1. Create directory structure with proper permission within the AFS volume (the sole existence of the directory is enough for MySQL to consider it a database, even if just an empty one). NOTE: If we stick to existing behavior on fyodor, requested database name should be prefixed with USERNAME_ . |
Line 29: | Line 29: |
1. vos create -server afs -partition a -name db.USERNAME -maxquota 5000 1. fs mkmount -dir /afs/.hcoop.net/common/databases/USERNAME -vol db.USERNAME -rw |
1. vos create -server afs -partition a -name db.'''USERNAME''' -maxquota 5000 1. fs mkmount -dir /afs/.hcoop.net/common/databases/'''USERNAME''' -vol db.'''USERNAME''' -rw |
Line 32: | Line 32: |
1. fs setacl -dir /afs/hcoop.net/common/databases/'''USERNAME''' -acl mysql l | |
Line 34: | Line 35: |
1. $dir = /afs/hcoop.net/common/databases/USERNAME/mysql | 1. $dir = /afs/hcoop.net/common/databases/'''USERNAME'''/mysql |
Line 36: | Line 37: |
1. mkdir $dir/DBNAME || exit # (Must not exist) 1. chown mysql:mysql $dir/DBNAME 1. chmod 770 $dir/DBNAME # (Just for visual impression) 1. ln -sf $dir/DBNAME /var/lib/mysql/DBNAME 1. fs setacl -dir /afs/hcoop.net/common/databases/USERNAME/mysql/DBNAME/ -acl mysql rlidwka 1. mysql -e 'create database DBNAME' |
1. fs setacl -dir /afs/hcoop.net/common/databases/'''USERNAME'''/mysql -acl mysql l 1. mkdir $dir/'''DBNAME''' || exit # (Must not exist) 1. chown mysql:mysql $dir/'''DBNAME''' 1. chmod 770 $dir/'''DBNAME''' # (Just for visual impression) 1. ln -sf $dir/'''DBNAME''' /var/lib/mysql/'''DBNAME''' 1. fs setacl -dir $dir/'''DBNAME'''/ -acl mysql all 1. mysql -e 'create database '''DBNAME'''' |
Line 43: | Line 45: |
( Now, about users and granting permissions to the database, I would like to see users being able create new users and passwords and their privileges (to their databases) themselves). This would allow fine-grained tuning of what service uses which DB username/pw, and what access rights it has. |
Now, about users and granting permissions to the database, I would like to see users being able create new users and passwords and their privileges (to their databases) themselves. This would allow fine-grained tuning of what service uses which DB username/pw, and what access rights it has. Maybe a list of users/passwords, or an appropriate support ticket would be cool. |
Line 50: | Line 53: |
1. CREATE USER 'USERNAME'@'HOSTNAME' IDENTIFIED BY 'PASSWORD'; 1. GRANT SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE VIEW,SHOW VIEW,GRANT OPTION,CREATE USER TO 'USERNAME'@'HOSTNAME'; |
1. CREATE USER ''''USERNAME''''@''''HOSTNAME'''' IDENTIFIED BY ''''PASSWORD''''; 1. GRANT SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE VIEW,SHOW VIEW,GRANT OPTION ON '''DBNAME''' TO '''USERNAME'''@'%.hcoop.net'; |
Line 55: | Line 58: |
There are two other things related to users, one is changing password and the other is deleting users. These simply map to mysql SET PASSWORD and DROP USER commands, if you go to implement them. And one last thing; mysql is listening both on localhost and network interfaces (deleuze external IP). Maybe we want to restrict it to run on just one of them. Or if not, if it will run on both, then the access rule ( USERNAME@'%.hcoop.net' ) has to be duplicated in a way (for USERNAME@localhost), OR the users will always have to specify mysql host "deleuze" instead of "localhost". Another solution to this is that we don't try to be clever at all, but simply let users make sure the hostname part in their support ticket will match the interface they'll be using to connect. |
1. DaemonAdmin/MySQL
Because we wanted to have version 5 of MySQL running on our stable server, we used the [http://dotdeb.org] package. For this reason there should be mirrors from dotdeb.org listed in the file /etc/apt/sources.list on deleuze.
1.1. Configuration details
my.cnf file modified to leave a port open over the network, default is local pipe only.
1.2. Progress
As of Sat Jan 6 12:29:23 EST 2007, the MySQL 5.0 daemon and client libraries have been installed on deleuze. I also installed the mysql-common and mysql-client 5.0 packages on mire which should will allow for users to connect back to the main DB server.
1.3. To Do
dbtool will need to be programmed to store data files in user home directories. It will also need to set up the appropriate GRANT permission on creation of each database to allow a connection from mire or other front-end servers by default.
1.4. Steps to perform
Ok, logically, the steps are:
- Create user's database volume in AFS, if one isn't there already
- Create directory structure with proper permission within the AFS volume (the sole existence of the directory is enough for MySQL to consider it a database, even if just an empty one). NOTE: If we stick to existing behavior on fyodor, requested database name should be prefixed with USERNAME_ .
- Create a symbolic link in /var/lib/mysql/ that points to the database
- Grant the user rights on the new DB
And, in terms of command line, the steps are:
(This is needed only once, for first DB - MySQL or any other):
vos create -server afs -partition a -name db.USERNAME -maxquota 5000
fs mkmount -dir /afs/.hcoop.net/common/databases/USERNAME -vol db.USERNAME -rw
- vos release root.cell
fs setacl -dir /afs/hcoop.net/common/databases/USERNAME -acl mysql l
(And this for every mysql DB):
$dir = /afs/hcoop.net/common/databases/USERNAME/mysql
- mkdir $dir
fs setacl -dir /afs/hcoop.net/common/databases/USERNAME/mysql -acl mysql l
mkdir $dir/DBNAME || exit # (Must not exist)
chown mysql:mysql $dir/DBNAME
chmod 770 $dir/DBNAME # (Just for visual impression)
ln -sf $dir/DBNAME /var/lib/mysql/DBNAME
fs setacl -dir $dir/DBNAME/ -acl mysql all
mysql -e 'create database DBNAME'
Now, about users and granting permissions to the database, I would like to see users being able create new users and passwords and their privileges (to their databases) themselves. This would allow fine-grained tuning of what service uses which DB username/pw, and what access rights it has. Maybe a list of users/passwords, or an appropriate support ticket would be cool.
So anyway, the procedure for creating a user and giving privileges, executed on behalf of the admin user (domtool2):
CREATE USER 'USERNAME'@'HOSTNAME' IDENTIFIED BY 'PASSWORD';
GRANT SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE VIEW,SHOW VIEW,GRANT OPTION ON DBNAME TO USERNAME@'%.hcoop.net';
- FLUSH PRIVILEGES;
(Thing to note here: Wildcard '%' can be used in hostname part, for things like '%.hcoop.net'.)
There are two other things related to users, one is changing password and the other is deleting users. These simply map to mysql SET PASSWORD and DROP USER commands, if you go to implement them.
And one last thing; mysql is listening both on localhost and network interfaces (deleuze external IP). Maybe we want to restrict it to run on just one of them. Or if not, if it will run on both, then the access rule ( USERNAME@'%.hcoop.net' ) has to be duplicated in a way (for USERNAME@localhost), OR the users will always have to specify mysql host "deleuze" instead of "localhost". Another solution to this is that we don't try to be clever at all, but simply let users make sure the hostname part in their support ticket will match the interface they'll be using to connect.