Hello!
Do you really want to create new user in mysql ?
If so, let's do it...
By default mysql(I use community edition) has user with name root on host localhost and password is empty.
We need to connect to mysql in order to create new user and grant some permissions to him. For this we use default user.
To connect to mysql you need to open command line and point it to bin directory of mysql installation folder.
I am on WindowsXP and mysql folder(mysql-5.6.11-win32) is on disk D.
I type in command line:
cd /d d:\mysql-5.6.11-win32\bin
Press Enter and you will be at specified folder. In our case this is bin folder of mysql installation folder.
Now we can use mysql.exe file to connect to mysql.
As we do not have any user yet except default one we must use default user to connect to mysql. To connect we type:
D:\mysql-5.6.11-win32\bin>mysql -u root -p //press Enter after -p
Enter password: //you should only press Enter here without any space
mysql>
So we connected to mysql !
Let's now see users present by default. For this we should use mysql database. Yes, mysql server has its own database called mysql where all users are stored. To use mysql we type :
mysql> use mysql;
Database changed
This database has table called user that holds user's name,password,host, permitted and prohibited privileges. Let's select interested data from table user:
mysql> select user,host,password from user;
password from user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *D55338571AC4FD0DE25D565852610148810E72E5 |
| root | ::1 | |
+------+-----------+-------------------------------------------+
2 rows in set
This is the result of table user of mine because I modified it before. If mysql server is new than the output will be different.
This was table user where mysql users are stored.
Let's now create new mysql user with custom name and password. I take host as localhost,user as volodia and password ****.
There are several ways to create new user. The most basic is
create user volodia@localhost identified by 'your_password';
We created user with name volodia on host localhost and password your_password.
But this way we created user with no privileges. Such user can connect to mysql server and:
In order to allow user to do more operations we need to grant privileges to him.
But we do not have any database yet to grant privileges to. To create database vovaDB we type:
mysql> create database vovaDB;
Query OK, 1 row affected
To grant privileges to database vovaDB we use grant keyword.
mysql> grant all on vovaDB.* to volodia@localhost;
Query OK, 0 rows affected
At this moment newly created user volodia has been granted all privileges to vovaDBdatabase.
However there is another way to insert user and grant specific or all privileges to him. This implies that we directly insert data for new user into tables that mysql use to store users.
We have seen already one such table - user. This table is useful if we need to assign password and only global privileges to new user. Global privileges mean privileges to all databases. To create new user with privileges to certain database I should insert user data into db table. db is table of mysql database as well as user table is.
So let's prove all this by examples. We should have already created database vovaDB. If you didn't that please do :
mysql> use mysql;
Database changed
Good! Table user has very many fields and almost all refer to specific global privileges. By default all privileges are prohibited. We permit only two global privileges - select and insert:
mysql> insert into user(host,user,password,select_priv,insert_priv) values('localhost','vovaUser',password('md5Password'),'Y','Y');
Query OK, 1 row affected
This is usual insert query. Note that we used special function called password() to have our password ciphered by md5 algorithm. In table user, in column pasword, ciphered value is stored but we should use value md5Password when password is prompted. Also note that for columns select_priv and insert_priv we set 'Y' , meaning that global select and insert privileges are allowed for new user. Finally the user column stores name of new user that isvovaUser in our case.
If we modify grant table(like user or db) directly we must tell mysql server to reload privileges so that new user and his privileges take affect:
mysql> flush privileges;
Query OK, 0 rows affected
Only after we flushed privileges we can use new user vovaUser.
Let's now verify that new user vovaUser exists and really has select,insert privileges for all databases. At first we should disconnect current user from mysql:
mysql> quit
Bye
We use name vovaUser and password md5Password to connect to mysql:
D:\mysql-5.6.11-win32\bin>mysql -u vovaUser -p //press Enter after -p
mysql> use vovadb;
Database changed
mysql> show tables;
Empty set
Tha fact that we successfully changed the database by use vovaDB; says that we have select privilege for it. Let's use mysql's own database mysql:
mysql> use mysql;
Database changed
mysql> select host,user,password from user;
+-----------+----------+-------------------------------------------+
| host | user | password |
+-----------+----------+-------------------------------------------+
| localhost | root | *D55338571AC4FD0DE25D565852610148810E72E5 |
| ::1 | root | |
| localhost | vovaUser | *25B5F3671BF79EB8B8C82896762F2843F12639BF |
+-----------+----------+-------------------------------------------+
3 rows in set
We obtained data from mysql database by using newly created user vovaUser.
Note that table user has also column called user.
We have select privilege to this database. Let's now verify insert privilege.
mysql> insert into user(host,user,password) values('localhost','goodUser',password('goodPassword'));
Query OK, 1 row affected
Insert is successful. Let's now delete user data we just inserted:
mysql> delete from user where user='goodUser' and password='goodPassword';
ERROR 1142 (42000): DELETE command denied to user 'vovaUser'@'localhost' for table 'user'
So, we see that vovaUser(we are connected with) does not have permission to use DELETE command. Note that we haven't flushed privileges after last insert thus we can't connect to mysql with goodUser. Recall that before we selected data and inserted record into user table successfully. Also with vovaUser we could select data from another database vovaDB. But DELETE command cannot be used!
So, by inserting new user's data into user table of mysql database we can assign password to new user as well as global privileges.
If it is needed to create new user and assign specific privileges to certain database we insert new record into db table. Note that with new user inserted into db database we cannot connect to mysql server because it does not have password. To assign password to him we need to create corresponding record in user table.
Let's do all this.
At first we should login to mysql server with default user root in order to delete users we created in previous examples. To delete user we can use drop keyword:
mysql> drop user vovaUser@localhost;
Query OK, 0 rows affected
This deletes vovaUser. Although we haven't flushed privileges of goodUser we inserted it into table user. Therefore it is there and to delete it we do the same:
mysql> drop user goodUser@localhost;
Query OK, 0 rows affected
Now let's insert new record into db table of mysql database:
mysql> use mysql;
Database changed
mysql> insert into db(host,db,user,select_priv,insert_priv) values('localhost','mysql','dbUser','Y','Y');
Query OK, 1 row affected
This creates new user dbUser with select and insert priviliges to mysql database(specified in db column). But unfortunately we can't connect with new user because it does not have associated password. To associate password we must create record for our user insideuser table :
mysql> insert into user(host,user,password) values('localhost','dbUser',password('dbUser'));
Query OK, 1 row affected
Note that we do not allow any global privileges with 'Y' because we want dbUser to have select,insert permissions only for mysql database. Also take notice that we use special function called password() to store password.
The preceding two inserts changed grant tables therefore we must reload privileges for changes to take affect.
mysql> flush privileges;
Query OK, 0 rows affected
Note that flush privileges; may be done whenever not necessarily in the same session as changes to grant tables.
Let's disconnect now:
mysql> quit
Bye
Good bye to this user and let's connect with just created dbUser:
D:\mysql-5.6.11-win32\bin>mysql -u dbUser -pdbUser //press Enter after -pdbUser
mysql>
Note how password can be specified(-pdbUser). Let's verify that dbUser really has select and insert permissions only for mysql database. At first we want to see which databases are accessible to dbUser:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set
mysql> use vovaDB;
ERROR 1044 (42000): Access denied for user 'dbUser'@'localhost' to database 'vovadb'
This is it. dbUser cannot access vovaDB. And if we try to use mysql database :
mysql> use mysql;
Database changed
Yes, we changed to mysql database. Recall that we enabled select and insert privileges formysql database. So, let's select user column from user table
mysql> select user,host from user;
+--------+-----------+
| user | host |
+--------+-----------+
| root | ::1 |
| dbUser | localhost |
| root | localhost |
+--------+-----------+
3 rows in set
Good! Let's try to select data from db table of mysql database:
mysql> select user,host from db;
+--------+-----------+
| user | host |
+--------+-----------+
| | % |
| | % |
| dbUser | localhost |
+--------+-----------+
3 rows in set
It works too. Now we try to insert new record into db table:
mysql> insert into db(host,db,user) values('localhost','someDB','someUser');
Query OK, 1 row affected (0.00 sec)
This shows that we can insert into mysql database with user dbUser. It is left to select data from db table again to see that it is there :
mysql> select user,db,host from db;
+----------+---------+-----------+
| user | db | host |
+----------+---------+-----------+
| | test | % |
| | test\_% | % |
| dbUser | mysql | localhost |
| someUser | someDB | localhost |
+----------+---------+-----------+
4 rows in set
This all works as expected ! dbUser can select from and insert into mysql database. And we specified this inserting dbUser into db table.
So, to sum up if we want to create new user with privileges to some specific database we need to insert new record into db table of mysql database and to assign password to this new user we create new record in user table. Note that we can also specify privileges applicable to certain database inside db table. And to set global privileges applicable to all databases we specify them in user table of mysql database.
To quickly create user identified by some password:
create user someUser@localhost identified by 'somePassword';
To set all privileges to certain database for certain user :
grant all on someDB.* to user@localhost;
To set all privileges to all databases for certain user :
grant all on *.* to user@localhost;
Note that using grant keyword we do not need to flush privileges as we did inserting into dband user tables.
If you have any questions,suggestions, post them in comments please!
Do you really want to create new user in mysql ?
If so, let's do it...
By default mysql(I use community edition) has user with name root on host localhost and password is empty.
We need to connect to mysql in order to create new user and grant some permissions to him. For this we use default user.
To connect to mysql you need to open command line and point it to bin directory of mysql installation folder.
I am on WindowsXP and mysql folder(mysql-5.6.11-win32) is on disk D.
I type in command line:
cd /d d:\mysql-5.6.11-win32\bin
Press Enter and you will be at specified folder. In our case this is bin folder of mysql installation folder.
Now we can use mysql.exe file to connect to mysql.
As we do not have any user yet except default one we must use default user to connect to mysql. To connect we type:
D:\mysql-5.6.11-win32\bin>mysql -u root -p //press Enter after -p
Enter password: //you should only press Enter here without any space
mysql>
So we connected to mysql !
Let's now see users present by default. For this we should use mysql database. Yes, mysql server has its own database called mysql where all users are stored. To use mysql we type :
mysql> use mysql;
Database changed
This database has table called user that holds user's name,password,host, permitted and prohibited privileges. Let's select interested data from table user:
mysql> select user,host,password from user;
password from user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *D55338571AC4FD0DE25D565852610148810E72E5 |
| root | ::1 | |
+------+-----------+-------------------------------------------+
2 rows in set
This is the result of table user of mine because I modified it before. If mysql server is new than the output will be different.
This was table user where mysql users are stored.
Let's now create new mysql user with custom name and password. I take host as localhost,user as volodia and password ****.
There are several ways to create new user. The most basic is
create user volodia@localhost identified by 'your_password';
We created user with name volodia on host localhost and password your_password.
But this way we created user with no privileges. Such user can connect to mysql server and:
- use databases whose name is called test or start with word test;
- query mysql's own database called information_schema;
In order to allow user to do more operations we need to grant privileges to him.
But we do not have any database yet to grant privileges to. To create database vovaDB we type:
mysql> create database vovaDB;
Query OK, 1 row affected
To grant privileges to database vovaDB we use grant keyword.
mysql> grant all on vovaDB.* to volodia@localhost;
Query OK, 0 rows affected
At this moment newly created user volodia has been granted all privileges to vovaDBdatabase.
However there is another way to insert user and grant specific or all privileges to him. This implies that we directly insert data for new user into tables that mysql use to store users.
We have seen already one such table - user. This table is useful if we need to assign password and only global privileges to new user. Global privileges mean privileges to all databases. To create new user with privileges to certain database I should insert user data into db table. db is table of mysql database as well as user table is.
So let's prove all this by examples. We should have already created database vovaDB. If you didn't that please do :
mysql> create database vovaDB;
Query OK, 1 row affected
Now we create user by inserting user's data into user table. At first we should select database of user table :mysql> use mysql;
Database changed
Good! Table user has very many fields and almost all refer to specific global privileges. By default all privileges are prohibited. We permit only two global privileges - select and insert:
mysql> insert into user(host,user,password,select_priv,insert_priv) values('localhost','vovaUser',password('md5Password'),'Y','Y');
Query OK, 1 row affected
This is usual insert query. Note that we used special function called password() to have our password ciphered by md5 algorithm. In table user, in column pasword, ciphered value is stored but we should use value md5Password when password is prompted. Also note that for columns select_priv and insert_priv we set 'Y' , meaning that global select and insert privileges are allowed for new user. Finally the user column stores name of new user that isvovaUser in our case.
If we modify grant table(like user or db) directly we must tell mysql server to reload privileges so that new user and his privileges take affect:
mysql> flush privileges;
Query OK, 0 rows affected
Only after we flushed privileges we can use new user vovaUser.
Let's now verify that new user vovaUser exists and really has select,insert privileges for all databases. At first we should disconnect current user from mysql:
mysql> quit
Bye
We use name vovaUser and password md5Password to connect to mysql:
D:\mysql-5.6.11-win32\bin>mysql -u vovaUser -p //press Enter after -p
Enter password: md5Password
mysql>
Note that password and username are case-sensitive! We use vovaDB:mysql>
mysql> use vovadb;
Database changed
mysql> show tables;
Empty set
Tha fact that we successfully changed the database by use vovaDB; says that we have select privilege for it. Let's use mysql's own database mysql:
mysql> use mysql;
Database changed
mysql> select host,user,password from user;
+-----------+----------+-------------------------------------------+
| host | user | password |
+-----------+----------+-------------------------------------------+
| localhost | root | *D55338571AC4FD0DE25D565852610148810E72E5 |
| ::1 | root | |
| localhost | vovaUser | *25B5F3671BF79EB8B8C82896762F2843F12639BF |
+-----------+----------+-------------------------------------------+
3 rows in set
We obtained data from mysql database by using newly created user vovaUser.
Note that table user has also column called user.
We have select privilege to this database. Let's now verify insert privilege.
mysql> insert into user(host,user,password) values('localhost','goodUser',password('goodPassword'));
Query OK, 1 row affected
Insert is successful. Let's now delete user data we just inserted:
mysql> delete from user where user='goodUser' and password='goodPassword';
ERROR 1142 (42000): DELETE command denied to user 'vovaUser'@'localhost' for table 'user'
So, we see that vovaUser(we are connected with) does not have permission to use DELETE command. Note that we haven't flushed privileges after last insert thus we can't connect to mysql with goodUser. Recall that before we selected data and inserted record into user table successfully. Also with vovaUser we could select data from another database vovaDB. But DELETE command cannot be used!
So, by inserting new user's data into user table of mysql database we can assign password to new user as well as global privileges.
If it is needed to create new user and assign specific privileges to certain database we insert new record into db table. Note that with new user inserted into db database we cannot connect to mysql server because it does not have password. To assign password to him we need to create corresponding record in user table.
Let's do all this.
At first we should login to mysql server with default user root in order to delete users we created in previous examples. To delete user we can use drop keyword:
mysql> drop user vovaUser@localhost;
Query OK, 0 rows affected
This deletes vovaUser. Although we haven't flushed privileges of goodUser we inserted it into table user. Therefore it is there and to delete it we do the same:
mysql> drop user goodUser@localhost;
Query OK, 0 rows affected
Now let's insert new record into db table of mysql database:
mysql> use mysql;
Database changed
mysql> insert into db(host,db,user,select_priv,insert_priv) values('localhost','mysql','dbUser','Y','Y');
Query OK, 1 row affected
This creates new user dbUser with select and insert priviliges to mysql database(specified in db column). But unfortunately we can't connect with new user because it does not have associated password. To associate password we must create record for our user insideuser table :
mysql> insert into user(host,user,password) values('localhost','dbUser',password('dbUser'));
Query OK, 1 row affected
Note that we do not allow any global privileges with 'Y' because we want dbUser to have select,insert permissions only for mysql database. Also take notice that we use special function called password() to store password.
The preceding two inserts changed grant tables therefore we must reload privileges for changes to take affect.
mysql> flush privileges;
Query OK, 0 rows affected
Note that flush privileges; may be done whenever not necessarily in the same session as changes to grant tables.
Let's disconnect now:
mysql> quit
Bye
Good bye to this user and let's connect with just created dbUser:
D:\mysql-5.6.11-win32\bin>mysql -u dbUser -pdbUser //press Enter after -pdbUser
mysql>
Note how password can be specified(-pdbUser). Let's verify that dbUser really has select and insert permissions only for mysql database. At first we want to see which databases are accessible to dbUser:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set
This does not show inherent mysql's own performance_schema database and another onevovaDB. Yes because information_schema and test databases are accessible for everybody whereas mysql database is accessible to dbUser as we specified when inserting.
To be very sure we try to use vovaDB:mysql> use vovaDB;
ERROR 1044 (42000): Access denied for user 'dbUser'@'localhost' to database 'vovadb'
This is it. dbUser cannot access vovaDB. And if we try to use mysql database :
mysql> use mysql;
Database changed
Yes, we changed to mysql database. Recall that we enabled select and insert privileges formysql database. So, let's select user column from user table
mysql> select user,host from user;
+--------+-----------+
| user | host |
+--------+-----------+
| root | ::1 |
| dbUser | localhost |
| root | localhost |
+--------+-----------+
3 rows in set
Good! Let's try to select data from db table of mysql database:
mysql> select user,host from db;
+--------+-----------+
| user | host |
+--------+-----------+
| | % |
| | % |
| dbUser | localhost |
+--------+-----------+
3 rows in set
It works too. Now we try to insert new record into db table:
mysql> insert into db(host,db,user) values('localhost','someDB','someUser');
Query OK, 1 row affected (0.00 sec)
This shows that we can insert into mysql database with user dbUser. It is left to select data from db table again to see that it is there :
mysql> select user,db,host from db;
+----------+---------+-----------+
| user | db | host |
+----------+---------+-----------+
| | test | % |
| | test\_% | % |
| dbUser | mysql | localhost |
| someUser | someDB | localhost |
+----------+---------+-----------+
4 rows in set
This all works as expected ! dbUser can select from and insert into mysql database. And we specified this inserting dbUser into db table.
So, to sum up if we want to create new user with privileges to some specific database we need to insert new record into db table of mysql database and to assign password to this new user we create new record in user table. Note that we can also specify privileges applicable to certain database inside db table. And to set global privileges applicable to all databases we specify them in user table of mysql database.
To quickly create user identified by some password:
create user someUser@localhost identified by 'somePassword';
To set all privileges to certain database for certain user :
grant all on someDB.* to user@localhost;
To set all privileges to all databases for certain user :
grant all on *.* to user@localhost;
Note that using grant keyword we do not need to flush privileges as we did inserting into dband user tables.
If you have any questions,suggestions, post them in comments please!
No comments:
Post a Comment