Home arrow Applications Tips arrow MySQL Privileges
MySQL Privileges PDF
Sunday, 01 June 2008
After you've installed mysql is good to change (add) a password for mysql root user (which is different from system root user. After you do that, the best practice is to add mysql users with different privileges (and not use mysql root user) for applications you will install.

To set a password for a mysql user:

  mysqladmin -u root -h hostname password 'new_pass'

If you already have a password setup and you want to change it use:

  mysqladmin -u root -p password newpass

where newpass is your new password. You will be asked for old password.

If you do not have set by default a mysql password (is a fresh install of mysql):

  mysqladmin password mypassword

run your mysql client

# mysql -u root -p

After you've input your mysql password and are loged to your mysql client, type:

mysql> grant all privileges on *.* to 'username'@'localhost' identified by 'password' with grant option;

mysql> grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;

mysql> grant reload, process on *.* to 'admin'@'localhost';

Then you can use mysql client to connect to your mysql server, using "username" account:

 #mysql -u username -h 10.0.0.1 -p

(where 10.0.0.1 is your mysql database server)

Comments:
First grant rules reffers only to localhost. If you want to connect to your database server from other machines instead of localhost type the ip of your machine, or % if you want to connect from any host.

To see grants for an user use:
mysql> show grants for 'username'@'localhost';

To revoke privileges from a user:
mysql> revoke all privileges on *.* from 'username'@'localhost';

To see all mysql grants:
mysql> show grants;

To delete a user from mysql:
mysql> DROP user username;

Last Updated ( Wednesday, 27 April 2011 )
 
< Prev   Next >

Other BSD Systems

OpenBSD

Misc

Solaris

Polls

Best BSD firewall?