[SR-Users] "kamdbctl create" failing with MySQL 8

Wed Nov 21 17:12:34 CET 2018

Thank you Henning :)

After looking at the output, I noticed it was ignoring $DBACCESSHOST, so I modified kamdbctl.mysql moving it up to the top as follows...

kamailio_db_grant () # pars: <database name>
{
	if [ $# -ne 1 ] ; then
		merr "kamailio_db_grant function takes one param"
		exit 1
	fi

	minfo "granting privileges to database $1 ..."

	# Users: kamailio is the regular user, kamailioro only for reading
	if [ ! -z "$DBACCESSHOST" ] ; then
		sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBRWPW';
			GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';"
		if [ $? -ne 0 ] ; then
			merr "granting access host privileges to database $1 failed!"
			exit 1
		fi
	elif [ "$DBHOST" != "localhost" ] ; then
		sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost' IDENTIFIED  BY '$DBRWPW';
			GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';"
		if [ $? -ne 0 ] ; then
			merr "granting localhost privileges to database $1 failed!"
			exit 1
		fi
	else
		sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
			GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST' IDENTIFIED BY '$DBROPW';"

		if [ $? -ne 0 ] ; then
			merr "granting privileges to database $1 failed!"
			exit 1
		fi
	fi

}

But, I'm still getting the same error.

## INFO: granting privileges to database kamailio ...
## + '[' '!' -z % ']'
## + sql_query '' 'GRANT ALL PRIVILEGES ON kamailio.* TO '\''_kamailio'\''@'\''%'\'' IDENTIFIED BY '\''<rw-password>'\'';
##                         GRANT SELECT ON kamailio.* TO '\''_kamailio-ro'\''@'\''%'\'' IDENTIFIED BY '\''<ro-password>'\'';'
## + '[' 2 -gt 1 ']'
## + '[' -n '' ']'
## + DB=
## + shift
## + '[' -n <admin-password> ']'
## + mysql -h <db-host> -P 3306 -u<user> -p<admin-password> -e 'GRANT ALL PRIVILEGES ON kamailio.* TO '\''_kamailio'\''@'\''%'\'' IDENTIFIED BY '\''<rw-password>'\'';
##                         GRANT SELECT ON kamailio.* TO '\''_kamailio-ro'\''@'\''%'\'' IDENTIFIED BY '\''<ro-password>'\'';'
## mysql: [Warning] Using a password on the command line interface can be insecure.
## ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '<rw-password>'' at line 1
## + '[' 1 -ne 0 ']'
## + merr 'granting access host privileges to database kamailio failed!'
## + '[' -t 1 -a -z '' ']'
## + echo -e '\E[37;31m\033[1mERROR: granting access host privileges to database kamailio failed!\033[0m'

I loaded the query into Workbench and it told me "'_kamailio' is not valid at this position for this server version, expecting an identifier".
That lead me to read up on the correct syntax for GRANT and apparently you can't have "IDENTIFIED BY" in the GRANT query with MySQL 8. The account must be created first.

Here's my changes to kamdbctl.mysql that are now working...

kamailio_db_create () # pars: <database name>
{
	if [ $# -ne 1 ] ; then
		merr "kamailio_db_create function takes one param"
		exit 1
	fi

	if [ "$CHARSET" = "" ]; then
		minfo "test server charset"
		db_charset_test
	fi

	minfo "creating database $1 and users $DBRWUSER, $DBROUSER ..."

	if [ ! -z "$DBACCESSHOST" ] ; then
		sql_query "" "CREATE DATABASE $1 CHARACTER SET $CHARSET;
			CREATE USER '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBRWPW';
			CREATE USER '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';
			FLUSH PRIVILEGES;"
		if [ $? -ne 0 ] ; then
			merr "Creating database $1 on access host failed!"
			exit 1
		fi
	elif [ "$DBHOST" != "localhost" ] ; then
		sql_query "" "CREATE DATABASE $1 CHARACTER SET $CHARSET;
			CREATE USER '$DBRWUSER'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
			CREATE USER '$DBROUSER'@'$DBHOST' IDENTIFIED BY '$DBROPW';
			FLUSH PRIVILEGES;"
		if [ $? -ne 0 ] ; then
			merr "Creating database $1 on localhost failed!"
			exit 1
		fi
	else
		sql_query "" "CREATE DATABASE $1 CHARACTER SET $CHARSET;
			CREATE USER '$DBRWUSER'@'localhost' IDENTIFIED BY '$DBRWPW';
			CREATE USER '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';
			FLUSH PRIVILEGES;"
		if [ $? -ne 0 ] ; then
			merr "Creating database $1 failed!"
			exit 1
		fi
	fi
}

kamailio_db_grant () # pars: <database name>
{
	if [ $# -ne 1 ] ; then
		merr "kamailio_db_grant function takes one param"
		exit 1
	fi

	minfo "granting privileges to database $1 ..."

	# Users: kamailio is the regular user, kamailioro only for reading
	if [ ! -z "$DBACCESSHOST" ] ; then
		sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST';
			GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST';
			FLUSH PRIVILEGES;"
		if [ $? -ne 0 ] ; then
			merr "granting access host privileges to database $1 failed!"
			exit 1
		fi
	elif [ "$DBHOST" != "localhost" ] ; then
		sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost';
			GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost';
			FLUSH PRIVILEGES;"
		if [ $? -ne 0 ] ; then
			merr "granting localhost privileges to database $1 failed!"
			exit 1
		fi
	else
		sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST';
			GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBHOST';
			FLUSH PRIVILEGES;"
		if [ $? -ne 0 ] ; then
			merr "granting privileges to database $1 failed!"
			exit 1
		fi
	fi

}

Am Mittwoch, 21. November 2018, 03:16:04 CET schrieb Jonathan Tyler:
> I just had the same thing happen to me. I installed Kamailio via Apt, and
> I'm using a dedicated MySQL server, with non-root admin credentials.
> Previously today I installed it using a local MySQL 5.7 database with no
> issues.
> 
> Kamailio Host: Kamailio 5.1.2 (Ubuntu 18.04)
> MySQL Host: MySQL 8.0.13 (Windows Server 2012 R2)
> 
> It creates the database, then fails at granting permissions. Accounts were
> created and privileges flushed before executing.
> > root at kamailio:~# kamdbctl create
> > INFO: creating database kamailio ...
> > mysql: [Warning] Using a password on the command line interface can be
> > insecure. INFO: granting privileges to database kamailio ...
> > mysql: [Warning] Using a password on the command line interface can be
> > insecure. ERROR 1064 (42000) at line 1: You have an error in your SQL
> > syntax; check the manual that corresponds to your MySQL server version
> > for the right syntax to use near 'IDENTIFIED BY '<password>'' at line 1
> I turned on query logging on the database host but didn't see any errors
> there, or in the error log itself.
> > 2018-11-21T01:58:15.045586Z 8 Connect <user>@kamailio on using SSL/TLS
> > 2018-11-21T01:58:15.046239Z 8 Query select @@version_comment limit 1
> > 2018-11-21T01:58:15.046764Z 8 Query CREATE DATABASE kamailio CHARACTER SET
> > utf8mb4 2018-11-21T01:58:15.189109Z 8 Quit
> > 2018-11-21T01:58:15.208476Z 9 Connect <user>@kamailio on using SSL/TLS
> > 2018-11-21T01:58:15.209019Z 9 Query select @@version_comment limit 1
> > 2018-11-21T01:58:15.210114Z 9 Quit
> 
> I've tried with the default MySQL 5.7 Client on Ubuntu, and upgrading it to
> 8.0.13, but got the same result.
> 
> Unfortunately I don't know how to see the output of the SQL Statement being
> sent (without installing 5.7), or I'd try to run it manually.

Hello Jonathan,

just an idea on how to debug this further:

Try to execute the kamdbctl with bash -x, like this "bash -x kamdbctl" (adding 
the correct path). Then it should print all the executed commands.

Best regards,

Henning

-- 
Henning Westerholt - https://skalatan.de/blog/
Kamailio services - https://skalatan.de/services
Kamailio security assessment - https://skalatan.de/de/assessment