Building a Fortress: A Deep Dive into a Secure, Dynamic Database Exploration Platform

How do you give business users the power to explore data across multiple databases without writing a new dashboard for every request or compromising on security? This was the central challenge I tackled while building "Insight," a dynamic data analysis platform. Instead of building a rigid reporting tool, I created a flexible backend that can connect to virtually any SQL database, understand its structure on the fly, and provide a secure API for querying and visualization.

The primary goal was to query tables we've never seen before. A standard approach using Spring Data JPA with @Entity classes for each table was out of the question. The solution was a three-part dynamic data access layer.

The Metadata Inspector

I designed a DatabaseInspector interface using the Strategy pattern. This allows the application to have different "inspector" implementations for different SQL dialects (PostgreSQL, SQL Server, etc.). When a new database is connected, the appropriate inspector is chosen to read the system catalogs and persist the schemas, tables, columns, and relationships into our own metadata database. This discovery runs asynchronously, so the UI remains responsive.

The Dynamic Query DTO

I created a DynamicQuery Plain Old Java Object (POJO). This object acts as a language-agnostic description of a query, capturing everything from SELECT columns and WHERE conditions to sorting and pagination. The frontend can construct this object without ever writing a line of SQL.

The Query Builder

This is the heart of the engine. The QueryBuilder takes the DynamicQuery object and, knowing the target database dialect, translates it into a native, parameterized SQL statement. It intelligently quotes identifiers and formats clauses correctly, preventing SQL injection and ensuring compatibility.

Giving users this much power demands a rigorous security model. I implemented a "defense-in-depth" strategy with four distinct layers:

The Main Gate (Authentication)

All access is protected by Spring Security, configured to use OAuth2/OIDC against Microsoft Entra ID for enterprise-grade authentication. For stateless API calls, it validates JWTs, ensuring every request has a verified identity.

The Walls (API Authorization)

Standard API endpoints are protected with role-based access control (RBAC). Using @PreAuthorize("hasRole('ADMIN')"), we ensure only users with the right roles can perform sensitive operations like creating new database connections or managing users.

The Room Keys (Resource Permissions)

This is where it gets interesting. A user might be allowed to use the query API, but what tables can they query? I built a PermissionService that manages fine-grained permissions. It links users or groups to specific resources (e.g., CONNECTION:conn1, TABLE:conn1:public.users) with specific rights (READ, EXPORT). Before any query is built, the service checks if the user has the required permission for that specific table.

The Redacted Files (Row-Level Security)

This is the deepest layer of our fortress. Even if a user has READ access to the orders table, should they see all orders? Probably not. I developed a custom Row-Level Security (RLS) engine. Administrators can write simple policy definitions in a DSL, and when a user queries a table, the RlsPolicyCompiler intercepts the request, substitutes placeholders with the logged-in user's attributes, and dynamically injects additional WHERE clauses into the final SQL query.

With great power comes the need for great accountability. A central AuditService is hooked into every critical part of the application. Every data query, export, permission change, and login attempt is recorded in an audit log, providing a complete, immutable record of all activity on the platform.

Building the Insight backend was a fantastic journey into creating a truly dynamic and secure data platform. By abstracting away database specifics and building a layered, policy-driven security model, we created a tool that is both powerful for users and safe for the organization.