Skip to content

Commit b217051

Browse files
araujoguiaddaleax
araujogui
authored and
addaleax
committed
sqlite: create authorization api
PR-URL: #59928 Reviewed-By: Anna Henningsen <[email protected]> Reviewed-By: Edy Silva <[email protected]>
1 parent 4a9a022 commit b217051

4 files changed

Lines changed: 694 additions & 0 deletions

File tree

‎doc/api/sqlite.md‎

Lines changed: 253 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -318,6 +318,81 @@ added:
318318
This method is used to create SQLite user-defined functions. This method is a
319319
wrapper around [`sqlite3_create_function_v2()`][].
320320

321+
### `database.setAuthorizer(callback)`
322+
323+
<!-- YAML
324+
added: REPLACEME
325+
-->
326+
327+
* `callback` {Function|null} The authorizer function to set, or `null` to
328+
clear the current authorizer.
329+
330+
Sets an authorizer callback that SQLite will invoke whenever it attempts to
331+
access data or modify the database schema through prepared statements.
332+
This can be used to implement security policies, audit access, or restrict certain operations.
333+
This method is a wrapper around [`sqlite3_set_authorizer()`][].
334+
335+
When invoked, the callback receives five arguments:
336+
337+
* `actionCode` {number} The type of operation being performed (e.g.,
338+
`SQLITE_INSERT`, `SQLITE_UPDATE`, `SQLITE_SELECT`).
339+
* `arg1` {string|null} The first argument (context-dependent, often a table name).
340+
* `arg2` {string|null} The second argument (context-dependent, often a column name).
341+
* `dbName` {string|null} The name of the database.
342+
* `triggerOrView` {string|null} The name of the trigger or view causing the access.
343+
344+
The callback must return one of the following constants:
345+
346+
* `SQLITE_OK` - Allow the operation.
347+
* `SQLITE_DENY` - Deny the operation (causes an error).
348+
* `SQLITE_IGNORE` - Ignore the operation (silently skip).
349+
350+
```cjs
351+
const { DatabaseSync, constants } = require('node:sqlite');
352+
const db = new DatabaseSync(':memory:');
353+
354+
// Set up an authorizer that denies all table creation
355+
db.setAuthorizer((actionCode) => {
356+
if (actionCode === constants.SQLITE_CREATE_TABLE) {
357+
return constants.SQLITE_DENY;
358+
}
359+
return constants.SQLITE_OK;
360+
});
361+
362+
// This will work
363+
db.prepare('SELECT 1').get();
364+
365+
// This will throw an error due to authorization denial
366+
try {
367+
db.exec('CREATE TABLE blocked (id INTEGER)');
368+
} catch (err) {
369+
console.log('Operation blocked:', err.message);
370+
}
371+
```
372+
373+
```mjs
374+
import { DatabaseSync, constants } from 'node:sqlite';
375+
const db = new DatabaseSync(':memory:');
376+
377+
// Set up an authorizer that denies all table creation
378+
db.setAuthorizer((actionCode) => {
379+
if (actionCode === constants.SQLITE_CREATE_TABLE) {
380+
return constants.SQLITE_DENY;
381+
}
382+
return constants.SQLITE_OK;
383+
});
384+
385+
// This will work
386+
db.prepare('SELECT 1').get();
387+
388+
// This will throw an error due to authorization denial
389+
try {
390+
db.exec('CREATE TABLE blocked (id INTEGER)');
391+
} catch (err) {
392+
console.log('Operation blocked:', err.message);
393+
}
394+
```
395+
321396
### `database.isOpen`
322397

323398
<!-- YAML
@@ -1048,6 +1123,182 @@ resolution handler passed to [`database.applyChangeset()`][]. See also
10481123
</tr>
10491124
</table>
10501125

1126+
#### Authorization constants
1127+
1128+
The following constants are used with the [`database.setAuthorizer()`][] method.
1129+
1130+
##### Authorization result codes
1131+
1132+
One of the following constants must be returned from the authorizer callback
1133+
function passed to [`database.setAuthorizer()`][].
1134+
1135+
<table>
1136+
<tr>
1137+
<th>Constant</th>
1138+
<th>Description</th>
1139+
</tr>
1140+
<tr>
1141+
<td><code>SQLITE_OK</code></td>
1142+
<td>Allow the operation to proceed normally.</td>
1143+
</tr>
1144+
<tr>
1145+
<td><code>SQLITE_DENY</code></td>
1146+
<td>Deny the operation and cause an error to be returned.</td>
1147+
</tr>
1148+
<tr>
1149+
<td><code>SQLITE_IGNORE</code></td>
1150+
<td>Ignore the operation and continue as if it had never been requested.</td>
1151+
</tr>
1152+
</table>
1153+
1154+
##### Authorization action codes
1155+
1156+
The following constants are passed as the first argument to the authorizer
1157+
callback function to indicate what type of operation is being authorized.
1158+
1159+
<table>
1160+
<tr>
1161+
<th>Constant</th>
1162+
<th>Description</th>
1163+
</tr>
1164+
<tr>
1165+
<td><code>SQLITE_CREATE_INDEX</code></td>
1166+
<td>Create an index</td>
1167+
</tr>
1168+
<tr>
1169+
<td><code>SQLITE_CREATE_TABLE</code></td>
1170+
<td>Create a table</td>
1171+
</tr>
1172+
<tr>
1173+
<td><code>SQLITE_CREATE_TEMP_INDEX</code></td>
1174+
<td>Create a temporary index</td>
1175+
</tr>
1176+
<tr>
1177+
<td><code>SQLITE_CREATE_TEMP_TABLE</code></td>
1178+
<td>Create a temporary table</td>
1179+
</tr>
1180+
<tr>
1181+
<td><code>SQLITE_CREATE_TEMP_TRIGGER</code></td>
1182+
<td>Create a temporary trigger</td>
1183+
</tr>
1184+
<tr>
1185+
<td><code>SQLITE_CREATE_TEMP_VIEW</code></td>
1186+
<td>Create a temporary view</td>
1187+
</tr>
1188+
<tr>
1189+
<td><code>SQLITE_CREATE_TRIGGER</code></td>
1190+
<td>Create a trigger</td>
1191+
</tr>
1192+
<tr>
1193+
<td><code>SQLITE_CREATE_VIEW</code></td>
1194+
<td>Create a view</td>
1195+
</tr>
1196+
<tr>
1197+
<td><code>SQLITE_DELETE</code></td>
1198+
<td>Delete from a table</td>
1199+
</tr>
1200+
<tr>
1201+
<td><code>SQLITE_DROP_INDEX</code></td>
1202+
<td>Drop an index</td>
1203+
</tr>
1204+
<tr>
1205+
<td><code>SQLITE_DROP_TABLE</code></td>
1206+
<td>Drop a table</td>
1207+
</tr>
1208+
<tr>
1209+
<td><code>SQLITE_DROP_TEMP_INDEX</code></td>
1210+
<td>Drop a temporary index</td>
1211+
</tr>
1212+
<tr>
1213+
<td><code>SQLITE_DROP_TEMP_TABLE</code></td>
1214+
<td>Drop a temporary table</td>
1215+
</tr>
1216+
<tr>
1217+
<td><code>SQLITE_DROP_TEMP_TRIGGER</code></td>
1218+
<td>Drop a temporary trigger</td>
1219+
</tr>
1220+
<tr>
1221+
<td><code>SQLITE_DROP_TEMP_VIEW</code></td>
1222+
<td>Drop a temporary view</td>
1223+
</tr>
1224+
<tr>
1225+
<td><code>SQLITE_DROP_TRIGGER</code></td>
1226+
<td>Drop a trigger</td>
1227+
</tr>
1228+
<tr>
1229+
<td><code>SQLITE_DROP_VIEW</code></td>
1230+
<td>Drop a view</td>
1231+
</tr>
1232+
<tr>
1233+
<td><code>SQLITE_INSERT</code></td>
1234+
<td>Insert into a table</td>
1235+
</tr>
1236+
<tr>
1237+
<td><code>SQLITE_PRAGMA</code></td>
1238+
<td>Execute a PRAGMA statement</td>
1239+
</tr>
1240+
<tr>
1241+
<td><code>SQLITE_READ</code></td>
1242+
<td>Read from a table</td>
1243+
</tr>
1244+
<tr>
1245+
<td><code>SQLITE_SELECT</code></td>
1246+
<td>Execute a SELECT statement</td>
1247+
</tr>
1248+
<tr>
1249+
<td><code>SQLITE_TRANSACTION</code></td>
1250+
<td>Begin, commit, or rollback a transaction</td>
1251+
</tr>
1252+
<tr>
1253+
<td><code>SQLITE_UPDATE</code></td>
1254+
<td>Update a table</td>
1255+
</tr>
1256+
<tr>
1257+
<td><code>SQLITE_ATTACH</code></td>
1258+
<td>Attach a database</td>
1259+
</tr>
1260+
<tr>
1261+
<td><code>SQLITE_DETACH</code></td>
1262+
<td>Detach a database</td>
1263+
</tr>
1264+
<tr>
1265+
<td><code>SQLITE_ALTER_TABLE</code></td>
1266+
<td>Alter a table</td>
1267+
</tr>
1268+
<tr>
1269+
<td><code>SQLITE_REINDEX</code></td>
1270+
<td>Reindex</td>
1271+
</tr>
1272+
<tr>
1273+
<td><code>SQLITE_ANALYZE</code></td>
1274+
<td>Analyze the database</td>
1275+
</tr>
1276+
<tr>
1277+
<td><code>SQLITE_CREATE_VTABLE</code></td>
1278+
<td>Create a virtual table</td>
1279+
</tr>
1280+
<tr>
1281+
<td><code>SQLITE_DROP_VTABLE</code></td>
1282+
<td>Drop a virtual table</td>
1283+
</tr>
1284+
<tr>
1285+
<td><code>SQLITE_FUNCTION</code></td>
1286+
<td>Use a function</td>
1287+
</tr>
1288+
<tr>
1289+
<td><code>SQLITE_SAVEPOINT</code></td>
1290+
<td>Create, release, or rollback a savepoint</td>
1291+
</tr>
1292+
<tr>
1293+
<td><code>SQLITE_COPY</code></td>
1294+
<td>Copy data (legacy)</td>
1295+
</tr>
1296+
<tr>
1297+
<td><code>SQLITE_RECURSIVE</code></td>
1298+
<td>Recursive query</td>
1299+
</tr>
1300+
</table>
1301+
10511302
[Changesets and Patchsets]: https://www.sqlite.org/sessionintro.html#changesets_and_patchsets
10521303
[Constants Passed To The Conflict Handler]: https://www.sqlite.org/session/c_changeset_conflict.html
10531304
[Constants Returned From The Conflict Handler]: https://www.sqlite.org/session/c_changeset_abort.html
@@ -1059,6 +1310,7 @@ resolution handler passed to [`database.applyChangeset()`][]. See also
10591310
[`SQLITE_DIRECTONLY`]: https://www.sqlite.org/c3ref/c_deterministic.html
10601311
[`SQLITE_MAX_FUNCTION_ARG`]: https://www.sqlite.org/limits.html#max_function_arg
10611312
[`database.applyChangeset()`]: #databaseapplychangesetchangeset-options
1313+
[`database.setAuthorizer()`]: #databasesetauthorizercallback
10621314
[`sqlite3_backup_finish()`]: https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupfinish
10631315
[`sqlite3_backup_init()`]: https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupinit
10641316
[`sqlite3_backup_step()`]: https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep
@@ -1078,6 +1330,7 @@ resolution handler passed to [`database.applyChangeset()`][]. See also
10781330
[`sqlite3_last_insert_rowid()`]: https://www.sqlite.org/c3ref/last_insert_rowid.html
10791331
[`sqlite3_load_extension()`]: https://www.sqlite.org/c3ref/load_extension.html
10801332
[`sqlite3_prepare_v2()`]: https://www.sqlite.org/c3ref/prepare.html
1333+
[`sqlite3_set_authorizer()`]: https://sqlite.org/c3ref/set_authorizer.html
10811334
[`sqlite3_sql()`]: https://www.sqlite.org/c3ref/expanded_sql.html
10821335
[`sqlite3changeset_apply()`]: https://www.sqlite.org/session/sqlite3changeset_apply.html
10831336
[`sqlite3session_attach()`]: https://www.sqlite.org/session/sqlite3session_attach.html

0 commit comments

Comments
 (0)