Modern MySQL++, MySQL++ v3.2.3

TangentSoft

Forks

… of unknown currency or quality.

Abandoned

Alternatives

MySQL++ v3.1.0 does not support SSL, but v3.2.2 does

Problem Statement

  • Contact a MySQL; c.f. MariaDB v5.5
  • Use mysql++ for C++
    mysql++-3.1.0-13.fc20.x86_64
  • Use SSL for client auth
  • Fedora 20

Indications

  • mysql++ is not built with SSL support.

Diagnosis

  • MySQL++ SSL Support on Linux; some dude using the self-asserted identity token Ryan; On Stack Overflow; 2011-10-17.
    tl;dr => The MySQL v3.1.0 configure fails to find mysql_ssl_set; the bug is fixed in MySQL++ 3.2.0, (claimed) to have been released 2011-10.

Background

Evidences

$ g++ -std=c++11 -o mysqlpp-ssl mysqlpp-ssl.cpp -DMYSQLPP_MYSQL_HEADERS_BURIED $(mysql_config --cflags --libs) -lmysqlpp
$ ./mysq2lpp-ssl
./mysq2lpp-ssl: error, bad option 'Option not supported by database driver v5.5.41-MariaDB'
#include "mysql++/mysql++.h"
#include <iostream>

namespace {
  char const *const NAME = "mysq2lpp-ssl";
}
auto main(int argc, char *argv[]) -> int {
  try {
    char const *const database = "mysql";
    char const *const hostname = "db.example.com";
    char const *const username = "wbaker";
    char const *const password = "password";
    char const *const key = "/etc/pki/myclient/key.pem";
    char const *const cert = "/etc/pki/myclient/cert.pem";
    char const *const ca = nullptr;
    char const *const capath = "/etc/pki/myclient/ca";
    char const *const cipher = nullptr;
    mysqlpp::Connection conn;
    conn.set_option(new mysqlpp::SslOption(key, cert, ca, capath, cipher));
    if ( !conn.connect(database, hostname, username, password) || !conn.connected()) {
      throw mysqlpp::ConnectionFailed("because");
    }
    std::cout << "OK!\n";
    return 0;
  } catch (mysqlpp::BadOption const &e) {
    std::cerr << NAME << ": error, bad option '" << e.what() << "'\n";
  }
  return 1;
}
$ mock --rebuild ~/Downloads/mysql++-3.2.2-1.src.rpm
...todo...

Using SSL with MariaDB (MySQL)


# Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 \
         -key ca-key.pem -out ca.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

mysql> show global variables like '%ssl%'; 
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| have_openssl  | YES                                   |
| have_ssl      | YES                                   |
| ssl_ca        | /etc/pki/mysql/root/ca-bundle.pem     |
| ssl_capath    |                                       |
| ssl_cert      | /etc/pki/mysql/server/cert.pem        |
| ssl_cipher    |                                       |
| ssl_key       | /etc/pki/mysql/server/key.pem         |
+---------------+---------------------------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 5.5.41-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		5
Current database:	
Current user:		wbaker@devbox.example.com
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		5.5.41-MariaDB MariaDB Server
Protocol version:	10
Connection:		mysql.example.com via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			8 min 50 sec

Threads: 1  Questions: 10  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.018
--------------

GRANT ALL PRIVILEGES ON test.* TO 'someuser'@'somehost'
  REQUIRE
      ISSUER '/C=US/ST=RedState/L=Thistown/O=MySQL Trust Authority 99/CN=The Man/emailAddress=ca@example.com'
  AND SUBJECT '/C=US/ST=BlueState/L=Thattown/O=ACME Widgets/CN=John Doe/emailAddress=john.doe@example.com';

MySQL++ v3.2.2 User Manual

Documents

Mentions

  • Specialized SQL Structures (SSQLS)

Classes

SOLVED: Which MySQL users have access to the database?

show databases;
SELECT * FROM mysql.db WHERE db = 'database_name';
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;

Example

select * from mysql.db where db = 'specimen';
+---------------------------------+-----------+----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host                            | Db        | User     | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+---------------------------------+-----------+----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost                       | specimen  | role     | Y           | Y           | Y           | Y           | Y           | Y         | Y          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| localhost                       | specimen  | wbaker   | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| host.emerson.baker.org          | specimen  | wbaker   | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
+---------------------------------+-----------+----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
3 rows in set (0.00 sec)

References