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@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
_______________________________________________
Kamailio (SER) - Users Mailing List
[email protected]
https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
