Securing MySQL using SMF - the Ultimate Manifest

The best way to learn the Solaris Service Management Facility (SMF) is to migrate a legacy service. The version of MySQL that comes with Solaris is an ideal application. It is relatively simple, has few dependencies, and can be done in just a few quick edits of an existing manifest (utmp would be a good starting template). We cover the basic process in the SMF Deep Dive and various people have contributed manifests to OpenSolaris and Blastwave. While these are good illustrations of how easy the process is, few show what SMF can really do. The motivation for this how-to came from a recent Solaris Bootcamp attendee who asked "what was wrong with the RC scripts the way they were ?".

Without skipping a beat.....
  1. Easy support of multiple service instances
  2. Deterministic location of service log files
  3. Timeouts on the start and stop methods to prevent system boots from hanging indefinetely.
  4. Quickly observable service state
  5. Flexible service dependencies
  6. Automatic restarting of the service upon failure
Upon closer inspection, recognizing when the service terminated and restarting it automatically isn't that special for mysql. The mysqld_safe daemon actually performs that step, restarting the database server if it fails. Yes, this is unique to mysql and may not exist for other services. Certianly, if the mysqld_safe parent actually fails, SMF does provide an additional capability by automatically restarting it. But we need more.

Most of the service migration demonstrations are single instance with no downstream application dependencies - so we still need more.

The mysql service start script runs through a set of configuration files, setting variables and starting a detached daemon, so it's highly unlikely that it will ever get stuck. Sure, it can get hacked and have bad things happen to it, but as delivered it is relatively safe. So we still need more.

The answer to the question lies in security. SMF provides a rich set of security features that demonstrate the power of Solaris Role Based Access Control (RBAC) and least privilege. Contrary to what you might think, these features are quite easy to use - once you learn a few simple concepts. This is how we will answer the question "what was wrong with the RC scripts the way they were?".

Authorizations

One of the most useful applications of RBAC is to create adminstration and operations roles. While the details of these roles will vary from customer to customer, the common theme is that operator roles should be able to start and stop a service in a safe manner and an administrative role should be able to modify service properties (of which some of those may be the ability to start or stop the service).

Historically this has been accomplished by third party security software inserting itself all over the kernel (sometimes in a manner that makes upgrades or maintenance difficult) or custom scripts that make use of setuid(2). Solaris 10 can perform many of these functions with just a few entries to some configuration files, and SMF makes this process extremely easy.

You can get lots of valuable information on Solaris Security features (roles, profiles, auths, privileges) at the OpenSolaris Security Community. As you navigate the wealth of white papers, ARC cases, and how-to examples, think of Solaris authorizations as the magic that makes this possible (or more precisely simple).

In a sentence, auths are labels that a privileged application uses to restrict access to it's features. In our case the privileged applications are svcadm(1M) and svccfg(1M). If you read the smf_security(5) man page (which is excellent reading) you will see that SMF provides several authorizations.
  • solaris.smf.manage - ability to start and stop any SMF managed service (good - but not what we are looking for)
  • action_authorization (in the general property group) - allows a non-root user to run the methods (start, stop, and refresh)
  • value_authorization (any property group) - change properties in the property group (such as general/enabled)
  • modify_authorization (any property group) - add or delete properties in the property group
Now this is getting interesting. So it appears that we can use either the action or modify authorization for the operator role. So which one do we use ?

The action_authorization would only allow running the method but not modifying any of the properties. The implication is that you can do
# svcadm enable -t mysql
but not
# svcadm enable mysql
The difference between the two commands is that enable without -t will try to set the property general/enabled to true in additional to running the start method. This would require the value_authorization. But value_authorization will allow you to change (almost) any property in the property group (in this case the general property group), so let's see what else value_authorization will let you do.
# svcprop -p general ssh
general/enabled boolean true
general/action_authorization astring solaris.smf.manage.ssh
general/entity_stability astring Unstable
general/single_instance boolean true
Hmmm, the only properties that might be abused would be the authorizations, but those require additional authorizations (solaris.smf.modify) to change. So it would seem that value_authorization would be safe for an operator role - unorthodox perhaps, but safe. modify_authorization would allow the creation of other service properties, and if limited to the general property group might be confusing, but relatively harmless - unless of course we add a new general property later. For this reason, modify_authorization would not be a good canidate for an operator role.

So which authorization to use ? Use action_authorization if you want a user (or role) to be able to start and stop the service, but not make the change permanent. This is the most common case. Use value_authorization in the general property group if you want that user or role to be able to permanently turn a service on or off - this is generally an adminstrative role.

Let's put this all together.

Start with your existing SMF manifest for MySQL. If you don't have one, you can use mine at http://blogs.sun.com/resources/bobn/mysql.xml or Keith Lawson's contributed MySQL manifest at the OpenSolaris SMF Contributed Manifests and Methods page.

Add the following section
<property_group name='general' type='framework'>
        <propval name='action_authorization' type='astring'    value='mysql.operator' />
       <propval name='value_authorization' type='astring'   value='mysql.administrator' />
</property_group>

Import the new manifest by the method of your choice (svccfg import, /lib/svc/method/manifest-import, or reboot) and your new MySQL can be managed by auths. So how to we get those auths assigned to users (or roles ?).

Authorizations are granted to users and roles by the configuration file /etc/user_attr. You can read the user_attr(4) man page for all of the details, but the process is to add auths=mysql.operator to the user or role entry. For example
# grep \^joeuser /etc/user_attr
joeuser::::type=normal;auths=mysql.operator
It is possible that a user or role may not be present in /etc/user_attr. In that case just add a line like the one above and assign the appropriate auth.

Let's see all of this in action.
% auths
mysql.operator,solaris.smf.manage.name-service.cache,solaris.smf.manage.bind,solaris.admin.dcmgr.clients,solaris.admin.dcmgr.read,solaris.snmp.\*,solaris.network.hosts.\*,solaris.smf.value.routing,solaris.smf.manage.routing,solaris.network.wifi.config,solaris.device.cdrw,solaris.profmgr.read,solaris.jobs.users,solaris.mail.mailq,solaris.admin.usermgr.read,solaris.admin.logsvc.read,solaris.admin.fsmgr.read,solaris.admin.serialmgr.read,solaris.admin.diskmgr.read,solaris.admin.procmgr.user,solaris.compsys.read,solaris.admin.printer.read,solaris.admin.prodreg.read,solaris.snmp.read,solaris.project.read,solaris.admin.patchmgr.read,solaris.network.hosts.read,solaris.admin.volmgr.read,solaris.jobs.user,solaris.device.mount.removable

% svcadm enable -t mysql
% svcs mysql
STATE          STIME    FMRI
online         15:51:02 svc:/application/mysql:default

So far so good.
% svcadm enable mysql
svcadm: svc:/application/mysql:default: Permission denied.

Why did this fail ?
% svcprop -p general mysql
general/enabled boolean true
general/action_authorization astring mysql.operator
general/entity_stability astring Unstable
general/single_instance boolean true
general/value_authorization astring mysql.administrator

Because enable also tries to set the general/enabled property - and that requires value or modify authorization. Change my user definition in /etc/user_attr
% grep \^joeuser /etc/user_attr
joeuser::::type=normal;auths=mysql.operator,mysql.administrator
% auths
mysql.operator,mysql.administrator,solaris.smf.manage.name-service.cache,solaris.smf.manage.bind,solaris.admin.dcmgr.clients,solaris.admin.dcmgr.read,solaris.snmp.\*,solaris.network.hosts.\*,solaris.smf.value.routing,solaris.smf.manage.routing,solaris.network.wifi.config,solaris.device.cdrw,solaris.profmgr.read,solaris.jobs.users,solaris.mail.mailq,solaris.admin.usermgr.read,solaris.admin.logsvc.read,solaris.admin.fsmgr.read,solaris.admin.serialmgr.read,solaris.admin.diskmgr.read,solaris.admin.procmgr.user,solaris.compsys.read,solaris.admin.printer.read,solaris.admin.prodreg.read,solaris.snmp.read,solaris.project.read,solaris.admin.patchmgr.read,solaris.network.hosts.read,solaris.admin.volmgr.read,solaris.jobs.user,solaris.device.mount.removable

% svcadm enable mysql
% svcs mysql
STATE          STIME    FMRI
online         16:10:37 svc:/application/mysql:default

This is all very cool - but we can still do more.

Removing Root from the Equation

For both simplicity and compatibility with other operating systems, the MySQL service is started by a script that is run as root. This script is generally linked into /etc/rc3.d, but since we have converted it to an SMF service we have many more options. We have already looked at delegated administration using auths, time to turn our attention to privileges.
# /etc/sfw/mysql/mysql.server start # ps -ef | grep mysqld | grep -v grep mysql 1975 1955 0 21:43:17 pts/8 0:00 /usr/sfw/sbin/mysqld --basedir=/usr/sfw --datadir=/var/mysql --user=mysql --pid root 1955 1 0 21:43:17 pts/8 0:00 /bin/sh /usr/sfw/sbin/mysqld_safe --datadir=/var/mysql --pid-file=/var/mysql/pa # /etc/sfw/mysql/mysql.server stop This suggests two immediate questions. Does the parent mysqld_safe really have to run as root, or can it be started as a lesser privileged user ? If it can run as a non-root user, exactly what privileges are required to run mysql ?

The answer to the first question is simple: it can be run as a regular user. It only runs as root out of convenience to operating systems that don't have as sophisticated a security framework as Solaris.
#  su - mysql
Sun Microsystems Inc.   SunOS 5.11      snv_57  October 2007
$ sh /etc/sfw/mysql/mysql.server start
$ /usr/sfw/bin/mysqladmin status
Uptime: 1174  Threads: 1  Questions: 1  Slow queries: 0  Opens: 6  Flush tables: 1  Open tables: 0  Queries per second avg: 0.001
$ sh /etc/sfw/mysql/mysql.server stop
Killing mysqld with pid 1975
Wait for mysqld to exit done
$ exit
#
Now that we have established the fact that a fully privileged user isn't required to run MySQL, what privileges are are really required ? How far can we restrict the mysql user ? Glenn Brunette's privilege debugger privdebug.pl is the perfect tool to help us answer this question.
# privdebug.pl -f -v  -e "su - mysql /usr/sfw/sbin/mysqld_safe --user=mysql"
STAT TIMESTAMP          PPID   PID    PRIV                 CMD
USED 2005619300419      2211   2212   proc_taskid          su
USED 2005620883559      2211   2212   proc_setid           su
USED 2005621147993      2211   2212   proc_setid           su
USED 2005621161490      2211   2212   proc_setid           su
USED 2005621165094      2211   2212   proc_setid           su
USED 2005630560973      2211   2212   proc_exec            su
Starting mysqld daemon with databases from /var/mysql                                  contract_event       
USED 2005679230394      2211   2212   proc_fork            sh
USED 2005750348321      2211   2212   proc_fork            sh
USED 2005751386190      2212   2214   proc_exec            sh
USED 2005756249415      2211   2212   proc_fork            sh
USED 2005757238096      2212   2215   proc_fork            sh
USED 2005758495289      2212   2215   proc_exec            sh
USED 2005761778059      2211   2212   proc_fork            sh
USED 2005762623018      2212   2217   proc_fork            sh
USED 2005763874569      2212   2217   proc_exec            sh
USED 2005767441408      2211   2212   proc_fork            sh
USED 2005768337263      2212   2219   proc_exec            sh
USED 2005772916576      2211   2212   proc_fork            sh
USED 2005773996432      2212   2220   proc_fork            sh
USED 2005775465400      2212   2220   proc_exec            sh
USED 2005778750305      2211   2212   proc_fork            sh
USED 2005779846375      2212   2222   proc_exec            sh
USED 2005782042348      2211   2212   proc_fork            sh
USED 2005783110622      2212   2223   proc_exec            sh
USED 2005785636236      2211   2212   proc_fork            sh
USED 2005786824801      2212   2224   proc_exec            sh
USED 2005788593079      2212   2224   proc_exec            nohup
USED 2005790693138      2212   2224   proc_exec            nohup
USED 2005792812264      2211   2212   proc_fork            sh
USED 2005794010658      2212   2225   proc_exec            sh
USED 2005795756145      2212   2225   proc_exec            nohup
USED 2005797704273      2212   2225   proc_exec            nohup
NEED 2005799674735      2211   2212   file_dac_write       sh
USED 2005800708905      2211   2212   proc_fork            sh
USED 2005801869396      2212   2226   proc_exec            sh
USED 2005804780370      2211   2212   proc_fork            sh
USED 2005805854317      2212   2227   proc_exec            sh
USED 2005807860051      2211   2212   proc_fork            sh
USED 2005808907677      2212   2228   proc_exec            sh
USED 2005811293197      2211   2212   proc_fork            sh
USED 2005812393916      2212   2229   proc_exec            sh
USED 2005814589669      2212   2229   proc_exec            nohup
USED 2005816674186      2212   2229   proc_exec            nohup
STOPPING server from pid file /var/mysql/pandora.pid                                  contract_event       
070325 22                    11  mysqld ended 18     contract_event       


Ignore the proc_taskid and proc_setid, they are artifacts of using su(1M) to run the database server as user mysql. We see that mysqld only needs proc_fork and proc_exec. The file_dac_write failure comes from a call to access(2) and is not needed for proper operation.

What do we do with what we have just learned ?

Referring to the smf_method(5) man page (another excellent read), it seems that all we need to do is add a method_credential option to the various methods (start, stop, and refresh). The appropriate section of my new and improved MySQL manifest now looks like
        <exec_method   type='method' name='start' exec='/etc/sfw/mysql/mysql.server %m'  timeout_seconds='60'>
                <method_context>
                        <method_credential user='mysql' group='mysql' privileges='proc_fork,proc_exec'  />
               </method_context>
        </exec_method>
        
        <exec_method   type='method' name='stop' exec='/etc/sfw/mysql/mysql.server %m'  timeout_seconds='120'>
                <method_context>
                        <method_credential user='mysql' group='mysql' privileges='proc_fork,proc_exec'  />
                </method_context>
        </exec_method>
        
        <exec_method   type='method' name='refresh' exec='/etc/sfw/mysql/mysql.server restart'  timeout_seconds='120'>
                <method_context>
                        <method_credential user='mysql' group='mysql' privileges='proc_fork,proc_exec'  />
                </method_context>
        </exec_method>
   

So we quickly modify our manifest and import it using one of the standard methods (svccfg import, /lib/svc/method/manifest-import, or a reboot) and we should be done, right ? Well...... not exactly - but we're close.
% svccfg enable mysql
% svcs mysql
STATE          STIME    FMRI
maintenance    21:53:37 svc:/application/mysql:default

$ tail -5 `svcprop -p restarter/logfile mysql`
[ Mar 26 21:51:12 Method "stop" exited with status 0 ]
[ Mar 26 21:53:36 Enabled. ]
[ Mar 26 21:53:36 Executing start method ("/etc/sfw/mysql/mysql.server start") ]
svc.startd could not set context for method: chdir: No such file or directory
[ Mar 26 21:53:37 Method "start" exited with status 96 ]

Doh! When we followed the MySQL installation instructions at /etc/sfw/mysql/README.solaris.mysql we created a user account called mysql. But we didn't specify a home directory, did we ? No - so the default template value of /home/mysql was used. But there is no /home/mysql, is there ? Well, no.

How do we fix this ?

Set a reasonable home directory for the mysql user. How about /var/mysql ? Elsewhere in the installation instructions we did set ownership and proper permissions to this directory - so that would seem like a reasonable home directory.

As root
# usermod -d /var/mysql mysql
That is one solution, but it may not be practical for all cases. Perhaps a better idea would be to provide a working directory for each of the methods. The benefit is that I could set it differently for each service instance. This would be done in the method_context tag for the method. So I modify my service manifest to look like
        <exec_method   type='method' name='start' exec='/etc/sfw/mysql/mysql.server %m'  timeout_seconds='60'>
                <method_context working_directory='/var/mysql'>
                        <method_credential user='mysql' group='mysql' privileges='proc_fork,proc_exec'  />
               </method_context>
        </exec_method>
        
        <exec_method   type='method' name='stop' exec='/etc/sfw/mysql/mysql.server %m'  timeout_seconds='120'>
                <method_context working_directory='/var/mysql'>
                        <method_credential user='mysql' group='mysql' privileges='proc_fork,proc_exec'  />
                </method_context>
        </exec_method>
        
        <exec_method   type='method' name='refresh' exec='/etc/sfw/mysql/mysql.server restart'  timeout_seconds='120'>
                <method_context working_directory='/var/mysql'>
                        <method_credential user='mysql' group='mysql' privileges='proc_fork,proc_exec'  />
                </method_context>
        </exec_method>
Reimport the manifest and let's see how things go.
# svccfg import /var/svc/manifest/application/mysql.xml
# svcadm clear mysql
# svcs mysql
STATE          STIME    FMRI
maintenance    22:17:49 svc:/application/mysql:default

Argh - now what ?
# tail -5 `svcprop -p restarter/logfile mysql`
/sbin/sh: /etc/sfw/mysql/mysql.server: cannot execute
[ Mar 26 22:17:49 Method "start" exited with status 1 ]
[ Mar 26 22:17:49 Executing start method ("/etc/sfw/mysql/mysql.server start") ]
/sbin/sh: /etc/sfw/mysql/mysql.server: cannot execute
[ Mar 26 22:17:49 Method "start" exited with status 1 ]

Doh! Since Solaris delivers MySQL as a legacy service the start script doesn't have execute permissions for the mysql user. That's easy to fix.
# ls -l /etc/sfw/mysql/mysql.server
-rwxr--r--   1 root     sys         5655 Mar 22 17:05 /etc/sfw/mysql/mysql.server
# chown mysql /etc/sfw/mysql/mysql.server
# svcadm clear mysql
# svcs mysql
STATE          STIME    FMRI
online         22:23:08 svc:/application/mysql:default
bash-3.00$ 
Now that's more like it. One last item to check.
# ps -ef | grep mysqld | grep -v grep
   mysql 12656 12634   0 22:23:11 ?           0:00 /usr/sfw/sbin/mysqld --basedir=/usr/sfw --datadir=/var/mysql --pid-file=/var/my
   mysql 12634     1   0 22:23:09 ?           0:00 /bin/sh /usr/sfw/sbin/mysqld_safe --datadir=/var/mysql --pid-file=/var/mysql/pa
   
# ppriv 12634
12634:  /bin/sh /usr/sfw/sbin/mysqld_safe --datadir=/var/mysql --pid-file=/var
flags = 
        E: basic,!file_link_any,!proc_info,!proc_session
        I: basic,!file_link_any,!proc_info,!proc_session
        P: basic,!file_link_any,!proc_info,!proc_session
        L: all

Now that's what I wanted to see. The parent mysqld_safe is now running as user mysql and with exactly the right privileges. This is very cool indeed. Armed with this information we could also create a zone and use the limitpriv attribute to restrict the zone privilege - but we'll leave that for another day.

Conclusion

It is quite easy to leverage not only Solaris authorizations but to run services with restricted privileges. We have presented a few templates and a general approach that should make this process less cumbersome.

More important though - we now have a compelling reply when asked "what was wrong with the RC scripts the way they were?"

Technocrati Tags:
Comments:

This was extremely helpful to me. Thank you for the excellent write-up!

Posted by roclar on March 31, 2007 at 10:34 AM CDT #

This is a great example of "putting it all together", Bob!

Posted by Isaac on April 14, 2007 at 11:18 AM CDT #

Bob, One problem I noticed with limiting the MySQL rc script to proc_fork,proc_exec was that when the RC script tried to kill MySQL, it couldn't, causing Mysql to go into "maintenence" mode. I'd be interested to see if the Sun MySQL rc script is different than the one that came with the MySQL distrobution we used (Sun Freeware, I think). - Mike

Posted by Mike on April 20, 2007 at 10:49 AM CDT #

According to privdebug.pl, the stop method needed the 'proc_session' privilege. I added proc_session to the stop and refresh methods and it worked perfectly.

Posted by roclar on June 29, 2007 at 08:37 AM CDT #

Thanks for the excellent tutorial, not only for the detailed information, but also for the way how they are presented. I have a question though. I checked mysql-5-0/Solaris/mysql.xml file on sfwnv-gate just now but didn't find it specify proc_fork and proc_exec privileges, does that mean they are not necessary any more? Thanks.

Posted by rayx on May 25, 2008 at 11:26 PM CDT #

thanks for the helpful post!

Posted by Igor Minar on September 15, 2008 at 08:30 AM CDT #

This is a useful article. I tried to use the instructions with binary distribution (tar) downloaded from mysql.com.
When I try to run the svcadm enable mysql, it goes into maintenance mode. In the application log, it just states that the application exited with status 1, but the mysql startup log - hostname.err file indicates the following error: Can't create IP socket: Permission Denied.
Does this imply that there need to additional privileges added to the manifest file, beyond what is stated in this article?
My test configuration: Solaris 10, x86 (running on VMWare vSphere4)
MySQL edition - 5.5.13

I could not find the privdebug script mentioned in this article (maybe this is replaced with something better in Sol 10?)
Any insight would be helpful.

thanks

Posted by guest on June 28, 2011 at 06:38 PM CDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Bob Netherton is a Principal Sales Consultant for the North American Commercial Hardware group, specializing in Solaris, Virtualization and Engineered Systems. Bob is also a contributing author of Solaris 10 Virtualization Essentials.

This blog will contain information about all three, but primarily focused on topics for Solaris system administrators.

Please follow me on Twitter Facebook or send me email

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today