Sometimes you don’t want to expose your MySQL root user or password to a third party, but do need to give that party access to a super user or administrator account that has full control over the MySQL server instance.
Turns out, to add such a super user account is pretty easy.
First, access your MySQL console with:
mysql -u root -p
Once in, run the following SQL statement:
GRANT ALL PRIVILEGES ON *.* TO myusername@`%` IDENTIFIED BY 'mypassword';
(‘myusername’ and ‘mypassword’ should of course be substituted for your own values. Also, you don’t have to give the account such wide access by exchanging the any host % symbol with a specific IP/host if necessary)
[IMPORTANT: This has the potential to seriously damage your system if it goes wrong – mucking up the sudo command had grave consequences for any linux distro. Try it out in a test environment first if you can!]
First, add the new user account through the use of the useradd command. Note the added switches which create the user’s home directory in the specified location.
sudo useradd -d /home/newuseraccount -m newuseraccount
With the user created, next set his password using:
sudo passwd newuseraccount
(I like bash as my default shell, so you can follow these steps to do this if needed).
Now the fun part. While we can make use of the visudo command to directly edit the sudoers file (in the past this was the only way to do it), nowadays it is advised to rather make custom changes in an extra file, basically meaning that you can now easily upgrade your system without losing all of your carefully crafted user accounts. To do this, create a file in the /etc/sudoers.d directory and edit it.
sudo touch /etc/sudoers.d/sudo-custom sudo chmod 0440 /etc/sudoers.d/sudo-custom sudo nano /etc/sudoers.d/sudo-custom
Append the following line to add your new user account to the list of sudo accounts:
newuseraccount ALL=(ALL:ALL) ALL
Note we’ve gone and created a really powerful sudo account with the declaration above. You might want to temper it a bit by reading up on the various options available to you.
There are two commands which you can use to create an user account on an Ubuntu Server installation. The first command is the adduser command which is not very flexible but is user friendly in that it prompts you for data with each and every step in the creation process.
The other more flexible command is useradd. Simply calling it with the desired username as a parameter will create a user account, but without listing extra options this user account won’t have a password or even a home directory. One of the better ways of calling it then is as such:
sudo useradd -d /home/newuseraccount -m newuseraccount sudo passwd newuseraccount
The above will add a new user on the system with the username “newuseraccount”. The -d option specifies where to create the home directory and the -m forces the creation of your specified directory. Note it copies files for the new home directory from the /etc/skel folder. Although we could have specified a password by using the -p option, it might be a better idea to stick with the classic passwd call to do this.
First, fire up MySQL in your terminal and login as an administrator account with:
mysql -u root -p
You will be prompted for your root password before being granted access to the MySQL monitor.
Now to grab a list of available user accounts on the system. Running:
select user,host from mysql.user;
If the user account that you wish to create doesn’t appear in that list, you are good to go. To create a new user account run the following query:
create user 'new-username'@'localhost' identified by 'password-for-new-account';
If you omit the @’localhost’ section, the user account will be generated with a host value of ‘%’. If you omit the identified by section, the user account will be created without a password (which is of course quite insecure).
Of course, this user isn’t linked to any databases yet, but this is simple to rectify with a:
grant all on mydatabase.* to useraccount@localhost
that will grant all rights for the user account to everything held in the mydatabase database. (Note that you generally don’t want to assign all rights, so will scale back as necessary in a real world example.)
Simple and nifty.
Related Link: http://dev.mysql.com/doc/refman/5.1/en/create-user.html