Activities

July 2011
M T W T F S S
« Jun   Aug »
 123
45678910
11121314151617
18192021222324
25262728293031

MySQL : Most commonly used permission set

Here I’m providing certain mysql permission set required for most of the time

a. Create a user and grant all the access to that database

    mysql> create database demo;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on demo.* to demouser@localhost identified by 'demopass';
Query OK, 0 rows affected (0.00 sec)

b. Permitting a user to create Views and routine ( most user level privileges)

mysql> grant select, insert, update, delete, create, drop, index, alter, create temporary tables, lock tables ,CREATE VIEW,CREATE ROUTINE,REFERENCES, EXECUTE, ALTER ROUTINE  on demo.* to demouser@localhost identified by 'demopass';
Query OK, 0 rows affected (0.00 sec)

c. Granting mysql remote access to a user on a particular ip

 mysql> grant all on demo.* to demouser@192.168.10.19 identified by 'demopass';
Query OK, 0 rows affected (0.00 sec)

d. Removing a user

 mysql> drop user  demouser@localhost ;
Query OK, 0 rows affected (0.00 sec)

Pls note if you are granting this user to a network access, you may need to identify ip you given and use the “drop user” statement accordingly.

Sometimes PMA doesn’t allow you to login even you are granting local access (localhost) to user. In that case, you may need to grand the system IP to access the database and configure system ip in the place of “localhost” in config.inc.php file.

e. Creating a read only user.

  mysql> grant select on demo.* to readonlyuser@localhost identified by 'pass';
Query OK, 0 rows affected (0.00 sec)

f. Creating a mySQL slave user with replication privileges

  mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'pass';

g. How do I enable General logs in mysql
it is a very rare scenario to check the mysql sql logs to identify the sql execution oftently happend and optimiszing the code accordingly. Most of the case certain querries are talking too much time and waiting to get execution completed from the mysql engine. These sql querries are still running in memeory which may affect the mysql performance badly. We can identify such query easily by adding the following entries in my.cnf file.

 long_query_time         = 1
log-slow-queries        = /var/log/mysql/mysql-slow.log

If we need to view all the sql scripts executing by the MySQL engine,
a. You may add log=/var/log/mysql.general.log entry in “etc/my.cnf” file.
b. Restart the mysql service
c. View the run-time output by

 # tail -f /var/log/mysql.general.log

Pls note that this file size was rapidly increase since all the sql executing is stored on this file as well as de-grade the MySQL server performance the same time. So do not forget to turn off this feature immediately after the verification.

In my.cnf added the following lines
log=/var/log/mysql.general.log entry

Then execute the following commands

#touch /var/log/mysql.general.log
#chown mysql.mysql /var/log/mysql.general.log
#service mysql restart
#tail -f /var/log/mysql.general.log

h. How do I view the users permissions list.
This command is a life saver to identify of any of the users have certain privilege assigned or not.

You may use ” Grants for user@serverip” to view the permissions. The following will be the perfect user permission set. This user can ‘t use sensitive command like drop,create statement.

mysql> show grants for  produsr@192.168.10.12;
+-------------------------------------------------------------------------------------------------------+
| Grants for produsr@192.168.10.12                                                                 |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'produsr'@'192.168.10.12' IDENTIFIED BY PASSWORD '226d742c'        |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `DB1`.* TO 'produsr'@'192.168.10.12'      |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, LOCK TABLES ON `DB2`.* TO 'produsr'@'192.168.10.12' |
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>

Privilege Used for
1. ALL [PRIVILEGES] Sets all simple privileges except GRANT OPTION
2. ALTER Enables use of ALTER TABLE or Database
3. CREATE Enables use of CREATE TABLE or Database
4. CREATE TEMPORARY TABLES Enables use of CREATE TEMPORARY TABLE for Views , trigger and stored procedure
5. DELETE Enables use of DELETE
6. DROP Enables use of DROP TABLE
7. EXECUTE Privilege to create store procedure and executing view, trigger querries
8. FILE Enables use of SELECT … INTO OUTFILE and LOAD DATA INFILE
9. INDEX Enables use of CREATE INDEX and DROP INDEX
10. INSERT Enables use of INSERT
11. LOCK TABLES Enables use of LOCK TABLES on tables for which you have the SELECT privilege
12. PROCESS Enables the user to see all processes with
13. SHOW PROCESSLIST Listing server process list
14. RELOAD Enables use of FLUSH
15. REPLICATION SLAVE Needed for replication slaves (to read binary log events from the master)
15. SELECT Enables use of SELECT
16. SHOW DATABASES SHOW DATABASES shows all databases
17. SHUTDOWN Enables use of MySQLadmin shutdown
18. SUPER Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the MySQLadmin debug command; allows you to connect (once) even if max_connections is reached
19. UPDATE Enables use of UPDATE
21. USAGE Synonym for privileges
22. GRANT OPTION Enables privileges to be granted

i. Permission set for MySQL Backup user
I see most of us are using privillage user account to backup the database. Database should be compromised of one can access your script file and can change the tables using the hacked credentials. So we may use a limited privilege given for backup user. Only SELECT and LOCK Table privileges required for this purpose

 #mysql >GRANT LOCK TABLES, SELECT,SHOW VIEW ON db1.* TO  db1user@192.168.0.12 IDENTIFIED BY 'password';

j. Mysql replication Admin privileges
This user can execute all the admin jobs like start/stop the slave, execute the binary log, change the binary log position etc.

#mysql>GRANT REPLICATION CLIENT,REPLICATION SLAVE,SELECT, FILE, PROCESS, SUPER RELOAD ON *.* TO repliadmin@192.168.10.12   identified by 'pasword';

k. MySQL Replication user privileges.

For MySQL 5.1.x version
Most of the RedHat/CentOS platform has MySQL 5.1 stable version. The basic difference in case of replication in between MySQl 5.1 and MySQL 5.5 is , latest version does not support MySQL master information configured in my.cnf file, We may need to provide it as sql to MySQL Master.

#mysql>GRANT REPLICATION SLAVE ON *.* TO replicauser@192.168.0.12 IDENTIFIED BY 'password';

For MySQL 5.5.x Version

CHANGE MASTER TO MASTER_HOST='192.168.10.17', MASTER_USER='replicauser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001',MASTER_CONNECT_RETRY=60,MASTER_PORT=3306,MASTER_LOG_POS=98

l. How do I revoke user permission from a database.

#revoke all on dbname.* from user@hostname;

mysql> revoke all on poe_sessions.* from 'chinook'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>