The experience of using the cloud in managing database access control in a software project

Published on November 12, 2015

The experience of using the cloud in managing database access control in a software project

    We are very grateful for the preparation of the article to Nikolai Sinitsin, senior software engineer at Aplan , for his help in writing this article. The rest of our Azure related articles can be found on the azureweek tag .

    Once in one of the projects we set a task, the conditions of which were to ensure that the software needed to access various databases with access control - literally, whoever has a license should give access, otherwise block access. In addition, it was necessary to be able to change the connection to another database, and so that the software knew that the address had changed. Several ways were found to do this, but in the end they came up with the option of using Azure cloud services - about how the decision was made and why and how we used the cloud - read under the cat.

    At first we thought of taking the obvious path — there are databases, users who have “licenses” are set up on each database. They solved the access problem, but, as you probably understand, it’s very inconvenient to manage such a system - add, delete users. In order to solve the problem of changing servers, it would be necessary to write some conditional service, which would also store users who have access. And everyone would know the address where the database server is located.

    This method has several disadvantages:
    • Need to write a service;
    • There is no single user repository with access to the system;
    • It is difficult to maintain the relevance of user data on various databases.

    Let's go through the minuses.

    Minus: You need to write a service .

    “What service did we need,” you ask. A service for managing access control to databases for software products deployed on various machines. Writing services is not bad. But, when it is necessary to write a service from scratch, it is possible to catch various errors, including the most complex are human. This is especially true when you write an access control and role distribution service. One mistake - and instead of giving one user the right to a resource, one may encounter that the rights are given to a large number. The price of the error is very high.

    Minus: There is no single repository of users with access to the system .

    In addition to the fact that there should be a service that allows you to provide different access, you need a single user database and a management manager that will easily allow you to manage this database. To write yourself from the very beginning = spend a lot of time.

    Minus: It is difficult to maintain the relevance of user data on various databases .

    If we use the option in which users are logged on to each database, we have the problem of synchronizing several databases with each other.

    Due to the fact that there are described problems, it was decided to look at cloud services that will solve the tasks and minimize or completely get rid of the above disadvantages + there was the possibility of a transition to a cloud database and site deployment in the cloud.

    Let's take a brief look at the cloud services that were analyzed and used to solve the problem:

    Azure Active Directory ( AAD or Azure AD )- Multi-user cloud directory and identity management service. It is similar to working with the Active directory, which comes with Windows Server. However, AAD is not intended for users to work in the local infrastructure of the company, but when working with cloud applications (for example, Azure Key Vault). Using this service, we solve the problems of “there is no single user repository with access to the system” and “it is difficult to maintain the relevance of user data on various databases. " Recently it was announced that AAD will support the ability to deploy domains.

    Azure Key Vault - HMS as a Service  ( Hardware security module ). HMS is dedicated hardware that allows store, manage keys / secrets and encrypt / decrypt, put / verify signatures in the  safest way and fast enough (specific hardware sharpened for encryption, according to the statements, works quickly, but how much or what measurements were taken, there is no information). KV was previously known as BYOK (bring-your-own-key). ( Link to the article ). Using this service, you can store secrets related to access to a particular database (login, password, address, type of database, etc.) Thus, users authorized using AAD receive Token with the help of which they gain access to the secret. And the program, based on this data, connects to the desired database.

    Using Azure Active Directory and Azure Key Vault

    The diagram above shows that access control, a single user repository and management manager are transferred to the side of cloud services such as AAD and Azure Key Vault. The interaction scheme is very simple. Each software user knows the username and password that the service administrator gave him. With it, the program logs into AAD and receives an authorization token, through which the software product gets access to the Azure Key Vault service in which secrets are stored. Further using secrets, the software establishes a connection to the database.

    If you need to remove access to the database, you can remove the user from AAD and generate a new password to the database. The rest of the systems, having lost the connection, will request a secret again and establish a connection.
    If we change the address or location of the database, we only need to change the information in secret and the system will automatically connect to another database that comes in the information from Key Vault.

    The advantages of this approach:
    • Uses a single entry point and access control . This plus allows you to easily manage the entire system of interaction with the database. There is no need to write and check the services for errors, this is all done for us by the Microsoft employees and the user community using these services.
    • Ease of expansion , since we can switch between databases easily without changing anything. We can change from a database deployed on our host to a database in the cloud.
    • Convenient access control interface . There is no need to spend time and money writing an access control interface. Everything is done and measured out by time.

    Among the minuses, one can be distinguished - in this implementation, after deleting the user, we need to change the password to the database. And this is not very good, since the system is already being used.

    That was the experience of using the cloud to solve a specific problem. Thanks for attention!