Hack 93. Auto-Create Database Users

Databases are useful to users. Consider creating database accounts for shell accounts.

When you create a shell account for a user, you expect that process to create everything that a general user might need. For instance, creating a user will also create an email account. So why not automatically create a database account for that user? After all, if the user has shell access on the machine, he can potentially see the databases anyway. This is better than having a single shared database account, because then you can log actions and know specifically who did what (see "Create an Audit Trail" [Hack #96]).

"Create Users and Administrators" [Hack #94] shows the different SQL commands needed to create a database account. You could follow this hack and enter the commands by hand, but it would be nicer if the Linux/Unix adduser script did this for you. For simplicity, we will be talking only about MySQL here, but you can use "Create Users and Administrators" [Hack #94] to convert the script to SQL Server or Oracle.

For ease of use it is best to make the username in the operating system the same as the username in the database. During the creation process, you can assign the same password to both the MySQL and shell accounts.

The following script, nuseradd, creates Unix and database users simultaneously:

#!/bin/bash if (( "$#" > 0 )) ; then eval user=$$# echo Running adduser for : $user useradd $@ if (( "$?" == 0 )) ; then pass=Q./easypass.pl -p 1Q echo $pass | passwd --stdin $user echo "MySQL password for $user set to $pass" cmd="create database $user; use $user;" cmd="$cmd grant all privileges on *.* to '$user'@'localhost' identified by '$pass' with grant option;"; cmd="$cmd flush privileges; " echo -e "$cmd" | mysql -u root -ppassword fi fi

When the script runs, all parameters are passed to the normal Linux user creation script (useradd). The last parameter on the command line is the name of the new user, and this is captured in the variable user. The initial password for the user is created using a password generator tool called easypass (http://iatrogenic.cx/easypass.html), which generates random but pronounceable passwords (e.g., creates passwords such as flickwhale rather than Ae13rrf). The new password is printed to the screen. The password can then be given to the new user along with his username. You should ask users to change their passwords to strong passwords that only they know.

Of course, you should make a corresponding script so that when the user is deleted, his MySQL account (or at least his privileges) are deleted.