A quick tour of MySQL 8.0 roles

A quick tour of MySQL 8.0 roles

Roles are a helpful way to simplify user management in a database environment.

A quick tour of MySQL 8.0 roles
Image by : 

opensource.com

x

Get the newsletter

Join the 85,000 open source advocates who receive our giveaway alerts and article roundups.

This year at the Percona Live Open Source Database Conference in Dublin, I'll be discussing a new feature introduced in MySQL 8.0: roles. This is a new security and administrative feature that allows database administrators to simplify user management and increases the security of multi-user environments.

In database administration, users are granted privileges to access schemas, tables, or columns, depending on the business needs. When many different users require authorization for different sets of privileges, administrators have to repeat the process of granting privileges several times. This is both tedious and error-prone. Using roles, administrators can define sets of privileges for a user category, and then the user authorization becomes a single statement operation.

Roles have been on the MySQL community's wish list for a long time. I remember several third-party solutions that tried to implement roles as a hack on top of the existing privileges granting system. I created my own solution many years ago when I had to administer a large set of users with different levels of access. Since then, anytime a new project promised to ease the roles problem, I gave it a try. None of them truly delivered a secure solution, until now.

When I saw the roles feature in MySQL 8, I tested it extensively, provided feedback to the MySQL team and asked for better usability. I was pleased to see that in the latest release (8.0.2) the team addressed some of my concerns, making the roles both easier to use and more powerful—although at the same time they introduced a new extension (mandatory roles) that could create more problems. All in all, I am delighted with the attitude of the MySQL team: they were willing to listen to my feedback and my proposals for improvement.

On a practical side, roles should be on everyone's radar. In a well-regulated and security-minded organization, administrators should only use roles for privilege management. This policy not only simplifies user management but also provides meaningful data on privilege usage. Also, developers should start getting familiar with roles, as they will become mainstream quite soon. Roles have a particularity in common with triggers: it's that kind of feature that developers can't ignore, on penalty of not understanding what the database can, and will, do.

Although roles make everyday tasks easier, they also present some additional challenges. In the MySQL implementation, roles are users with some small differences. While this similarity allows admins to get lazy and assign pre-existing users as roles to other users, this practice would make administration more difficult in the long run. To truly benefit from this new feature, DBAs must get organized, and spend some time planning how they want to orchestrate their roles for maximum efficiency before plunging in.

Many users, when hearing about roles, think that it's a straightforward extension of the existing privileges system. In practice, roles usage is a minefield. Many commands perform seemingly the same operation but often lead to unexpected results.

My session should make the basic operations clear, and teach attendees how to avoid the most common pitfalls.

To see my talk, register for the Percona Live Europe Open Source Database Conference 2017. Use the code SeeMeSpeakPLE17 to get 10% off your registration.

Topics

About the author

Giuseppe Maxia - Giuseppe Maxia is a Software Explorer at VMware. He previously held positions at MySQL AB, Sun Microsystems, Oracle and Continuent. Maxia is an active member of the MySQL community and long timer open source enthusiast. He has dedicated the past 25 years to various IT related fields, with a focus on databases, object oriented programming, system administration. Maxia is fluent in Italian, English, Spanish, Perl, Python, SQL, C, Bash, and good speaker of C, French, Java, and Ruby.