w3resource logo


SQL exercises

SQL Exercises, Solution - SQL User Management

Secondary Nav

SQL User Management : 16 Exercise with Solution

1. How to create a user on localhost.

Syntax :

create user [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];

Example :

create user [email protected] identified by "mypasword";
  • 'ramaswamy' is the user name
  • 'localhost' is the machine name, here same machine
  • 'mypassword' is the password

2. How to create a user for an IP address other than localhost.

Syntax :

create user [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];

Example :

create user [email protected] identified by "mypasword";
  • 'ramaswamy' is the user name
  • '192.168.0.105' is the IP address
  • 'mypassword' is the password

3. How to grant permission to a user to select only from localhost.

Syntax :

grant [<permission_1,permission_2,...permission_n>|<all>] 
on [database].[<table_name>|<all_tables(*)>] 
to [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];

Example :

grant select on posts.* to [email protected] identified by 'mypassword';
flush privileges;
  • 'select' is permission name
  • 'posts' is the database name
  • '*' is used for all tables in the database
  • 'ramaswamy' is the user name
  • 'localhost' is the machine name, here same machine
  • 'mypassword' is the password

4. How to grant a user permission to create, insert, update, delete and create temporary tables from localhost.

Syntax :

grant [<permission_1,permission_2,...permission_n>|<all>] 
on [database].[<table_name>|<all_tables(*)>] 
to [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];

Example :

grant select, create, insert, update, delete, create temporary tables 
on posts.* to [email protected] identified by 'mypassword';
flush privileges;
  • 'select','create','insert','update','delete','create temporary tables' are the permission name
  • 'posts' is the database name
  • '*' is used for all tables in the database
  • 'amit' is the user name
  • 'localhost' is the machine name, here same machine
  • 'mypassword' is the password

5. How to grant a user permission to create, insert, update, delete and create temporary tables from any host.

Syntax :

grant [<permission_1,permission_2,...permission_n>|<all>] 
on [database].[<table_name>|<all_tables(*)>] 
to [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];

Example :

grant select, create, insert, update, delete, create temporary tables on posts.* 
to amit@'%' identified by 'mypassword';
flush privileges;
  • 'select','create','insert','update','delete','create temporary tables' are the permission name
  • 'posts' is the database name
  • '*' is used for all tables in the database
  • 'amit' is the user name
  • '%' is used for any host
  • 'mypassword' is the password

6. How to grant a user permission to select only from any host but to a specific table of a database.

Syntax :

grant [<permission_1,permission_2,...permission_n>|<all>] 
on [database].[<table_name>|<all_tables(*)>] 
to [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];

Example :

grant select on posts.url_master to jhon@'%' identified by 'mypassword';
flush privileges;
  • 'select' is the permission name
  • 'posts' is the database name
  • 'url_master' is the table name in the database 'posts'
  • 'jhon' is the user name
  • '%' is used for any host
  • 'mypassword' is the password

7. How to grant all privileges to a user from all machines.

Syntax :

grant [<permission_1,permission_2,...permission_n>|<all>] 
on [database].[<table_name>|<all_tables(*)>] 
to [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];

Example :

grant all on posts.* to joy@'%' identified by 'mypassword';
flush privileges;
  • 'all' is used for all the permission
  • 'posts' is the database name
  • '*' is used for all tables in the database
  • 'joy' is the user name
  • '%' is used for any host
  • 'mypassword' is the password

8. How to revoke all privileges from a user.

Syntax :

revoke [<permission_1,permission_2,...permission_n>|<all privileges>] 
on [database].[<table_name>|<all_tables(*)>] 
from [user name]@[<localhost>|<IP address>|<any host('%')>];

Example :

revoke all privileges on posts.* from joy@'%';
flush privileges;
  • 'all privileges' is used for all the permission
  • 'posts' is the database name
  • '*' is used for all tables in the database
  • 'joy' is the user name
  • '%' is used for any host

9. How to revoke specific privilege from a user.

Syntax :

revoke [<permission_1,permission_2,...permission_n>|<all privileges>] 
on [database].[<table_name>|<all_tables(*)>] 
from [user name]@[<localhost>|<IP address>|<any host('%')>];

Example :

revoke select on posts.* from joy@'%';
flush privileges;
  • 'select' is permission name
  • 'posts' is the database name
  • '*' is used for all tables in the database
  • 'joy' is the user name
  • '%' is used for any host

10. How to check permissions granted to a specific user.

Example :

show grants for amit;

11. How to check the list of system privileges that the MySQL server supports.

Example :

show privileges;

12. How to Grant permission to a user so that (s)he can execute not more than a specific number of queries in an hour.

Example :

create user [email protected] identified by 'mypassword';
grant select on posts.* to [email protected] 
identified by 'mypassword' with MAX_QUERIES_PER_HOUR 50;
flush privileges;

13. How to Grant permission to a user so that (s)he can execute not more than a specific number of queries in an hour.

Example :

set password for [email protected] = password('mypassword123');

14. How to delete user.

Example :

drop user steffi@'localhost';

15. How to rename a user.

Example :

rename user [email protected] to [email protected];

16. How to create a user and granting no privileges

Example :

grant usage on posts.* to [email protected] identified by 'mypassword';

Note : Command shown as solution is executed successfully on MySQL Server 5.6

Go to top



Join our Question Answer community to learn and share your programming knowledge.