Restricting SQL Statements with an Authorizer
On this page
An authorizer is a handler that SQLite calls while it compiles a SQL statement, before the statement is executed.
SQLite splits the statement into elementary actions, such as reading a column, inserting into a table, executing a PRAGMA, or attaching a database, and asks the handler about each of them.
The handler allows the action, denies the whole statement, or makes SQLite ignore the action.
This lets you restrict what SQL executed on a connection can do, for example, when your application executes SQL entered by users.
To register an authorizer, call the SQLiteConnection.RegisterAuthorizer method of an open connection and pass an SQLiteAuthorizerHandler delegate.
The handler receives the action code (SQLiteAuthorizerActionCode) and up to four string arguments that describe the action: two arguments that depend on the action, the database name (main, temp, or the name of an attached database), and the name of the innermost trigger or view that caused the action.
For example, for the Read action, the first two arguments are the table and column names.
For more information about the arguments of every action, see SQLiteAuthorizerActionCode Enumeration.
The handler returns one of the following SQLiteAuthorizerReturnCode values:
Ok – The action is allowed.
Deny – The whole statement is rejected. Executing it throws an SQLiteException with the SQLiteErrorCode.Auth error code.
Ignore – The statement is executed, but the action is skipped. For the Read action, the column is returned as NULL. For the Delete action, the rows are deleted one by one instead of the table being truncated. For the other actions, the operation isn't performed.
A connection can have only one authorizer. Registering a new one replaces the previous one.
To remove the authorizer, call the SQLiteConnection.UnRegisterAuthorizer method.
When the connection is closed, including when it returns to the connection pool, the authorizer is removed, so you have to register it again after you open the connection.
If the handler throws an exception, the statement is rejected, and the SQLiteException that is thrown contains the exception from the handler in the InnerException property.
The handler mustn't do anything that modifies the connection it is registered in. This includes SQL statement preparation and execution, data retrieval, and so on.
Statements executed by dotConnect for SQLite
dotConnect for SQLite itself executes some statements, for example, to retrieve metadata when the GetSchema method or the GetSchemaTable method with CommandBehavior.KeyInfo is used. dotConnect for SQLite can also be used to begin, commit, and roll back TransactionScope transactions.
The authorizer isn't called for these statements, so they work even under a strict authorizer.
Note that because of this, GetSchema returns the metadata of tables and views that the authorizer does not allow you to read.
The authorizer checks the same PRAGMA statements in your own SQL as usual.
Transactions that you start with the SQLiteConnection.BeginTransaction method are checked: the handler receives the Transaction action for BEGIN, COMMIT, and ROLLBACK.
Allowing only reading data
The following example allows only SELECT statements, column access, and function calls. All other statements, including INSERT, DELETE, PRAGMA, and ATTACH, are rejected.
|
|---|
SQLiteConnection connection = new SQLiteConnection(@"Data Source=D:\SQLite\test.db");
connection.Open();
// Allow only reading data: SELECT statements, reading columns and calling functions
connection.RegisterAuthorizer((action, arg1, arg2, dbName, innerMostTriggerOrView) => {
switch (action) {
case SQLiteAuthorizerActionCode.Select:
case SQLiteAuthorizerActionCode.Read:
case SQLiteAuthorizerActionCode.Function:
return SQLiteAuthorizerReturnCode.Ok;
default:
return SQLiteAuthorizerReturnCode.Deny;
}
});
SQLiteCommand command = new SQLiteCommand("select count(*) from employees", connection);
Console.WriteLine(command.ExecuteScalar());
try {
command.CommandText = "delete from employees";
command.ExecuteNonQuery();
}
catch (SQLiteException ex) {
// ErrorCode is SQLiteErrorCode.Auth
Console.WriteLine(ex.ErrorCode + ": " + ex.Message);
}
connection.UnRegisterAuthorizer();
connection.Close();
|
|
|---|
Function ReadOnlyAuthorizer(action As SQLiteAuthorizerActionCode, arg1 As String, arg2 As String, _
dbName As String, innerMostTriggerOrView As String) As SQLiteAuthorizerReturnCode
Select Case action
Case SQLiteAuthorizerActionCode.Select, SQLiteAuthorizerActionCode.Read, SQLiteAuthorizerActionCode.Function
Return SQLiteAuthorizerReturnCode.Ok
Case Else
Return SQLiteAuthorizerReturnCode.Deny
End Select
End Function
Sub ReadOnlySample()
Dim connection As New SQLiteConnection("Data Source=D:\SQLite\test.db")
connection.Open()
' Allow only reading data: SELECT statements, reading columns and calling functions
connection.RegisterAuthorizer(AddressOf ReadOnlyAuthorizer)
Dim command As New SQLiteCommand("select count(*) from employees", connection)
Console.WriteLine(command.ExecuteScalar())
Try
command.CommandText = "delete from employees"
command.ExecuteNonQuery()
Catch ex As SQLiteException
' ErrorCode is SQLiteErrorCode.Auth
Console.WriteLine(ex.ErrorCode.ToString() & ": " & ex.Message)
End Try
connection.UnRegisterAuthorizer()
connection.Close()
End Sub
|
Hiding a column
The following example returns the salary column of the employees table as NULL. The query itself is executed without errors.
|
|---|
SQLiteConnection connection = new SQLiteConnection(@"Data Source=D:\SQLite\test.db");
connection.Open();
// The salary column of the employees table is read as NULL
connection.RegisterAuthorizer((action, arg1, arg2, dbName, innerMostTriggerOrView) =>
action == SQLiteAuthorizerActionCode.Read && arg1 == "employees" && arg2 == "salary"
? SQLiteAuthorizerReturnCode.Ignore
: SQLiteAuthorizerReturnCode.Ok);
SQLiteCommand command = new SQLiteCommand("select name, salary from employees", connection);
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
Console.WriteLine(reader.GetString(0) + " " + (reader.IsDBNull(1) ? "NULL" : reader.GetValue(1).ToString()));
}
connection.Close();
|
|
|---|
Dim connection As New SQLiteConnection("Data Source=D:\SQLite\test.db")
connection.Open()
' The salary column of the employees table is read as NULL
connection.RegisterAuthorizer(Function(action, arg1, arg2, dbName, innerMostTriggerOrView) _
If(action = SQLiteAuthorizerActionCode.Read AndAlso arg1 = "employees" AndAlso arg2 = "salary", _
SQLiteAuthorizerReturnCode.Ignore, SQLiteAuthorizerReturnCode.Ok))
Dim command As New SQLiteCommand("select name, salary from employees", connection)
Using reader As SQLiteDataReader = command.ExecuteReader()
While reader.Read()
Console.WriteLine(reader.GetString(0) & " " & If(reader.IsDBNull(1), "NULL", reader.GetValue(1).ToString()))
End While
End Using
connection.Close()
|
Exceptions in the handler
In the following example, the handler throws an exception for the ATTACH statement. The statement is rejected, and the exception from the handler is available in the InnerException property.
|
|---|
SQLiteConnection connection = new SQLiteConnection(@"Data Source=D:\SQLite\test.db");
connection.Open();
connection.RegisterAuthorizer((action, arg1, arg2, dbName, innerMostTriggerOrView) => {
if (action == SQLiteAuthorizerActionCode.Attach)
throw new ApplicationException("ATTACH is not allowed: " + arg1);
return SQLiteAuthorizerReturnCode.Ok;
});
try {
SQLiteCommand command = new SQLiteCommand("attach database 'other.db' as other", connection);
command.ExecuteNonQuery();
}
catch (SQLiteException ex) {
// ex.ErrorCode is SQLiteErrorCode.Auth, ex.InnerException is the ApplicationException thrown by the handler
Console.WriteLine(ex.InnerException.Message);
}
connection.Close();
|
|
|---|
Dim connection As New SQLiteConnection("Data Source=D:\SQLite\test.db")
connection.Open()
connection.RegisterAuthorizer(Function(action, arg1, arg2, dbName, innerMostTriggerOrView)
If action = SQLiteAuthorizerActionCode.Attach Then
Throw New ApplicationException("ATTACH is not allowed: " & arg1)
End If
Return SQLiteAuthorizerReturnCode.Ok
End Function)
Try
Dim command As New SQLiteCommand("attach database 'other.db' as other", connection)
command.ExecuteNonQuery()
Catch ex As SQLiteException
' ex.ErrorCode is SQLiteErrorCode.Auth, ex.InnerException is the ApplicationException thrown by the handler
Console.WriteLine(ex.InnerException.Message)
End Try
connection.Close()
|
See Also