{"id":970,"date":"2009-03-29T10:30:11","date_gmt":"2009-03-29T15:30:11","guid":{"rendered":"http:\/\/www.coresecuritypatterns.com\/blogs\/?p=970"},"modified":"2020-08-08T04:03:11","modified_gmt":"2020-08-08T04:03:11","slug":"demystifying-mysql-security-for-web-20-part-2","status":"publish","type":"post","link":"https:\/\/websecuritypatterns.com\/blogs\/2009\/03\/29\/demystifying-mysql-security-for-web-20-part-2\/","title":{"rendered":"Demystifying MySQL Security for Web 2.0: Part 2"},"content":{"rendered":"

Access control exploits, user credential exposures and related security compromises are becoming increasingly common in Web 2.0 world <\/a>! Most of these issues pertain to broken or insufficient authentication controls and flawed credential management that allows attackers to compromise vulnerable applications by stealing or manipulating credentials such as passwords, keys, session cookies and\/or impersonating another user through forged or guessed credentials.  Any such access control failure leads to unauthorized access and disclosure of underlying application databases, user accounts and stored data.  Most access control related vulnerabilities are due to the inherent application-specific weakness and failure to enforce authentication mechanisms, verify authentication credentials, lack of policy enforcement prior to granting or denying access to the underlying database.<\/p>\n

This is my second installment of work exploring MySQL security features to enforce stronger authentication controls and defend against unauthorized disclosure of user account credentials and application-related <\/span>database tables<\/span>.  In simpler terms, I will be uncovering a set of MySQL security mechanisms intended for the following:<\/p>\n

    \n
  1. X.509 certificate-based  MySQL authentication<\/li>\n
  2. Enabling host verification to cease access from untrusted hosts<\/li>\n
  3. Restricting remote access to MySQL database<\/li>\n
  4. Disable unauthorized access to local files<\/li>\n
  5. Securing MySQL user accounts, passwords and access privileges<\/li>\n
  6. Data encryption using AES<\/li>\n<\/ol>\n

    X.509 Certificate based MySQL authentication<\/h2>\n

    Enforcing X.509 v3 Certificate authentication allows clients to authenticate the MySQL database server using X.509 certificates and its attributes. To enable certificate based authentication,  the MySQL  GRANT<\/em><\/strong> statement allows to limit user access to request X.509 certificate by specifying a set of options.  To connect the client must specify the certificates using  <\/span>\u2013ssl-ca  (CA certificate), <\/span><\/span><\/em> <\/span>\u2013ssl-cert (Client certficate) and <\/span><\/span><\/em>-ssl-key (Client key).<\/span><\/span><\/em><\/p>\n

    <\/em> <\/p>\n

    a)  The REQUIRE X509<\/em><\/strong>  option allows user to provide a valid X.509 certificate, where the signing authority should be verifiable using the CA certificate. <\/p>\n

    mysql>  GRANT  ALL PRIVILEGES  ON  test.*  TO ‘ramesh’@’localhost’  IDENTIFIED  BY  ‘password’  REQUIRE  X509;<\/em><\/span><\/span><\/p>\n

    <\/em><\/span><\/span> <\/p>\n

    b) The REQUIRE SUBJECT  ..  AND ISSUER<\/em><\/strong>  .. option allows the user to provide a valid X.509 certificate containing the subject information of the user and the certificate issued by a specific CA  as defined in the GRANT statement. The user’s certificate and the specified SUBJECT and ISSUER attributes are verified against the information provided with GRANT statement.<\/span><\/span><\/p>\n

    mysql>  GRANT  ALL PRIVILEGES  ON  test.*  TO ‘ramesh’@’localhost’  IDENTIFIED  BY  ‘password’  REQUIRE SUBJECT  ‘\/C=US\/ST=Massachusetts\/L=Burlington\/O=Sun<\/em><\/span><\/span><\/span><\/span> M<\/em><\/span><\/span><\/span><\/span>icrosystems\/CN=Ramesh Nagappan\/Email =Ramesh.Nagappan@mysqltest.com’  AND ISSUER ‘\/C=US\/ST=Massachusetts\/L=Burlington\/O=Sun Microsystems\/CN=SunTest CA’;<\/em><\/span><\/span><\/span><\/span><\/p>\n

    <\/em><\/span><\/span><\/span><\/span> <\/p>\n

    c) In addition to SUBJECT and ISSUER, the user’s certificate can be identified with the specific CIPHER .  The REQUIRE CIPHER<\/em><\/strong> option allows to specify the required algorithm to grant access to the database.<\/span><\/span><\/span><\/span><\/p>\n

    mysql>  GRANT  ALL PRIVILEGES  ON  test.*  TO ‘ramesh’@’localhost’  IDENTIFIED  BY  ‘password’  REQUIRE SUBJECT  ‘\/C=US\/ST=Massachusetts\/L=Burlington\/O=Sun Microsystems\/CN=Ramesh Nagappan\/Email =Ramesh.Nagappan@mysqltest.com’  AND ISSUER ‘\/C=US\/ST=Massachusetts\/L=Burlington\/O=Sun Microsystems\/CN=SunTest CA’   AND  CIPHER ”<\/em>DHE-RSA-AES256-SHA’;<\/span><\/span><\/span><\/span><\/p>\n

    d)  To allow access to user with SSL-enabled connection.<\/span><\/span><\/span><\/span><\/p>\n

    GRANT  ALL PRIVILEGES  ON  test.*  TO ‘ramesh’@’localhost’  IDENTIFIED  BY  ‘password’  REQUIRE SSL;<\/em><\/span><\/span><\/span><\/span><\/p>\n

    <\/em><\/span><\/span><\/span><\/span> <\/p>\n

    Trusted Host Verification<\/span><\/h2>\n

    Host identification helps to allow the user requests initiated from the specified host only. If the user and hostname doesnot match the specified host the server will deny access to the database.  To enable host verification, the MySQL  CREATE USER and GRANT statements allows to specify the user assigned with a target hostname.<\/span><\/p>\n

    a)  The CREATE USER allows to specify the user assigned to a specific hostname. The user will be allowed access only if the request orginated from the specified hostname.<\/span><\/p>\n

    mysql>   CREATE  USER  ‘ramesh’@’localhost’  IDENTIFIED BY ‘some_password’;<\/em><\/span><\/span><\/span><\/p>\n

     The above statement creates an user ‘ramesh’ assigned to hostname ‘localhost’.  This means ‘ramesh’@’localhost’ account can be used only when connecting from the localhost.  <\/span><\/span><\/span><\/p>\n

    b)  The GRANT statement allows to define user privileges on a database table only when the user is accessed from a specified host.  If the user connected from a different host the access will be denied.<\/span><\/p>\n

    mysql>   GRANT SELECT,INSERT,UPDATE  ON  test.*  TO  ‘ramesh’@’east.sun.com’<\/a>;<\/em><\/span><\/span><\/span><\/span><\/p>\n

    <\/em><\/span><\/span><\/span><\/span> <\/p>\n

    <\/em><\/span><\/span><\/span><\/span> <\/p>\n

    Disabling Remote Access from Network<\/span><\/span><\/span><\/span> <\/h2>\n

    If the MySQL database is accessed locally by the coexisting appplications, remote access from the network can be disabled.  To disable remote access via network,  you may add skip-networking<\/em><\/strong> under the [mysqld]<\/em><\/strong> section of my.cnf<\/em><\/strong> or start mysqld <\/em><\/strong>using the –skip-networking<\/em><\/strong> option.  To enable MySQL listen to a specific host IP address, you need to set the following attribute in the [mysqld]<\/em><\/strong> section of my.cnf<\/em><\/strong>  as follows:<\/span><\/span><\/span><\/p>\n

    bind-address=Host-IP-address<\/em><\/span><\/span><\/span><\/p>\n

     <\/p>\n

     <\/p>\n

    Disabling unauthorized access to Local files<\/span><\/span><\/span><\/h2>\n

    To disable unauthorized access or reading of local files, particularly to prevent applications access local files using SQL injection attacks – you may add the set-variable=local-infile=0<\/em><\/strong> under the [mysqld]<\/em><\/strong> section of my.cnf<\/em><\/strong> . <\/span><\/span><\/span><\/p>\n

    Also, run MySQL as run as an user with minimized privileges so that any potential attacks does not result in damages to the operating system and other processes.  <\/span><\/span><\/span><\/p>\n

     <\/p>\n

     <\/p>\n

    Securing MySQL User Accounts, Passwords and Privileges<\/span><\/span><\/span><\/h2>\n

     <\/h2>\n

    a) To prevent unauthorized and anonymous access to the server,  first remove the test database and all user accounts (with the exception of root account).<\/p>\n

    mysql> drop database test;
    \nmysql> use mysql;
    \nmysql> delete from db;
    \nmysql> delete from user where not (host=”localhost” and user=”root”);
    \nmysql> flush privileges;<\/em><\/p>\n

    mysql> quit;<\/em><\/p>\n

    <\/em> <\/p>\n

    b)  Change the MySQL root<\/strong><\/em> password and make sure the password is done via mysql> command line.  It is a bad practice, to change passwords via mysqladmin – u root<\/em><\/strong> password as the password can be accessed via “ps -aef”  (Solaris) “ps -aux” (Linux) command or by reviewing the Unix command history files. <\/p>\n

     <\/p>\n

    c)  Passwords are usually visible as plain text in SQL statements especially while executing CREATE USER, GRANT and SET PASSWORD statements. If the MySQL server is logging the SQL events and action to tables, then make sure those tables are protected from unauthorized users.<\/p>\n

     <\/p>\n

    d) Change the default administrator account name from ‘root’ to a harder to guess ‘username’.  This would help defend against hackers performing dictionary\/brute-force guessing attacks for administrator credentials.<\/p>\n

    mysql>  update user set user=”mysqlgeek’ where user=”root”;<\/em>
    \nmysql> flush privileges;<\/em><\/p>\n

    <\/em> <\/p>\n

    e) MySQL stores user accounts and its passwords in mysql.user<\/em><\/strong> table. Disable access to this table for any non-administrator users.<\/p>\n

     <\/p>\n

    f)  Enforce the ‘principle of least privileges’ by granting minimum privileges for performing the required actions especially for user accounts that connects to the MySQL database from external applications. Do not grant privileges at the database level, MySQL allows to define privileges as required at the Table and Column level<\/strong><\/em>.<\/p>\n

        grant <privileges> <column> on <database>.<table> to <login-name>@<FQDN-or-IP> identified by <password>;<\/em><\/p>\n

    <\/em> <\/p>\n

    Data Encryption using AES<\/h2>\n

    MySQL supports data encryption functions by providing support for AES (Advanced Encryption Standard) and DES (Triple-DES) algorithms.  It is important to note, the encryption function return binary strings as BLOBS, so you may need to store the encrypted data in columns of BLOB or VARBINARY data types. MySQL provides AES_ENCRYPT( ) and AES_DECRYPT ( )  to facilitate AES based encryption and decryption  and DES_ENCRYPT( ) and DES_DECRYPT ( )  to facilitate Triple-DES based encryption and decryption  operations.<\/p>\n

    For example:<\/p>\n

    mysql>  insert into mytable (username, password)  VALUES (‘nramesh’,  AES_ENCRYPT(‘g01ns@n3’, ‘myaeskey’));<\/em><\/p>\n

    mysql>  select username, AES_DECRYPT(password, ‘myaeskey’) from mytable;<\/em><\/p>\n

    +——–+————————————-+
    \n| username | des_decrypt(password, ‘myaesencryptionkey’)  |
    \n+——–+————————————-+
    \n| nramesh | g01ns@n3 |
    \n| bobama | s@v3u5a |
    \n+——–+———————————+<\/p>\n

    It is important to note, the encryption KEY must be provided by the application user to MySQL. It means that MySQL does’nt provide mechanisms for generating the keys. Also it is critical to store the key for supporting further decryption operations.<\/p>\n

    That’s all folks. I will revisit again on my next MySQL security project …till then let me practice wearing an Oracle shirt \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"

    Access control exploits, user credential exposures and related security compromises are becoming increasingly common in Web 2.0 world ! Most of these issues pertain to broken or insufficient authentication controls and flawed credential management that allows attackers to compromise vulnerable applications by stealing or manipulating credentials such as passwords, keys, session cookies and\/or impersonating another user through forged or guessed credentials.  Any such access control… Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[17,20,6,9],"tags":[37,48,50,57,62,73],"class_list":["post-970","post","type-post","status-publish","format-standard","hentry","category-database-security","category-java-ee","category-main","category-security","tag-j2ee","tag-mysql","tag-openssl","tag-pki-main","tag-security","tag-web2-0"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/posts\/970","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/comments?post=970"}],"version-history":[{"count":1,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/posts\/970\/revisions"}],"predecessor-version":[{"id":2833,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/posts\/970\/revisions\/2833"}],"wp:attachment":[{"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/media?parent=970"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/categories?post=970"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/tags?post=970"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}