UMASK & UMASK_DIR – changing default MySQL file dir permissions

UMASK & UMASK_DIR – changing default MySQL file dir permissions

UMASK and UMASK_DIR are amongst few MySQL Environment variables which defines directory and file creation modes (file permissions). In this post we will understand using UMASK & UMASK_DIR for changing the database directory and file permissions from it’s default to what we need.

Changing default MySQL permissions using UMASK & UMASK_DIR

Let’s build some background first. Earlier we learnt about setting-up MySQL database backup using holland and xtrabackup. As a routine I have seen database backups being run under users other than “mysql” or “root” for such physical backups (looking at security aspect). For that, we need to add this backup-user to mysql group and grant the group read and exec permissions on MySQL datadir.

Adding backup user to mysql group:
usermod -a -G mysql backup

Granting group-read,execute permissions to backup user (on MySQL datadir):
chmod g+rx /mysql/data -R

The backup running from “backup” user will be able to read the datadir contents and thus backup will succeed.

Now the fact is by default, MySQL creates database and RAID directories with an access permission value of 0700. So, whenever a new database is created it will not have group-read,execute permissions on them. Thus our xtrabackup will fail reading those files/folders, until we grant the group-read,execute permissions.
This fails our backup. The simple fix to this is manually granting read,execute permission on new database directory to the backup user.

This is the background and now we’re going to answer it 

What if MySQL create databases with the permission we need: user=all, group=read,execute, others=none (750)!

We can do this using UMASK and UMASK_DIR system variables. UMASK defines the mode for files creation while UMASK_DIR is the base mode for directory creation. Yes, UMASK / UMASK_DIR actually are modes and not masks despite the name.

We need 740 permissions for files and 750 for directories, so those will be our respective modes. Let’s see how we get that…

Add following lines to MySQL startup script:

[backup@mysql /mysql/data]# vi /etc/init.d/mysqld

 UMASK=0640
 export UMASK
 UMASK_DIR=0750
 export UMASK_DIR

(Here /mysql/data is MySQL datadir)

Restart MySQL and create database & table.

[backup@mysql /mysql/data]# /etc/init.d/mysqld restart
Shutting down MySQL ..... SUCCESS!
Starting MySQL .. SUCCESS!
[backup@mysql /mysql/data]#  mysql -e "create database abc; use abc;create table a(id int);"; ls -l | grep abc;ls -l abc/;
drwxr-x---. 2 mysql mysql      4096 Jun 23 08:23 abc
total 16
-rw-r-----. 1 mysql mysql 8556 Jun 23 08:23 a.frm
-rw-r-----. 1 mysql mysql   65 Jun 23 08:23 db.opt

Note that under /mysql/data (MySQL datadir), we’ve proper group permissions provided as well as the datafiles are having group read permissions.

Well this ends our fix here, we fixed directory & file permissions for MySQL datadir go grant group user rx (read,execute) access. Though how does this work is a little more than simply setting umask & umask_dir environment variables.

Let’s get into slight detail:

UMASK is the user-file creation mode when creating files.
UMASK_DIR is the user-directory creation mode when creating directories.

The UMASK and UMASK_DIR variables, despite their names, are used as modes, not masks. Read this again, MODES not MASKS.

And “Modes” are the permissions given to users, groups and/or the ‘other’ class to access files. That’s why we set the UMASK & UMASK directory values according to the desired permissions.

MySQL Documentation on UMASK and file permissions:

If UMASK is set, mysqld uses ($UMASK | 0600) as the mode for file creation, so that newly created files have a mode in the range from 0600 to 0666 (all values octal).

Calculations:

($UMASK | 0600)
=> 740 | 600 
=> 740

So that’s how we get our 740 permissions for MySQL datafiles.

MySQL Documentation on UMASK_DIR and directory permissions:

If UMASK_DIR is set, mysqld uses ($UMASK_DIR | 0700) as the base mode for directory creation, which then is AND-ed with ~(~$UMASK & 0666), so that newly created directories have a mode in the range from 0700 to 0777 (all values octal). The AND operation may remove read and write permissions from the directory mode, but not execute permissions.

From above documentation quote, our equation resolves as follows:

($UMASK_DIR | 0700) AND ~(~$UMASK & 0666)
Consider, UMASK_DIR=750 and UMASK=740
=> ( ~( ~0740 & 0666) & 0750 ) 
=> ( ~( 0037 & 0666) & 0750 ) 
=> ( ~( 0026 ) & 0750 ) 
=> ( 0751 & 0750 ) 
=> ( 0750 )

That is how the directory permissions are set.

In the MySQL bug-report (see references) we have a feature request for introducing new variables, $MYSQL_FILE_MODE & $MYSQL_DIRECTORY_MODE and deprecating & discarding the confusing ones, $UMASK & $UMASK_DIR.

Hope this helps.

References:

• https://dev.mysql.com/doc/refman/5.6/en/environment-variables.html
• https://dev.mysql.com/doc/refman/5.6/en/file-permissions.html
• https://bugs.mysql.com/bug.php?id=35090