[asterisk-users] Ubuntu as remote MySQL server

[Tilghman Lesher]

On Sunday 25 May 2008 23:31:27 Ronald Wiplinger wrote:
> Tzafrir Cohen wrote:
> > On Mon, May 26, 2008 at 11:59:11AM +0800, Ronald Wiplinger wrote:
> >> I have replaced a SuSE server with an Ubuntu 8.04 server.
> >>
> >> The server runs MySQL, which is working locally. My Asterisk server
> >> (192.168.1.20) should access this MySQL server (192.168.1.254). I have
> >> left on the Asterisk server my old settings:
> >> [general]
> >> dbhost = 192.168.1.254
> >> dbname = myasterisk
> >> dbuser = myastuser
> >> dbpass = myastpass
> >> dbport = 3306
> >> dbsock = /var/lib/mysql/mysql.sock
> >>
> >> In MySQL I have setup a record with
> >>
> >> INSERT INTO `user` (`Host`, `User`, `Password`, `Select_priv`,
> >> `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`,
> >> `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`,
> >> `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`,
> >> `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`,
> >> `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`,
> >> `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`,
> >> `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`,
> >> `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`,
> >> `max_questions`, `max_updates`, `max_connections`,
> >> `max_user_connections`) VALUES
> >> ('192.168.1.20', 'root', '**********************', 'Y', 'Y', 'Y', 'Y',
> >> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
> >> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);
> >
> > And the reason you need to use such a priviliged user is?
> >
> > Use mysqladmin or whatever to administrate the database. The Asterisk
> > mysql user does *not* need to have full control. If someone manages to
> > find a clever way to inject SQL commands through your CDR data, thoey
> > should not be able to toast your whole database at that opportunity.
>
> Do you mean that too many privileges prevent to connect to the database?

This is actually a MySQL question.  Generally, although the user table does
control who can connect, it is advisable to use the GRANT command to set up
permissions, e.g.

GRANT select,insert,update ON asterisk.* to astuser at 192.168.1.20 IDENTIFIED BY
'somepassword';

There are various issues with just inserting a record, the main one of which
is that the permissions are cached in memory, so if you do update the table,
you need to "FLUSH TABLES;" before those changes will take effect.  Also, the
hash used for the password function differs in various versions.  A mismatch
will cause the database user to be unable to login.  And finally, if there are
existing permissions, you may get more or less than you bargained for, by
inserting records on your own.  Using the GRANT/REVOKE facility will ensure
that the resulting permissions exactly match what you specified in that
command, no more and no less.

-- 
Tilghman