Why wouldn't you set activate_all_roles_on_login?

Question

I'm just getting my toes wet in MySQL DBA and created some users and assigned roles to them. It took me a long time to realize that this on its own didn't result in the user assuming the roll when they login. This led me to learn about activate_all_roles_on_login and SET DEFAULT ROLE. I understand what they do, but I'm struggling to imagine a scenario where you wouldn't want the users and roles you created to be active after logging it.

I know I must be missing something, what are some reasons you wouldn't want this as default behavior?

Are there any things to be aware of when setting activate_all_roles_on_login to true?

Answer 1

They're called roles for a reason. By the SQL3 standard, database users are supposed to be able to adopt only one role at a time. Like when a live stage play has too few actors, they need to play multiple characters by throwing on a different coat or a hat. The idea is that a user shouldn't have the union of the privileges of all roles they are permitted to use. They should use SET ROLE to choose one at a time.

This can be practical, for example a "backup" role may allow read-only access to data for purposes of making backups. You wouldn't want a user to have read-write access while using backup tools. But at other times, the same user doing transactions may need read-write access, though only to a limited set of tables. In some ways, this is like the principles behind SOX compliant security: No single user should have unlimited access by themselves.

MySQL does have a feature to allow a user to adopt multiple roles simultaneously. For many applications, this would be perfectly reasonable, which is no doubt why MySQL added this as an extension to standard SQL. If this is ordinary for all users at your site, you may therefore choose to enable activate_all_roles_on_login. But it's not standard SQL, so it's not enabled by default.