CODEWITHSUNDEEP
mysqlphpmyadminprivileges
magdmartin
magdmartin

Reputation: 1787

phpmyadmin limit privileges for a specific database

I want my user to have extended privileges on all databases at the exception of one single database where the user should only have select statement right.

I have tried to limit to select only using database-specific option, but it has no effect and the user can still update the database. I suspect the extended privileges override my database-specific option.

I don't want to manage database-specific privileges for all databases as I have four users and 100+ databases.

Upvotes: 1

Views: 2623

Answers (2)

balleyne
balleyne

Reputation: 318

You can't exclude certain databases, AFAIK, but you can GRANT on a wildcard. So, if you name the one single database exception differently, say if the rest contain a prefix that that single one doesn't, you could accomplish something similar with the wildcard.

For example, if all the databases were prefixed with 'shared_' or 'common_' or something, you could GRANT all privileges for "common_%", but then control the permission of your one database without the prefix independently.

That's still involved, as you might have to rename a ton of databases, but then you only have two grants to manage, rather than one per database.

Upvotes: 1

Jesbus
Jesbus

Reputation: 815

I don't think there is a way to grant a MySQL user priviliges in the form of 'all except one'. However you could use a MySQL query to generate a list of GRANT-queries like this:

SELECT CONCAT('GRANT ALL ON ',SCHEMA_NAME,' TO user_name') FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME!='exclude_database'

and then execute those for every user.

Upvotes: 0

Related Questions