Task:

Grant SELECT access on tables in vos3000db schema except a few tables (which contains user credentials  and security information) to another newly created MySQL user which will be used by billing department. Connection to MySQL server will only be allowed from billing application server. Each day a new table is creating in MySQL vos3000db schema.

Workaround:

• Server Environment: Centos 5, MySQL 5.2
• Client Environment: Windows server 2008
• Resolution plan:
  1. Connect to MySQL server
  2. Create a new MySQL user.
  3. Initially Grant only SELECT permission to the existing vos3000db schema user tables except the excluded ones to `billing`@`192.168.*.*` user.
  4. Generate grant scripts that will grant the newly created tables to the new MySQL user and save into a .sql file in the home directory of the root user.
  5. Create a shell script file that will execute the above grant scripts file.
  6. Create an entry into the cron job file to schedule the script execution.

• Execution plan:
  1. Connect to MySQL server by MySQL “root” user.
  2. Create a new user named as “billing” with allowing it to connect only from the client server ip(192.168.*.*)

     • CREATE USER `billing`@`192.168.*.*` IDENTIFIED BY 'pass' REQUIRE NONE;

  3. Initially Grant only SELECT permission to the `billing`@`192.168.*.*` user on vos3000db schema user tables except the excluded one named as ‘e_user’:

     • SELECT concat("GRANT SELECT ON vos3000db.",TABLE_NAME," TO billing@192.168.*.*;")
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA='vos3000db' 
       AND TABLE_NAME NOT IN('e_user'); //IN used so that multiple tables could be included in exclude list

     • execute the above script output in toad or another client for MySQL.
  4. Create a file named as base_script.sql under the  root user home directory “/home/root” and paste the below content:

     • SELECT concat("GRANT SELECT ON vos3000db.",TABLE_NAME," TO billing@192.168.*.*;")
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA='vos3000db' 
       AND CREATE_TIME BETWEEN SYSDATE()-INTERVAL 1 DAY and SYSDATE()
       AND TABLE_NAME NOT IN('e_user')

     • Above script will generate grant statements of newly created tables within the mentioned range to the user `billing`@`192.168.*.*` .
  5. Create a shell script file named as exec_script.sh and paste the below content:

     • mysql information_schema -u root –proot_password -N < /home/root/base_script.sql > /home/root/grant_script.sql

     • When edit the root password make sure you add the leading p with no space.
     • Above script will connect to MySQL server and will execute the base_script.sql storing the output scripts in file named as grant_script.sql and finally executing the grant_script.sql to grant.
  6. Edit the cron job file to make an entry of exec_script.sh execution at everyday 00:01 hour:
     • From o/s shell execute the below command under root user or any o/s user:

     • crontab -e

     • 0 01 * * sun,mon,tue,wed,thu,fri,sat  sh /home/root/exec_script.sh

  7. Finally create another script named as exec_grant.sh and make an entry in “crontab” which will execute the GRANT commands contained in the above grant_script.sql file (Step:5) connected through MySQL.
     • Create the exec_grant.sh file and paste the below command.

     • mysql information_schema -u root –proot_password -N < /home/root/grant_script.sql

     • Make an entry in the crontab; note to make it executable after the exec_script.sh; otherwise it will have the old contents of grant scripts.

     • 0 05 * * sun,mon,tue,wed,thu,fri,sat  sh /home/root/exec_grant.sh