How to keep database user permissions under source control

Question

I use liquibase for source control of our database. I put there changes to the schema, static data and procedures. So far I've kept permissions out of it; we have just four users with very well defined permissions that don't change. Now, for a new application, I need to create a new user and grant it table and column-specific permissions as they're demanded. Today it needs select permission for table A, tomorrow also write permission to column description of table B.

I think I definitely should somehow keep track of these permission changes. I could keep them also under the same liquibase project, but I'm not sure that's a good idea. Permission changes are in a different level to the other ones, plus, I would need to grant the liquibase user (the one I run liquibase under) permission to grant permissions, which I'd prefer that only the root user has.

Should I then put theses changes in the same database source control project? In a different one? What are some other alternatives?

Answer 1

I am not a liquibase expert, but a short look into the docs revealed it has a feature called "SQL output", which makes it possible to generate scripts for updating the DB. Those scripts can be handed over to a DBA with the required permissions.

So if you want permission changes to be applied only by a root user (but schema changes still be applied by your "liquibase user"), you simply have to make sure you can separate them from the changesets which are allowed for your "liquibase user". I guess you can utilize the context feature of Liquibase for this. Define a "root" context for this purpose and tag your permission changesets with that context. Then let Liquibase produce sql output specificially for this context (see the command line options how to do this), and pass the result over to a dba with the required root permissions.

That way, it should allow you to keep permission changes in source control under the same liquibase project as schema changes without any need for granting the liquibase user additional permissions.

Answer 2

Good question. There is no single solution for your problem. Specially because you didn't stated if there is a security issue. Most environments have different passwords.

The best way I can imagine even if I have done it myself is to create scripts with parameters like the password from the environment. I believe you may be able to do it with changelog parameters.

So liquibase should manage scripts to change permissions it the two possible ways:

• Incrementally between versions
• For a clean system from scratch.

Alternatively you may at least document in a text file which permission changes are changed between versions in a manual script (without the passwords). This will be really bad for continuous delivery but at least it gets documented what changes should be made between versions.

Answer 3

The fact that not everyone who is allowed to access source code has sufficient privileges to execute it under all circumstances is no reason for giving up good practices for data curation. If your project uses a specific database structure, and certain access rights are necessary to participate, then a script that sets these access rights should definitely be part of the project that defines interaction with that database.

Just as adding a new user is a privileged task, granting them rights is a privileged task, but even the admin will have an easier time if the administrative scripts for day-to-day maintenance of the FOOBAR application are in the FOOBAR project and not in some separate FOOBAR_administration project.