Specific GRANT statements for new mysql users.

2 posts / 0 new
Last post
#1 Fri, 11/30/2007 - 03:21
TonyShadwick

Specific GRANT statements for new mysql users.

As you're aware, I use ndbcluster here. :)

I'm finally starting to finish up our database deployment, and there's just one last piece to the puzzle that needs cleared up...

We have multiple web servers, and multiple database servers. I have used mysql's built-in replication to replicate mysql.user to my non-virtualmin mysql servers. I add a user on my virtualmin box, it gets created on the other servers.

Whether I do it this way, or use Jamie's feature to create users on multiple servers, one issue remains...

Each user needs grant permissions for all of our web servers. By default, virtualmin only does so on it's host, and localhost.

I'd like to be able to say "when you create the mysql user, also grant on hosts x, y, and z". I can probably hack this in, but ideally I could grant specific permissions from multiple hosts at user creation time, and just run a script that hits all of the existing users.

For now I'll probably just put a cron job in that makes sure all of the users have grant from the web servers. Here's to hoping! :)

Fri, 11/30/2007 - 03:26
TonyShadwick

FYI, here's a good tutorial on getting the user table to replicate:

http://www.databasejournal.com/features/mysql/article.php/3355201

Here's the quick and dirty version. On the master:

GRANT REPLICATION SLAVE ON *.* TO slave_user IDENTIFIED BY 'slave_password';

In the master's my.cnf, under mysqld, add:
log-bin
server-id=1

On your slaves, in my.cnf under mysqld, add:
master-host=master-hostname
master-user=slave-user
master-password=slave-password
server-id=2
replicate-do-table=mysql.user
replicate-wild-ignore-table=*.*

Be sure to keep incrementing server-id. Restart mysql on all servers. Add a user or change a user permission on the master server, then on master and slave do:

select * from mysql.user;

The two should match. :)

Topic locked