dotConnect for SQLite Documentation
On this page
    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:

    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.

    Note 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