0

Is there a tool that makes the task of editing user privileges easier on a MySQL database? I've got nearly 100 tables to work through for 4 users, each with different per-table grants, which is getting tedious via phpMyAdmin. It'll get a lot more irritating when I make changes to the database structure and user list later on. I also can't see a way to do it in MySQL Workbench.

Are there any permissions management tools that make management of table permissions easier?

Update: For clarification, I need a tool that can manage the per-schema and per-table permissions for a user. I'll be changing permissions later, so it needs to be able to fully manage existing permissions as well as grant new ones. MySQL Workbench allows management of per-schema privileges, but not per-table.

Polynomial
  • 27,674
  • 12
  • 80
  • 107
  • If you have command line access, maybe the easiest thing for 100 tables would be to write scripts for the mysql program. Then you could use your favorite editor to make global changes to your script, then run the whole thing at once. – Marvo Jun 05 '12 at 19:38
  • 400 grants in a script doesn't sound very maintainable. – Polynomial Jun 05 '12 at 19:47

8 Answers8

2

Mysql gui tools . See here http://dev.mysql.com/downloads/gui-tools/5.0.html MySQL Administrator allows you to do all that.

biplav
  • 781
  • 6
  • 13
  • 1
    MySQL Workbench supports per-schema permissions, but not per-table permissions. – Polynomial Jun 05 '12 at 19:44
  • Did you try the downloads at biplav's link? MySQL Admin did support that. They are superseding MySQL Admin with Workbench, though you can still find MySQL Admin around, like at the link. It is part of the MySQL GUI Tools. – Michael K Oct 05 '12 at 16:12
1

I realize the question is a couple of years old but I came across it while looking for a solution so I suppose others might as well.

I came across SQL Yog and find it capable of doing all that I wanted. I use the community version so it is free to download and its User Manager has the capability to set permissions at the schema and table level.

James C
  • 21
  • 3
0

You may want to look at www.securich.com - its an open source user management tool for MySQL.

kmatyaszek
  • 19,016
  • 9
  • 60
  • 65
John D
  • 2,307
  • 17
  • 28
  • According to the documentation, it's a security plugin, not a management tool. I'm looking for a full permissions management tool. – Polynomial Jun 05 '12 at 19:46
0

Workbench is supposed to let you manage users and grant privileges -- see here. You can also use Navicat (not free) or if you are on a mac, Sequel Pro, which is free.

netfire
  • 158
  • 8
  • Workbench doesn't allow editing of per-table permissions. I'm not on a Mac, so Sequel Pro isn't any use. Navicat's rather steeply priced for this use, too. – Polynomial Jun 05 '12 at 19:43
0

MySQL Workbench provides this functionality.

  1. Open a connection via 'Server Administration'
  2. Click on 'Users and Privileges'
  3. Select the 'Schema Privilegs' tab
  4. Create entries for the schema you're working on, per your needs.

More information here.

Nathaniel Ford
  • 20,545
  • 20
  • 91
  • 102
0

Just looked at my install of Squirrel-SQL here. I've only got Oracle databases in this copy of it, but I use it for MySQL at home. Anyway, for Oracle, for tables, it has two tabs: Privileges, and Column Privileges. It may or may not provide similar functionality for MySQL. Give it a look perhaps. (I like it for other database work.)

Marvo
  • 17,845
  • 8
  • 50
  • 74
0

Try Security Manager tool in dbForge Studio for MySQL (the Security Manager is available in free Express Edition).

In new version multi-user editing is supported; you can select some users and grant or revoke Global and Object (database, tables, fields, ...) privileges, then apply changes or view them in SQL script.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Looks promising, but I get "Configuration system failed to initialise" on launch, when trying to connect to the db. Looks like the `.config` isn't in the right format. – Polynomial Jun 06 '12 at 12:14
  • Try to update .NET Framework on your computer. Otherwise, please write a request, specify your .NET Framework version - http://www.devart.com/dbforge/mysql/studio/support.html – Devart Jun 06 '12 at 12:35
  • Dropped the latest .NET Framework in, still having the same issue. I'll drop in a support ticket. – Polynomial Jun 06 '12 at 14:04
0

phpMyAdmin allows table-level privileges. More details can be found, for exemple, here and here.

kBytes
  • 1
  • 1