MySQL Security

Did extensive research on MySQL security… here are my salient findings. Hope you find it useful:

The ‘mysql’ system database stores tables that control user authentication and security. Recall that one of Codd’s rules of RDBMS states that it should manage all internal DB operations using relational constructs.
Mysql actually provides a pretty fine grained security mechanism.. even upto the table column level (It lacks a workgroup model though.. Perhaps the kind folks at MySQL may introduce it in some future version)

The tables involved in maintaing security are described below(I got the table defs by doing a ‘show create table’ at the mysql prompt)

User
CREATE TABLE `user` (
`Host` varchar(60) binary NOT NULL default ”,
`User` varchar(16) binary NOT NULL default ”,
`Password` varchar(16) binary NOT NULL default ”,
`Select_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Insert_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Update_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Delete_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Create_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Drop_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Reload_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Shutdown_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Process_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`File_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Grant_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`References_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Index_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Alter_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Show_db_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Super_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Create_tmp_table_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Lock_tables_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Execute_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Repl_slave_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Repl_client_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`ssl_type` enum(”,’ANY’,’X509′,’SPECIFIED’) NOT NULL default ”,
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL default ‘0’,
`max_updates` int(11) unsigned NOT NULL default ‘0’,
`max_connections` int(11) unsigned NOT NULL default ‘0’,
PRIMARY KEY (`Host`,`User`)
) TYPE=MyISAM COMMENT=’Users and global privileges’

Db

CREATE TABLE `db` (
`Host` char(60) binary NOT NULL default ”,
`Db` char(64) binary NOT NULL default ”,
`User` char(16) binary NOT NULL default ”,
`Select_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Insert_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Update_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Delete_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Create_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Drop_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Grant_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`References_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Index_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Alter_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Create_tmp_table_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
`Lock_tables_priv` enum(‘N’,’Y’) NOT NULL default ‘N’,
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) TYPE=MyISAM COMMENT=’Database privileges’

Tables_priv
CREATE TABLE `tables_priv` (
`Host` char(60) binary NOT NULL default ”,
`Db` char(64) binary NOT NULL default ”,
`User` char(16) binary NOT NULL default ”,
`Table_name` char(64) binary NOT NULL default ”,
`Grantor` char(77) NOT NULL default ”,
`Timestamp` timestamp(14) NOT NULL,
`Table_priv` set(‘Select’,’Insert’,’Update’,’Delete’,’Create’,’Drop’,’Grant’,’References’,’Index’,’Alter’) NOT NULL default ”,
`Column_priv` set(‘Select’,’Insert’,’Update’,’References’) NOT NULL default ”,
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) TYPE=MyISAM COMMENT=’Table privileges’

Columns_priv
CREATE TABLE `columns_priv` (
`Host` char(60) binary NOT NULL default ”,
`Db` char(64) binary NOT NULL default ”,
`User` char(16) binary NOT NULL default ”,
`Table_name` char(64) binary NOT NULL default ”,
`Column_name` char(64) binary NOT NULL default ”,
`Timestamp` timestamp(14) NOT NULL,
`Column_priv` set(‘Select’,’Insert’,’Update’,’References’) NOT NULL default ”,
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) TYPE=MyISAM COMMENT=’Column privileges’

Evidently, no referential integrity constraints have been set on these tables (although they are pulled from an installation of MySQL ver 4.1)…. probably a downward compatibility thing – note that all tables are of type MyISAM and not Innodb.

Because of the lack of referential integrity constraints, it is very easy to mess up these these tables… For instance, I can add a row to tables_priv without actually having an entry in the user table!

The Process:

>Check for an entry in the User table with the users login name, password and host address.
>If the user has access to all tables in a database, there will be a corresponding entry in the Db table
>If the user has limited access to tables, they will be listed in the Tables_priv table
>If the user has access only to a few fields in selected tables, those rows appear in the Columns_Priv table.

Note that it is possible to have entries for a user in the tables_priv table without having an entry in the db table.
Similarly, there can be entries for the user in the columns_priv table without entries in the db or tables_priv tables.. Gotcha??? Good.

Also note that the ‘Grant’ and ‘Revoke’ SQL statements internally manage these tables (very well).. so please use extreme caution while issuing commands directly against these tables.

Host based security decreases the vulnerability of your database.

For instance, if your username and password do fall in malicious hands, unless they are in the host address range specified, they will not be able to login to the db.

If you are running a web application that reads and writes stuff from a db server, you obviously have a userid and password setup that you enter into a ‘config file’ visible to the server script. When creating the userid, if you specify the ‘host’ as the IP address of the web server, you reduce your chance of a hacker finding his way to your server (Of course it is still possible to attempt an IP Spoof …. this scheme will be more secure if used alongside an intrusion detection system)

If you already have a mysql user account ‘user1’ being accessed by a web app running on server with and ip address x.x.x.x, use the following statements to provide host based security.

update mysql.user set Host=’X.X.X.X’ where user=’user1′;
update mysql.db set Host=X.X.X.X’ where user=’ user1′;
flush privileges;

Important Note: If you have a user account that uses ‘host’ based security and the account has privileges on other tables (and/or columns), you must leave the ‘host’ column in tables_priv and Columns_priv as ‘*’ for it to work properly…dont ask why!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s