0
# Database Security Services
1
2
Database-backed authentication and authorization using JNDI DataSources with configurable user/role schema and full Jetty security integration for enterprise applications.
3
4
## Capabilities
5
6
### Database Login Service
7
8
Comprehensive database-backed login service that obtains user credentials and role information via JNDI DataSource with configurable table schema and SQL generation.
9
10
```java { .api }
11
class DataSourceLoginService extends AbstractLoginService {
12
// Constructors
13
DataSourceLoginService();
14
DataSourceLoginService(String name);
15
DataSourceLoginService(String name, IdentityService identityService);
16
17
// JNDI DataSource configuration
18
void setJndiName(String jndi);
19
String getJndiName();
20
21
// Server integration
22
void setServer(Server server);
23
Server getServer();
24
25
// Database schema management
26
void setCreateTables(boolean createTables);
27
boolean getCreateTables();
28
29
// User table configuration
30
void setUserTableName(String name);
31
String getUserTableName();
32
void setUserTableKey(String tableKey);
33
String getUserTableKey();
34
void setUserTableUserField(String tableUserField);
35
String getUserTableUserField();
36
void setUserTablePasswordField(String tablePasswordField);
37
String getUserTablePasswordField();
38
39
// Role table configuration
40
void setRoleTableName(String tableName);
41
String getRoleTableName();
42
void setRoleTableKey(String tableKey);
43
String getRoleTableKey();
44
void setRoleTableRoleField(String tableRoleField);
45
String getRoleTableRoleField();
46
47
// User-role junction table configuration
48
void setUserRoleTableName(String roleTableName);
49
String getUserRoleTableName();
50
void setUserRoleTableUserKey(String roleTableUserKey);
51
String getUserRoleTableUserKey();
52
void setUserRoleTableRoleKey(String roleTableRoleKey);
53
String getUserRoleTableRoleKey();
54
55
// Core authentication methods
56
UserPrincipal loadUserInfo(String username);
57
List<RolePrincipal> loadRoleInfo(UserPrincipal user);
58
59
// Database initialization
60
void initDb() throws NamingException, SQLException;
61
}
62
```
63
64
### Database User Principal
65
66
Enhanced user principal that includes database key information for efficient role lookups and database operations.
67
68
```java { .api }
69
static class DBUserPrincipal extends UserPrincipal {
70
// Get database primary key for this user
71
int getKey();
72
}
73
```
74
75
## Configuration Patterns
76
77
### Basic Configuration
78
79
```java
80
// Create and configure basic database login service
81
DataSourceLoginService loginService = new DataSourceLoginService("MyRealm");
82
83
// Configure JNDI DataSource
84
loginService.setJndiName("jdbc/SecurityDB");
85
86
// Use default table schema (users, roles, user_roles tables)
87
loginService.setCreateTables(false); // Don't auto-create tables
88
89
// Add to server
90
Server server = new Server();
91
server.addBean(loginService);
92
```
93
94
### Custom Schema Configuration
95
96
```java
97
// Configure custom database schema
98
DataSourceLoginService loginService = new DataSourceLoginService("CustomRealm");
99
loginService.setJndiName("jdbc/AuthDB");
100
101
// Custom user table
102
loginService.setUserTableName("app_users");
103
loginService.setUserTableKey("user_id");
104
loginService.setUserTableUserField("username");
105
loginService.setUserTablePasswordField("password_hash");
106
107
// Custom role table
108
loginService.setRoleTableName("app_roles");
109
loginService.setRoleTableKey("role_id");
110
loginService.setRoleTableRoleField("role_name");
111
112
// Custom user-role junction table
113
loginService.setUserRoleTableName("user_role_assignments");
114
loginService.setUserRoleTableUserKey("user_id");
115
loginService.setUserRoleTableRoleKey("role_id");
116
117
// Enable automatic table creation (for development)
118
loginService.setCreateTables(true);
119
```
120
121
### Complete Security Setup
122
123
```java
124
// Set up complete database security for Jetty server
125
Server server = new Server(8080);
126
127
// 1. Create and configure login service
128
DataSourceLoginService loginService = new DataSourceLoginService("SecureRealm");
129
loginService.setJndiName("jdbc/SecurityDB");
130
loginService.setServer(server);
131
132
// Custom schema configuration
133
loginService.setUserTableName("users");
134
loginService.setUserTableUserField("username");
135
loginService.setUserTablePasswordField("password");
136
loginService.setRoleTableName("roles");
137
loginService.setRoleTableRoleField("rolename");
138
loginService.setUserRoleTableName("user_roles");
139
140
server.addBean(loginService);
141
142
// 2. Create security handler
143
SecurityHandler security = new SecurityHandler();
144
security.setLoginService(loginService);
145
146
// 3. Configure authentication method
147
FormAuthenticator authenticator = new FormAuthenticator();
148
authenticator.setLoginPage("/login.jsp");
149
authenticator.setErrorPage("/login-error.jsp");
150
security.setAuthenticator(authenticator);
151
152
// 4. Define security constraints
153
Constraint constraint = new Constraint();
154
constraint.setName("auth");
155
constraint.setAuthenticate(true);
156
constraint.setRoles(new String[] {"user", "admin"});
157
158
ConstraintMapping mapping = new ConstraintMapping();
159
mapping.setPathSpec("/secure/*");
160
mapping.setConstraint(constraint);
161
security.setConstraintMappings(Arrays.asList(mapping));
162
163
// 5. Create webapp with security
164
WebAppContext webapp = new WebAppContext();
165
webapp.setContextPath("/");
166
webapp.setWar("myapp.war");
167
webapp.setSecurityHandler(security);
168
169
server.setHandler(webapp);
170
```
171
172
## Database Schema Requirements
173
174
### Default Schema
175
176
The DataSourceLoginService expects the following default table structure:
177
178
```sql
179
-- Users table
180
CREATE TABLE users (
181
id INTEGER PRIMARY KEY,
182
username VARCHAR(100) NOT NULL UNIQUE,
183
password VARCHAR(100) NOT NULL
184
);
185
186
-- Roles table
187
CREATE TABLE roles (
188
id INTEGER PRIMARY KEY,
189
rolename VARCHAR(100) NOT NULL UNIQUE
190
);
191
192
-- User-Role junction table
193
CREATE TABLE user_roles (
194
user_id INTEGER NOT NULL,
195
role_id INTEGER NOT NULL,
196
PRIMARY KEY (user_id, role_id),
197
FOREIGN KEY (user_id) REFERENCES users(id),
198
FOREIGN KEY (role_id) REFERENCES roles(id)
199
);
200
```
201
202
### Custom Schema Example
203
204
```sql
205
-- Custom table names and fields
206
CREATE TABLE app_users (
207
user_id INTEGER PRIMARY KEY,
208
username VARCHAR(50) NOT NULL UNIQUE,
209
password_hash VARCHAR(255) NOT NULL,
210
email VARCHAR(100),
211
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
212
);
213
214
CREATE TABLE app_roles (
215
role_id INTEGER PRIMARY KEY,
216
role_name VARCHAR(50) NOT NULL UNIQUE,
217
description VARCHAR(200)
218
);
219
220
CREATE TABLE user_role_assignments (
221
user_id INTEGER NOT NULL,
222
role_id INTEGER NOT NULL,
223
assigned_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
224
PRIMARY KEY (user_id, role_id),
225
FOREIGN KEY (user_id) REFERENCES app_users(user_id),
226
FOREIGN KEY (role_id) REFERENCES app_roles(role_id)
227
);
228
```
229
230
## Integration Examples
231
232
### JNDI DataSource Setup
233
234
```java
235
// Set up JNDI DataSource for security database
236
DataSource securityDataSource = createSecurityDataSource();
237
Resource securityDB = new Resource("jdbc/SecurityDB", securityDataSource);
238
239
// Alternative: scoped to specific webapp
240
Resource webappSecurityDB = new Resource(webapp, "jdbc/SecurityDB", securityDataSource);
241
```
242
243
### Programmatic User Management
244
245
```java
246
// Example of programmatic user/role management
247
// (Note: This requires direct database access, not through DataSourceLoginService)
248
249
DataSource ds = (DataSource) new InitialContext().lookup("jdbc/SecurityDB");
250
try (Connection conn = ds.getConnection()) {
251
// Create user
252
PreparedStatement createUser = conn.prepareStatement(
253
"INSERT INTO users (username, password) VALUES (?, ?)");
254
createUser.setString(1, "newuser");
255
createUser.setString(2, hashedPassword);
256
createUser.executeUpdate();
257
258
// Get user ID
259
PreparedStatement getUser = conn.prepareStatement(
260
"SELECT id FROM users WHERE username = ?");
261
getUser.setString(1, "newuser");
262
ResultSet rs = getUser.executeQuery();
263
int userId = rs.next() ? rs.getInt(1) : -1;
264
265
// Assign role
266
PreparedStatement assignRole = conn.prepareStatement(
267
"INSERT INTO user_roles (user_id, role_id) " +
268
"SELECT ?, id FROM roles WHERE rolename = ?");
269
assignRole.setInt(1, userId);
270
assignRole.setString(2, "user");
271
assignRole.executeUpdate();
272
}
273
```
274
275
### Authentication Testing
276
277
```java
278
// Test authentication programmatically
279
DataSourceLoginService loginService = new DataSourceLoginService("TestRealm");
280
loginService.setJndiName("jdbc/SecurityDB");
281
282
// Initialize the service
283
loginService.start();
284
285
// Test user authentication
286
UserPrincipal user = loginService.login("testuser", "password");
287
if (user != null) {
288
System.out.println("Authentication successful for: " + user.getName());
289
290
// Check roles
291
if (loginService.getUserPrincipal("testuser") instanceof DataSourceLoginService.DBUserPrincipal) {
292
DataSourceLoginService.DBUserPrincipal dbUser =
293
(DataSourceLoginService.DBUserPrincipal) user;
294
System.out.println("User database key: " + dbUser.getKey());
295
}
296
297
// Get user roles
298
List<RolePrincipal> roles = loginService.loadRoleInfo(user);
299
for (RolePrincipal role : roles) {
300
System.out.println("User has role: " + role.getName());
301
}
302
} else {
303
System.out.println("Authentication failed");
304
}
305
```
306
307
### Integration with Web Security
308
309
```java
310
// Complete web security configuration
311
WebAppContext webapp = new WebAppContext();
312
313
// Set up database login service
314
DataSourceLoginService loginService = new DataSourceLoginService("WebRealm");
315
loginService.setJndiName("jdbc/SecurityDB");
316
317
// Configure form authentication
318
SecurityHandler security = new SecurityHandler();
319
security.setLoginService(loginService);
320
321
FormAuthenticator authenticator = new FormAuthenticator("/login", "/login?error=1");
322
security.setAuthenticator(authenticator);
323
324
// Define role-based constraints
325
Constraint adminConstraint = new Constraint();
326
adminConstraint.setName("admin");
327
adminConstraint.setAuthenticate(true);
328
adminConstraint.setRoles(new String[] {"admin"});
329
330
ConstraintMapping adminMapping = new ConstraintMapping();
331
adminMapping.setPathSpec("/admin/*");
332
adminMapping.setConstraint(adminConstraint);
333
334
Constraint userConstraint = new Constraint();
335
userConstraint.setName("user");
336
userConstraint.setAuthenticate(true);
337
userConstraint.setRoles(new String[] {"user", "admin"});
338
339
ConstraintMapping userMapping = new ConstraintMapping();
340
userMapping.setPathSpec("/user/*");
341
userMapping.setConstraint(userConstraint);
342
343
security.setConstraintMappings(Arrays.asList(adminMapping, userMapping));
344
345
webapp.setSecurityHandler(security);
346
```
347
348
## Error Handling and Monitoring
349
350
### Database Connection Issues
351
352
```java
353
DataSourceLoginService loginService = new DataSourceLoginService("MyRealm");
354
loginService.setJndiName("jdbc/SecurityDB");
355
356
try {
357
loginService.initDb();
358
System.out.println("Database connection successful");
359
} catch (NamingException e) {
360
System.err.println("JNDI lookup failed: " + e.getMessage());
361
// Handle missing DataSource
362
} catch (SQLException e) {
363
System.err.println("Database error: " + e.getMessage());
364
// Handle database connectivity issues
365
}
366
```
367
368
### Authentication Monitoring
369
370
```java
371
// Custom login service with logging
372
DataSourceLoginService loginService = new DataSourceLoginService("MonitoredRealm") {
373
@Override
374
public UserPrincipal loadUserInfo(String username) {
375
long start = System.currentTimeMillis();
376
try {
377
UserPrincipal user = super.loadUserInfo(username);
378
long duration = System.currentTimeMillis() - start;
379
380
if (user != null) {
381
LOG.info("User '{}' authentication successful in {}ms", username, duration);
382
} else {
383
LOG.warn("User '{}' authentication failed in {}ms", username, duration);
384
}
385
386
return user;
387
} catch (Exception e) {
388
long duration = System.currentTimeMillis() - start;
389
LOG.error("User '{}' authentication error in {}ms: {}", username, duration, e.getMessage());
390
throw e;
391
}
392
}
393
};
394
```
395
396
## Performance Considerations
397
398
### Connection Pooling
399
400
```java
401
// Use connection pooling for better performance
402
HikariDataSource hikariDS = new HikariDataSource();
403
hikariDS.setJdbcUrl("jdbc:postgresql://localhost:5432/security");
404
hikariDS.setUsername("security_user");
405
hikariDS.setPassword("password");
406
hikariDS.setMaximumPoolSize(10);
407
hikariDS.setMinimumIdle(2);
408
hikariDS.setConnectionTimeout(30000);
409
410
Resource securityDB = new Resource("jdbc/SecurityDB", hikariDS);
411
412
// Configure login service
413
DataSourceLoginService loginService = new DataSourceLoginService("PooledRealm");
414
loginService.setJndiName("jdbc/SecurityDB");
415
```
416
417
### Caching Strategies
418
419
```java
420
// For high-traffic applications, consider implementing caching
421
// Note: This would be custom implementation extending DataSourceLoginService
422
423
public class CachedDataSourceLoginService extends DataSourceLoginService {
424
private final Map<String, UserPrincipal> userCache = new ConcurrentHashMap<>();
425
private final Map<String, List<RolePrincipal>> roleCache = new ConcurrentHashMap<>();
426
427
@Override
428
public UserPrincipal loadUserInfo(String username) {
429
// Check cache first
430
UserPrincipal cached = userCache.get(username);
431
if (cached != null && isCacheValid(cached)) {
432
return cached;
433
}
434
435
// Load from database
436
UserPrincipal user = super.loadUserInfo(username);
437
if (user != null) {
438
userCache.put(username, user);
439
}
440
return user;
441
}
442
443
// Implement cache invalidation, TTL, etc.
444
}
445
```