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
Prepending a character to all values in a column
I needed to prepend a "/" value to a directory field in a MySQL table named
files for every entry in the table. With a column named
Directory in the table, that can be done using the
UPDATE command. E.g.:
UPDATE fles SET Directory = Concat('/', Directory);
[/software/database/mysql]
permanent link
Creating a business category in Quicken 2014
I wanted to create a new business expense category in Quicken 2014 named
Hardware. But when I created a new expense category, the category
always defaulted to be a personal expense. I could not locate a means to
specify that I wanted the new category to be in the business rather than
personal group and I didn't see any way to change the classification to
business after the category was created. I could put the new business expense
category as a subcategory beneath an existing business category, but that is
not what I wanted to do. There is a way to put the new category as a top-level
category in the business group rather than have it be a subcategory, though,
by first creating it as a subcategory and then changing it.
- Click on Tools.
- Select Category List.
- Click on the Add Category button.
- Type a name for the new category in the Category Name field.
- Select the Subcategory of radio button and pick a business
category, such as "Supplies (Business)". Add a description if you like.
- Click on the Tax Reporting tab and make any changes, if any
are needed, there.
- Click on OK.
- Right-click on the new category in the category list, then select the
Expense radio button, rather than the Subcategory of one.
- Click on OK.
- Click on Done to close the Category List window.
The category will remain as a business category after the change.
[/financial]
permanent link
