SQL Exercises, Solution - SQL User Management

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 "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 [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 [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
• '%' 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 [email protected]'%' 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 [email protected]'%' 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 [email protected]'%';
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 [email protected]'%';
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 [email protected]'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

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