We had both a live site and a corresponding development site using the same MySQL database server. On the live site, carrying out a particular user task worked fine, but doing the same task on the development site would fail with the message "DB Error: unknown error".
The problem was eventually traced back to the fact that the contents of the database for the development site had been loaded by importing a backup of the live site.
The live site contained several database functions. For example, here's one of the exported function definitions.
CREATE DEFINER=`user1`@`localhost`
FUNCTION `clean_string`(in_str varchar(4096))
RETURNS varchar(4096) CHARSET latin1
BEGIN
/**
* Function will strip all non-ASCII and unwanted ASCII characters in string
*
* @author Shay Anderson 10.11
*
* @param VARCHAR in_arg
* @return VARCHAR
*/
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(255) DEFAULT '';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( in_str );
REPEAT
BEGIN
SET c = MID( in_str, i, 1 );
IF c REGEXP '[[:alnum:]]' THEN
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END
Luis noticed that the definitions of this database function as well as others included the clause DEFINER = 'user1'@'localhost'.
By default, the SQL SECURITY characteristic of a function is DEFINER. That means that when the routine is executed, it does so within the security context of the user specified as the DEFINER.
This worked fine for the live site because its database runs under user user1.
However, for the staging site, that user does not have access to the staging database, so it was denied. That is, the user did not have sufficient permissions for executing the function body as applied to the other database.
Deleting the DEFINER clause and reloading the function confirmed that the clause was the problem.
Also, a key factor was that both databases are managed by the same db server, within which user1 exists. If the function definition were imported into a different db server for which user1 does not exist, it could be that the problem would not occur.
Further research uncovered this advice:
"For a stored routine or view, use SQL SECURITY INVOKER in the object definition when possible so that it can be used only by users with permissions appropriate for the operations performed by the object. ".
In other words, set the SQL SECURITY characteristic to INVOKER so that the security context of whichever user invokes the function is in effect. This seems ideal.
So we modified the beginning part of the function definition as follows:
CREATE
FUNCTION `clean_string`(in_str varchar(4096))
RETURNS varchar(4096) CHARSET latin1
SQL SECURITY INVOKER
BEGIN
....
END
(Incidentally, the error message "DB Error: unknown error" is apparently output by the Drupal 7 CiviCRM module.)
Sources:
Access Control for Stored Programs and Views
https://dev.mysql.com/doc/refman/5.7/en/stored-programs-security.html
