About Securich Started April 2009 Migration from Sybase to MySQL - - PowerPoint PPT Presentation

About Securich

 Started April 2009

 Migration from Sybase to MySQL inspired it

 Open Sourced June 2009 v0.1.1  Current version v0.2.5  GPLv2 (Sharing is Caring)  Supported on MySQL 5.1.12 +

 NDB cluster - untested

DBA responsibilities

TO US DATA IS SACRED WE NEED TO PROTECT IT

User management

 How often do we audit user privileges and access

levels?

 Do we forget to remove temporary privileges once

the user is done with the task?

 How fast do we revoke access to former employees

• r compromised users?

MySQL security model

USER DB TABLES

COLUMNS

• GRANTED - EXECUTE QUERY
• DENIED check db table
• GRANTED - EXECUTE QUERY
• DENIED check tables_priv table
• GRANTED - EXECUTE QUERY
• DENIED check columns_priv table
• GRANTED - EXECUTE QUERY
• DENIED - BLOCK

Privileges checking hierarchy

MySQL Security Model

 Authentication against 'username'@'hostname'  Password hashed by PASSWORD() function  Wide range of privileges  Intelligent control for requests of granting privileges

(can’t grant what user doesn’t already have privileges on etc)

PROS

MySQL Security Model

 Quotes from “High Performance MySQL 2nd Ed”

" ..... MySQL doesn’t provide any functionality for User groups or roles, as they’re variously known in other database servers ..... " " ..... MySQL also doesn’t provide any way for an administrator to enforce good password standards ..... "

MySQL Security Model

limitations

 Passwords limits not available

• No password size limit
• No password history
• No password complexity meter
• No password minimum age

 Complex to manage  No roles  No user cloning  Easily unsecured

MySQL Security Model

securich adds

 Passwords limits made available

• Password size limit
• Password history
• Password complexity meter
• Password minimum age

 Easier to manage  Pseudo roles provided  Users can be cloned  MySQL is secured on installation

SECURI - Roles

 What is a role ?

 A role is a set or group of privileges that can be

granted to users.

SECURI - Cloning

SECURI

securich mysql

grant / revoke create / drop set password

information schema

reconciliation

SECURI

 PROS

• Backend Open source
• Very easy to install, upgrade, manage and uninstall
• Doesn’t remove any prior functionalities
• Enables migration from mysql to securich
• Enables roles
• Enhances password security
• Enables user cloning
• Enables granting privileges on tables using regexp
• Enables revoking privileges on tables using regexp

 PROS

• Lightweight – Doesn’t require special resources
• Self explanatory design
• Compatible with MySQL 5.1.12 onwards
• Friendlier display of `show grants`
• Embedded `help` - documentation
• Dynamic roles
• Enables temporary block and unblock user
• Stores user creation date and time

SECURI

 PROS

• Password dictionary compare
• Password complexity – Configurable
• Audits changes in roles
• Audits password changes
• Audits grants and revokes
• Restricts password changes to current user
• Steady updates / new features
• Assumes no_auto_create
• Users can check their own privileges

SECURI

 PROS

• Users can be safely renamed
• Outputs suggestions if error is encountered
• Option to kill user connections when revoking privileges
• Enables reserved usernames
• Doesn’t permit grants to the ‘mysql’ database
• It doesn’t need Perl or other kind of compiler or

interpreter

• GUI is open source and platform independent

SECURI

• Doesn’t work on versions 5.1.11 and earlier

because of certain types of prepared statements, it uses the information_schema and new password hashing

• No column level privileges (future feature)
• No functions privileges (future feature)
• Emailing user about password expiry is in bash

(not SQL)

• It is beta

SECURI - Cons

 Password setting by user requires old password  Password needs to be not less than eight characters (configurable)  Password complexity is configurable through sec_config or sam-my:  Password complexity is only NOT obeyed when password is changed by

root

 Password history stores last five passwords

SECURI - Passwords

Password length Uppercase Lowercase Numbers Special Characters Dictionary check Username equivalent check

Live Demo

SECURI

SECURI - Design

SECURI - Credits

Big guys Command line Applications / Web app MySQL DB vi / vim SQL Yog + Wine MySQL Workbench screen Text Wrangler MySQL Docs visor Mac VIM Google (chrome / code) unix tools / bash XAMPP

TradingScreen

Nicklas Westerlund and Lenz Grimmer

SECURI

THANK

Darren Cassar Skype: darren.cassar Email: darren@darrencassar.com Email: info@securich.com URL: http://www.securich.com URL: http://code.google.com/p/securich URL: http://code.google.com/p/sam-my Blog: http://www.mysqlpreacher.com

SECURI - Thank You

SECURI

SECURI

SECURI

SECURI

SECURI