ERROR 1356 (HY000)

The most important thing to understand when coming upon an error is to take a moment and relax. 
Sometimes people run across an error and become flustered and frustrated.  Take a deep breath and relax. Sometimes "Madness is like gravity. All that needs is a little push" and “Sometimes the questions are complicated and the answers are simple.”

What is all this about? Well for example I recently was asked to help solve a problem that was troubling another DBA. Nothing meant in disrespect at all to that DBA but being in a rush people just overlook the answer that is in front of them and instead spiral down into madness. 

Here is the general concept of the situation. 

After doing a mysqldump of a database the user moved it to another database for others to being testing. The import worked just fine. However, the users began to see errors when they tried to query from a prebuilt view. 

mysql> select * from <View_name> limit 1; 

ERROR 1356 (HY000): View '<DB_NAME>.<ViewName>' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 

The first reaction was to assume that the something went wrong with the import. Did the view or actually the related tables become corrupt? 

No. 

To solve this error the first thing todo was to:    mysql> show create view <View_Name> \G 

You likely will see something that begins like this:
*************************** 1. row *************************** 
View: Visitors 

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`<Some_user>`@`<Some_host_or_IP>` SQL SECURITY DEFINER VIEW

After you see who  the definer is : `<Some_user>`@`<Some_host_or_IP>`   you can view the grants for that user. 

mysql> show grants for `<Some_user>`@`<Some_host_or_IP>`; 

It turns out the the solution was just what the error said in the first place:  "definer/invoker of view lack rights to use them"

To make it easy you can do a show grants in the database where it already works and copy the grant statement, or you can review what type of permissions you need to implement in the new database to enable the view to gather the required data. 

A simple update of permissions for the user and the error was gone.  

“Sometimes the answers are simple.”

MySQL Users :: Grants :: mysql_config_editor :: Security

Secure access to the database is likely priority number one for any database administrator. If it is not then you need to seriously look into why it is not.

General guidelines via the manual are already available:

• http://dev.mysql.com/doc/refman/5.6/en/password-security.html
• http://dev.mysql.com/doc/refman/5.6/en/password-security-user.html
• http://dev.mysql.com/doc/refman/5.6/en/create-user.html
• http://dev.mysql.com/doc/refman/5.6/en/grant.html
• http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

One of the primary issues with security in MySQL is of course the permissions that you give users.
These are a few simply guidelines.

First keep "super user" or "root" accounts to a minimum. A user with full access or "GRANT ALL" will still have access when you have reached your max connections. So the last thing you would want is a program to be executing commands with a user with full access.

Keep in mind what types of accounts you are creating. You can limit a user to MAX QUERIES, MAX UPDATES, MAX CONNECTIONS and MAX USER CONNECTIONS per HOUR.

Keep in mind of the network environment your users are connecting from. If users are going to be using DHCP network addresses within the same subnet you would just be creating more work for yourself if you limited them to a single IP. You can still limit them to the subnet though with a wildcard. For example '192.168.0.2' versus '192.168.0.%'

Stay away from entire wildcard access for host and users.



> CREATE USER ''@'192.168.0.56' ;
Query OK, 0 rows affected (0.02 sec)

> show grants for ''@'192.168.0.56';
+-----------------------------------------+
| Grants for @192.168.0.56                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO ''@'192.168.0.56' |
+-----------------------------------------+



This would leave you wide open for anyone from 192.168.0.56 and is not a smart secure thing to do.
It could also violate other accounts from 192.168.0.56 because MySQL checks host first and username second.


> GRANT SELECT ON test.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY 'somepassword';

> show grants for 'exampleuser'@'192.168.0.%';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@192.168.0.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT ON `test`.* TO 'exampleuser'@'192.168.0.%'                                                              |
+----------------------------------------------------------------------------------------------------------------------+



This will allow selects only for exampleuser from '192.168.0.%'. You must also keep in mind that if exampleuser is connecting from LOCAL HOST that the system will likely user localhost first before the 192.168.0.% subnet address unless the user used the subnet address the host to connect to.

This means that you can create one user and password with different privileges per host.



> SHOW GRANTS FOR 'exampleuser'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'localhost' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT, UPDATE, DELETE ON `test`.* TO 'exampleuser'@'localhost'                                              |
+--------------------------------------------------------------------------------------------------------------------+



Try your best to not use the --password=<password> option via the mysql client. You can use -p to prompt for a password.

You also have the option with MySQL 5.6 to use the MySQL Configuration Utility.


# mysql_config_editor set  --login-path=local --host=localhost --user=root --password
Enter password:







# mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost





# mysql 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# mysql  --login-path=local

Welcome to the MySQL monitor.

# mysql  --login-path=local -e 'SELECT NOW()';

You do have options to name different paths like local or remote and etc as well. So you can encrypt more than one access user account in your ~/.mylogin.cnf file that is created once you use the set command.

If you have shell scripts that use the mysql client and likely then have passwords in the scripts updating them to use the "--login-path=" is a more secure way to go.


Of course when you no longer need a user... Drop the user.


> DROP USER 'exampleuser'@'localhost';