3

We have several core db tables on our system and allow our employees access to these tables via views that just show them the data they need to see.

We created these views in a new db specific to the new user. Creating all of these views requires running several mysql queries.

I would like to automate this process into a script, but feel there must be tools already out there that take a list of queries and run them.

Can anyone recommend any such tools or even any built-in features in mysql that could assist me here ?

Thanks!

  • 4
    "there must be tools already out there that take a list of queries and run them" You mean.. Like a script? – Benoit Mar 22 '11 at 14:35
6

Create a text file containing all your commands. Call it newuser.sql. Then when you want to set up a new user, let's call him gaius, you would simply do

$ cat newuser.sql|mysql --user=gaius --password=abc123 somedb

Obviously you could even create new users in a script, let's call it newuser.sh

#!/bin/bash

echo "create user '$1'@'%' identified by password('$2');"|mysql --user=root --password=secret
cat newuser.sql|mysql --user=$1 --password=$2 somedb

Then do

$ ./newuser.sh gaius abc123
0

Try cron jobs, also known as crontab, cron etc etc.

0

Creating a view is nothing more than

create db.view as select ...

What do you want to automate here?

0

Stored procedures

Perl

PHP (especially handy from web page)

GRANT access to each user down at the column level. (As an alternative to VIEWs.)

0

Are you referring to a GUI tool, such as MySQL Workbench? It will allow you to run multiple MySQL statements.

Other GUI tools are:
DbVisualizer
PhpMyAdmin

Not the answer you're looking for? Browse other questions tagged or ask your own question.