DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • Building a Flask Web Application With Docker: A Step-by-Step Guide
  • Decoding eBPF Observability: How eBPF Transforms Observability as We Know It
  • Stack in Data Structures
  • IDE Changing as Fast as Cloud Native

Trending

  • Building a Flask Web Application With Docker: A Step-by-Step Guide
  • Decoding eBPF Observability: How eBPF Transforms Observability as We Know It
  • Stack in Data Structures
  • IDE Changing as Fast as Cloud Native
  1. DZone
  2. Data Engineering
  3. Databases
  4. Emulating MySQL Roles With the Percona PAM Plugin and Proxy Users

Emulating MySQL Roles With the Percona PAM Plugin and Proxy Users

Peter Zaitsev user avatar by
Peter Zaitsev
·
Mar. 06, 15 · Interview
Like (0)
Save
Tweet
Share
6.55K Views

Join the DZone community and get the full member experience.

Join For Free

From time to time people wonder how to implement roles in MySQL. This can be useful for companies having to deal with many user accounts or for companies with tight security requirements (PCI or HIPAA for instance). Roles do not exist in regular MySQL but here is an example on how to emulate them using Percona Server, the PAM plugin and proxy users.

The goal

Say we have 2 databases: db1 and db2, and we want to be able to create 3 roles:

  • db1_dev: can read and write on db1 only.
  • db2_dev: can read and write on db2 only.
  • stats: can read on db1 and db2

For each role, we will create one user: joe (db1_dev), mike (db2_dev) and tom (stats).

Setting up the Percona PAM plugin

The Percona PAM plugin is distributed with Percona Server 5.5 and 5.6. I will be using Percona Server 5.6 in this post and I will authenticate users with /etc/shadow. As explained here, the setup is easy:

  • Make sure /etc/shadow can be read by the mysql user:
    # chgrp mysql /etc/shadow
    # chmod g+r /etc/shadow
  • Install the plugin:
    mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';
  • Create a /etc/pam.d/mysqld file containing:
    auth       required     pam_warn.so
    auth       required     pam_unix.so audit
    account    required     pam_unix.so audit

Tinkering with the permissions of /etc/shadow may a security concern. Authenticating users against an LDAP server may be a better option. The configuration of the PAM plugin is similar (replace pam_unix.so with pam_ldap.so and forget the part about /etc/shadow).

Testing authentication with the PAM plugin

Now let’s create a user:

# adduser test_pam
# passwd test_pam
mysql> GRANT ALL PRIVILEGES ON db1.* TO test_pam@localhost IDENTIFIED WITH auth_pam;

And let’s check that the authentication is working as we expect:

mysql -utest_pam -p
Enter password:
mysql> show grants;
+-----------------------------------------------------------+
| Grants for test_pam@localhost                             |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_pam'@'localhost'              |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'test_pam'@'localhost' |
+-----------------------------------------------------------+

That works! We can delete the user and go to the next step.

Creating proxy user

The key to emulate a role is to create a MySQL account for which nobody will know the password (then nobody will be able to use it directly). Instead we will use the PROXY privilege to make sure we map an anonymous account that will match any incoming user to the right MySQL user.

So the first step is to create an anonymous user:

mysql> CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysqld, pam_db1=db1_dev, pam_db2=db2_dev, pam_stats=stats';

The goal of this user is simply to map Unix users in the pam_db1 group to the db1_dev MySQL user, Unix users in the pam_db2 group to the db2_dev MySQL user and Unix users in the pam_stats group to the stats MySQL user.

Creating the proxied accounts

Now we can create the MySQL users corresponding to each of the roles we want to create:

mysql> GRANT SELECT, INSERT ON db1.* TO 'db1_dev'@localhost IDENTIFIED BY 'XXXXX';
mysql> GRANT PROXY ON 'db1_dev'@'localhost' TO ''@'';
mysql> GRANT SELECT, INSERT ON db2.* TO 'db2_dev'@localhost IDENTIFIED BY 'YYYYY';
mysql> GRANT PROXY ON 'db2_dev'@'localhost' TO ''@'';
mysql> GRANT SELECT ON db1.* TO 'stats'@localhost IDENTIFIED BY 'ZZZZZ';
mysql> GRANT SELECT ON db2.* TO 'stats'@localhost;
mysql> GRANT PROXY ON 'stats'@'localhost' TO ''@'';

Creating the Unix user accounts

The last step is to create the Unix users joe, mike and tom and assign them the correct group:

# useradd joe
# passwd joe
# groupadd pam_db1
# usermod -g pam_db1 joe
# useradd mike
# passwd mike
# groupadd pam_db2
# usermod -g pam_db2 mike
# useradd tom
# passwd tom
# groupadd pam_stats
# usermod -g pam_stats tom

Again you may prefer using an LDAP server to avoid creating the users at the OS level.

Testing it out!

Let’s try to connect as mike:

# mysql -umike -p
Enter password:
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------+
| Grants for db2_dev@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'db2_dev'@'localhost' IDENTIFIED BY PASSWORD '*C1DDB6E980040762275B29A316FD993B4A19C108' |
| GRANT SELECT, INSERT ON `db2`.* TO 'db2_dev'@'localhost'                                                       |
+----------------------------------------------------------------------------------------------------------------+

Not bad!

Alternatives

The Percona PAM plugin is not the only option to use roles:

  • MariaDB 10 supports roles from version 10.0.5
  • Oracle distributes a PAM plugin for MySQL 5.5 and MySQL 5.6 as part of the MySQL Enterprise subscription
  • Securich is a set of stored procedures that has many features regarding user management
  • Google has been offering support for roles through its google-mysql-tools for a long time.

Conclusion

Even if they are not officially supported, roles can be emulated with an authentication plugin and a proxy user. Let’s hope that roles will be added in MySQL 5.7!

 

MySQL Database

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Building a Flask Web Application With Docker: A Step-by-Step Guide
  • Decoding eBPF Observability: How eBPF Transforms Observability as We Know It
  • Stack in Data Structures
  • IDE Changing as Fast as Cloud Native

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: