DBA

Deadbolt: User Management for Databases

Not too long ago, I posted about a project I created for Careerbuilder called CloudSeed. The goal of the project was to make life easier for my team as we began our rapidly-accelerating move to the AWS cloud space. The project has been largely successful for our team, and as such, we open-sourced it with the hopes that others may find it equally useful. Now, I present to you another tool with just such an origin story:Deadbolt

Deadbolt was once again the product of trying to make life easier for our DBAs. Most of them were accustomed to working with Microsoft SQL Server, but now had to adjust to primarily using MySQL and Amazon Aurora. One of their most noted pain points was the lack of Windows Authentication. With Windows Authentication, they never needed to worry about bootstrapping users on to a new system, because the Active Directory (AD) did that for them! Without it, users had to be manually created and granted access for each system. Since “manually” is a dirty word on our team, I set to work.

We first researched how other companies got around this issue. It seemed there was a common LDAP package for MySQL that many teams had come to rely upon, so we began digging deeper in to that solution. Eventually we hit a wall in the road. AWS does not allow for AD or LDAP authentication for RDS resources. This was a major blow as we had just finished migrating the last EC2 MySQL instances to RDS. We had found a problem with no reliable solution, so it was time for a homegrown one.

The Plan

First off, we needed a management portal. This would allow us to add or remove users, assign them to databases, and assign permissions. We decided on a user to group to systems approach, allowing us to create groups representing functional teams which had access to the systems owned by that team.

Secondly, we needed a way to create the users on different systems with the same password. We added a password portal to the system with the ability to hash a password in each flavor’s preferred method and construct the appropriate user creation/update query.

Lastly, we needed a way to store these hashed passwords securely, so that any new systems would be auto-populated with users. The hashes are not secure enough themselves, so they need to be re-encrypted before storage.

The Execution

I am a sucker for the MEAN stack (with MySQL in place of Mongo) so I started building a Node API with Express. The API handles all authentication, management, and password functionality server side. Users can then interact with the API through a portal created in Angular and served via Express. The portal looks slightly different depending on if the logged in user is a  full admin, a group admin, or a developer. Developers can only use the portal to reset their password, Group Admins can assign new users and permissions to users in the groups they administrate, and “Full Admins” can add and remove systems, groups, and users.

Deadbolt Portal

The appearance of the portal to a full admin

All actions taken through the API or portal are recorded into a history table on the backing database. This database also holds the user information, hashed passwords, group mappings, and system information necessary for propagating the user to the databases. All sensitive info is encrypted as per the last point in the plan using Amazon KMS or AES depending on how the API is configured at set-up.

Try it yourself!

This tool has made managing a growing number of RDS systems a breeze, since we no longer have to worry about users. We can add a system to Deadbolt and within 5 seconds, all the user accounts are created and given appropriate access. If this sounds like something you could use, please give it a try. Its free and open source, and I will happily answer any questions you may have.

Deadbolt can be found on the Careerbuilder Open Source Org here:

https://github.com/careerbuilder/DeadBolt

Advertisements