Jun 24th, 2014 at 06:28 PM

Question and answer on Securing MySQL

01. Create a user name jone@localhost with the following privilege : all privilege mysql> grant all privileges on test.* to jone@localhost; Query OK, 0 rows affected (0.00 sec) 02.Set new password to jone@localhost like demo mysql> set password for jone@localhost=password('kasem'); Query OK, 0 rows affected (0.00 sec) 03.Rename user jone@localhost to jony@localhost mysql> rename user jone@localhost to jony@localhost; Query OK, 0 rows affected (0.00 sec) 04. Revoke update privilege from jony@localhost mysql> revoke update on test.* from jony@localhost; Query OK, 0 rows affected (0.00 sec) 05. Show list of all grants for the jony@localhost mysql> show grants for jony@localhost G 06. Revoke select privilege from table for jony@localhost mysql> revoke select on test.* from jony@localhost; Query OK, 0 rows affected (0.00 sec) 07. Create a user with require SSL mysql> grant insert, select, update on test.* to kasem@localhost identified by 'kasem' require ssl; Query OK, 0 rows affected (0.00 sec) 08. Show user list for a specific database; mysql> select * from db where db='test' G 09. Write nine GRANT and REVOKE Tips GRANT and REVOKE Tips The following list offers various tips to keep in mind when you’re working with GRANT and REVOKE: • You can grant privileges for a database that doesn’t yet exist. • If the user identified by the GRANT command does not exist, it will be created. • If you create a user without including the IDENTIFIED BY clause, no password will be required for login. • If an existing user is granted new privileges, and the GRANT command is accompanied by an IDENTIFIED BYclause, the user’s old password will be replaced with the new one. • Table-level GRANTs only support the following privilege types: ALTER, CREATE, CREATE VIEW, DELETE, DROP, GRANT, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, and UPDATE. • Column-level GRANTs only support the following privilege types: INSERT, SELECT, and UPDATE. CHAPTER 29 SECURING MYSQL 581 • The _and %wildcards are supported when referencing both database names and host names in GRANT commands. Because the _character is also valid in a MySQL database name, you need to escape it with a backslash if it’s required in the GRANT. • If you want to create and delete users,and are running MySQL 5.0.2 or greater, consider using the CREATE USER and DROP USER commands instead. • You can’t reference *.*in an effort to remove a user’s privileges for all databases. Rather, each must be explicitly referenced by a separate REVOKE command. 10. Write 5 grant option ALL PRIVILEGES Affects all privileges except WITH GRANT OPTION ALTER Affects the use of the ALTER TABLE command ALTER ROUTINE Affects the ability to alter and drop stored routines CREATE Affects the use of the CREATE TABLE command CREATE ROUTINE Affects the ability to create stored routines 11. Write 6 SSL option --ssl, --ssl-ca, --ssl-capath, --ss-cipher, --ssl-key 12. Write all option of MySQL client --host=name or -h:, --password[=name] or -p: , --pipe or -W: , --port=port_num or -P:, --socket=/path/to/socket or -s:, --user=name or -u: 13. Write query for limit user connection at a time. mysql> grant insert, select, update on test.* to kasem@localhost identified by 'kasem' with max_connections_per_hour 3600; Query OK, 0 rows affected (0.00 sec) 14. Write a query for column level privilege. mysql> grant update (name) on test.user to jony@localhost; Query OK, 0 rows affected (0.01 sec) 15. Change root user password using all alternate methods. Method-1 : C:>mysql -u root mysql mysql>SET PASSWORD FOR root@localhost=PASSWORD('secret'); mysql>FLUSH PRIVILEGES; Method-2 : C:>mysqladmin -u root password ‘secret’ Method-3 : Mysql:>use mysql mysql> update user set password=password('123') where user='kasem'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0