Story

As a auditor
I want to be able to brows all db requests and the executed queries
So that I can check which statements have been executed

Implementation Details

• It should be possible to search for Requests (Requestor, Creation Time, Approver)
• It should be possible to search by statement executions (Fuzzy search of statement string, execution time, user)
• For requests it should it should be possible to get all statements which were executed with the given request
• For statement executions it should be possible to see the request which belongs to the given statement execution

Story

As a user
I want to be able to run SQL statements in the context in the context of a given request
So that I can perform the needed operations on the DB

Implementation Details

• Request should only be possible as long as the request is valid
• The queries should be executed against the DB from the request
• The queries should be restricted to the defined restrictions of the request (read/write, table set)
• All queries should be logged in the database for auditing purposes including a link to the request and the executing user
• The result set should be limited by a configurable limit
• The result set should be rendered as HTML table
• The result set should be downloadable as CSV

Story

As a user
I want to be able request access to a database
So that I can run queries against the requested database

Implementation Details

• It should be possible to select read/write access
• It should be possible to write a comment
• It should be possible to select the target database
• It should be possible to select the table set which will are required in the request

The user interface of the Audited SQL Console should be implemented with Angular with TypeScript. The build should be integrated seamlessly into the Gradle build.

Story

As a system operator
I want that all sensitive data such as the database connection passwords are stored encrypted
So that the sensitive data cannot be read by unauthorized users.

Technical Background

The software has to be able to connect to different database endpoints. Therefor the username and password of the given system is needed so that the connection can be established.
However such sensitive data should be protected from unauthorized access. Therefor the data should be stored encrypted in the database.
For the en/decryption an asynchronous encryption algorithm should be used. The key material has to be configured in the application. The password of the key material has to be entered once after system startup and should be stored in memory.

h1.Story
As a engineer
I want to implement the software with an microservices architecture
So that we can easily extend new features by adding new services

h1.Technical Details
With the requirement of full audit log and the idea of implementing this with event sourcing microservices as an design and architecture methodology fits perfectly for this project.
As security is needed to work between all services we have to migrate the existing login with OAuth2

Story

As a approver
I want to be able to approve or reject DB Requests
So that I can grant or reject access to the database

Implementation Details

• All request details should be rendered in the UI
• The requesting user should also be rendered in the UI
• An optional comment should be possible to document why request was granted or rejected
• The approval should be stored in the DB for auditing purposes
• A request should have a limited validity which starts at the time of approval

Story

As a developer
I want to get continues integration including code coverage
So that I get fast feedback when committing changes

Story

As a auditor
I expect that all operations in system a stored as an event
So that I can trace all changes made in the system

Technical Background

The overall system should be implemented with the methodology. Each change should be reflected in the event store. This story is about building the event store which is capable to hold all system events.
The events should be stored in Cassandra. Event listeners should be notified when the event has been persisted.

Story

As a user
I want to be able to login to the application
So that I can create DB access requests or to approve DB access request

Technical Details

• Authentication should be against should be first implemented again an internal user store (either in the database or in a file). Other user/role providers can be implemented at a later point in time.
• There should be the three roles USER, ADMIN, APPROVER, AUDITOR
• Password should be hashed with a secure algorithm such as bcrypt (< 10 iterations)