Add users mysql using two different ways, by using GRANT statement or manipulating Mysql users grant table.
The recommend method is to use GRANT Statement, because they are more concise, safe, and less error command. Book for learning basic until experd using mysql and how to get pwerfull performance mysql database.
For add user Mysql, You must be Administator user or also called root user.
The example below, show how add user using Grant Statement method.
user@localhost> mysql --user=root mysql {enter into Mysql database, using root user}
mysql> GRANT ALL PRIVILEGES ON *.* TO newuser@localhost
IDENTIFIED BY 'some_password' WITH GRANT OPTION; newuser is superuser but only from localhost}.
mysql> GRANT ALL PRIVILEGES ON *.* TO newuser@"%"
IDENTIFIED BY 'some_password' WITH GRANT OPTION; {newuser is superuser but only from localhost}.
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost; ( user admin have Administrative privileges, like the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist)
mysql> GRANT USAGE ON *.* TO dummy@localhost; ( user can connect without password, but only from localhost).
Issue Grant Statement for both newuser@localhost and newuser@"%", if we don’t add the entry with localhost, the anonymous user can entered into Mysql take precedence when connect from outside localhost, it very dangerous, because it we has a more specific Host field value for prevent it.
The example below, add users use the specific privilege for each user.
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON tbl_data.*
TO didai@localhost
IDENTIFIED BY 'stupid'; (user didai at localhost have a privileges mentioned, but must use a password ‘stupid’)
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON tbl_transaction.*
TO didai@greenhouse
IDENTIFIED BY 'stupid';(user didai from hostname “greenhouse” have a privileges mentioned and must use a password ‘stupid’)
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON tbl_customer.*
TO didai@'%' IDENTIFIED BY 'stupid';(user didai from anonymous host have a privileges mentioned and must use a password ‘stupid’)
Or you can manipulating user in table user at database Mysql, like below.
INSERT INTO user VALUES('localhost','user_abcd',PASSWORD('pass_abcd), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
Or
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('localhost','transaction','user_abcd','Y','Y','Y','Y','Y','Y');
Both of The example is very crucial command and not usefull.
Now, the choice is in you. Choose the choice with safety consideration.
Thank you, I hope this little article is something worthwhile
The recommend method is to use GRANT Statement, because they are more concise, safe, and less error command. Book for learning basic until experd using mysql and how to get pwerfull performance mysql database.
For add user Mysql, You must be Administator user or also called root user.
The example below, show how add user using Grant Statement method.
user@localhost> mysql --user=root mysql {enter into Mysql database, using root user}
mysql> GRANT ALL PRIVILEGES ON *.* TO newuser@localhost
IDENTIFIED BY 'some_password' WITH GRANT OPTION; newuser is superuser but only from localhost}.
mysql> GRANT ALL PRIVILEGES ON *.* TO newuser@"%"
IDENTIFIED BY 'some_password' WITH GRANT OPTION; {newuser is superuser but only from localhost}.
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost; ( user admin have Administrative privileges, like the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist)
mysql> GRANT USAGE ON *.* TO dummy@localhost; ( user can connect without password, but only from localhost).
Issue Grant Statement for both newuser@localhost and newuser@"%", if we don’t add the entry with localhost, the anonymous user can entered into Mysql take precedence when connect from outside localhost, it very dangerous, because it we has a more specific Host field value for prevent it.
The example below, add users use the specific privilege for each user.
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON tbl_data.*
TO didai@localhost
IDENTIFIED BY 'stupid'; (user didai at localhost have a privileges mentioned, but must use a password ‘stupid’)
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON tbl_transaction.*
TO didai@greenhouse
IDENTIFIED BY 'stupid';(user didai from hostname “greenhouse” have a privileges mentioned and must use a password ‘stupid’)
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON tbl_customer.*
TO didai@'%' IDENTIFIED BY 'stupid';(user didai from anonymous host have a privileges mentioned and must use a password ‘stupid’)
Or you can manipulating user in table user at database Mysql, like below.
INSERT INTO user VALUES('localhost','user_abcd',PASSWORD('pass_abcd), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
Or
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('localhost','transaction','user_abcd','Y','Y','Y','Y','Y','Y');
Both of The example is very crucial command and not usefull.
Now, the choice is in you. Choose the choice with safety consideration.
Thank you, I hope this little article is something worthwhile
reference from http://mysql.com
