CODEWITHSUNDEEP
mysql
nick
nick

Reputation: 2783

Where does MySQL store "database-specific" privileges?

I'm assuming it's in the database folder. But where.. WHERE could it be?

The problem was solved by running FLUSH PRIVILEGES. I copied over the mysql folder but did not restart the server.

Upvotes: 1

Views: 5144

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

Run SHOW GRANTS (or its more specific variants like SHOW GRANTS FOR 'user'@'host' or SHOW GRANTS FOR CURRENT_USER) to get privilege information.

You can also get information from mysql database as shown in julumme's answer if you wish to get result as an SQL result set.

Upvotes: 0

julumme
julumme

Reputation: 2366

They are normally stored to "mysql" database.

As http://dev.mysql.com/doc/refman/5.5/en//grant-table-structure.html shows, the table is called "db"

mysql> use mysql;
mysql> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

Granting access to a specific database, use:

GRANT SELECT ON database.* TO user@'localhost';
GRANT SELECT ON database.* TO user@'localhost' IDENTIFIED BY 'password';

Upvotes: 1

Related Questions