Showing permissions for MySQL databases
Any of the following commands can be used to view privileges on MySQL databases for the currently logged in user:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
E.g., if logged into MySQL as the user joe:
mysql> show grants;
+----------------------------------------------------------------------------------------------------+
| Grants for joe@localhost |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'joe'@'localhost' IDENTIFIED BY PASSWORD '75ac044c66d44642' |
| GRANT ALL PRIVILEGES ON `family`.* TO 'joe'@'localhost' |
| GRANT ALL PRIVILEGES ON `partslist`.* TO 'joe'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `tokyo`.`tokyo` TO 'joe'@'localhost' |
+----------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
To show databases for which the user has access, the show databases
command can be used:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| family |
| partslist |
| tokyo |
+--------------------+
4 rows in set (0.00 sec)
To see which users have access to a particular database, you use the
command select user from mysql.db where db='dbname';, where
dbname is the name of the relevant database, if
you are logged into an account with administrator privileges, e.g., as root.
E.g. for a database named partslist:
mysql> select user from mysql.db where db='partslist';
+------+
| user |
+------+
| joe |
+------+
1 row in set (0.01 sec)
[/software/database/mysql]
permanent link
Dropping tables with a similar prefix from a MySQL database
I wanted to remove all of the tables from a MySQL database where the tables
all had names starting with a similar prefix. Rather than deleting the tables
one by one, I wanted to reduce the time and typing on my part to drop all
of the tables. One way to do so is to use the
mysqldump command
to create a file that contains all of the needed
DROP commands.
You can issue a mysqldump command similar to the following where
user
is a user name with permissions to access the database and
database
is the name of the database:
mysqldump -u user -p database > dump.sql
The -p option will prompt you for the password for the user.
E.g., supposing the user name is jdoe and the database is named jdoe_db:
$ mysqldump -u jdoe -p jdoe_db > dump.sql
Enter password:
Then, on a Unix/Linux/ system, you can use the grep command
to search for lines in the dump.sql file produced by the
mysqldump command for "DROP TABLE IF EXISTS" and then pipe
that output into another grep command that searches only for lines containing
the particular string in the table name that you want to key on for dropping
tables. E.g., suppose you want to drop all tables that have example
as part of the table name:
$ grep "DROP TABLE IF EXISTS" dump.sql | grep example > drop.sql
You can check the drop.sql file output by the above commands to
verify that only the particular tables you wish to drop will be removed from
the database.
$ more drop.sql
DROP TABLE IF EXISTS `example_settings`;
DROP TABLE IF EXISTS `example_smileys`;
DROP TABLE IF EXISTS `example_spiders`;
If you are satisfied that only the tables you want removed from the database
will be deleted from the database, you can then issue the command:
mysql -u user -p database < drop.sql
E.g., for the example above, you could use:
$ mysql -u jdoe -p joe_db < drop.sql
Enter password:
The dump.sql and drop.sql files can then
be deleted.
[/software/database/mysql]
permanent link
