Tuesday, September 11, 2007

How To Create New Users In Mysql With PRIVILEGES

Adding New User In MySQL

We can create users in MySQL in two ways:
1) By using statements intended for creating accounts, such as CREATE USER or GRANT
2) By manipulating the MySQL grant tables directly with statements such as INSERT,
UPDATE, or DELETE

I will show you how, can we add user using the first method, that is using MySQL grant command.

First, use the mysql program to connect to the server as the MySQL root user. Here root user is representing the Main Super Admin kind of user. If we have password for this Super user root then perform the following command,

First, use the mysql program to connect to the server as the MySQL root user:
shell> mysql --u=root –p

The system will ask you for password, then enter the password
Shell>*******

If you don’t have any password set for the Super User root, u can connect to the MySQL server as follows,

shell> mysql --user=root




Then select the mysql database (Note:: here the name of the database is it self mysql)

Shell> use mysql





After connecting to the server as root, you can add new accounts. The following
A) To add an user with username “sidd” and password “mypassword” who can connect only from “localhost” and with all privileges and grant option.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'sidd'@'localhost'
-> IDENTIFIED BY 'mypassword' WITH GRANT OPTION;


B) To add an user with username “siddharth” and password “mypassword” who can connect only from any host and with all privileges and grant option.


mysql> GRANT ALL PRIVILEGES ON *.* TO 'siddharth'@'%'
-> IDENTIFIED BY ' mypassword' WITH GRANT OPTION;



C) To add and user “junioruser” with no password, and without any privileges

mysql> GRANT USAGE ON *.* TO ' junioruser'@'localhost';

in this example the user "junioruser" can connect only from localhost to the database server. In the same example if you want the same user to be able to connect from any computer
fire the following ,

mysql> GRANT USAGE ON *.* TO ' junioruser'@'%';

The USAGE privilege in the GRANT statement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later.


D) To Add and user maintenanceacc who will not access the database, but will perform database related maintenances work like
mysqladmin reload, mysqladmin refresh etc.

mysql> GRANT RELOAD,PROCESS ON *.* TO ' maintenanceacc admin'@'localhost';




No comments :

Post a Comment