SQL Exercises, Solution - SQL User Management

Last update on August 19 2022 21:51:00 (UTC/GMT +8 hours)

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 ramaswamy@localhost identified by "mypassword";

'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 "mypassword";

'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 ramaswamy@localhost 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 amit@localhost 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 steffi@localhost identified by 'mypassword';
grant select on posts.* to steffi@localhost 
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 steffi@localhost = password('mypassword123');

14. How to delete user.

Example :

drop user steffi@'localhost';

15. How to rename a user.

Example :

rename user amit@localhost to sumit@localhost;

16. How to create a user and granting no privileges

Example :

grant usage on posts.* to boris@localhost identified by 'mypassword';

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

Previous: Write a query to create a view that find the salesmen who issued orders on either August 17th, 2012 or October 10th, 2012.
Next: SQL Exercises, Practice, Solution - BASIC exercises on movie Database