Search code examples
cencryptionsqliteaccess-control

How to enable access control in SQLite?

We have an application which uses SQLite as an embedded RDBMS. Initially the application was meant for a single machine and single user. But with time the scope of the project has expanded. Now one of the feature that we need is a Fine grained access control.

Now as mentioned on the Appropriate Uses For SQLite

... SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions...

And in this forum post

sqlite doesn't have usernames and passwords. you can't secure the data files in that manner.

So, from the above it is very clear that access control is not available. And in a way it makes sense as it is mostly used in mobile applications, browsers or where ever an embedded db is required.

As per this SO post it is possible to encrypt the database. But to my best of knowledge I can not describe it as fine grained access control as the SQLite database is bounded to a single encryption key. Also, I can't have multiple users and with different access levels.

Now my question are:

  1. Is it possible to enable access control in SQLite in anyway? (I may have answered my question, but still, for my inner satisfaction I am asking this one :) )
  2. Is my understanding of fine grained access control related to encryption is correct?
  3. Lets say if I use encryption for a single machine and single user then what are the drawbacks of it over a real fine grained access control? (performance might be one of them as we have to decrypt it for every connection.. not sure)

Some Useful Information

  1. We are using the latest release of SQLite.
  2. The SQLite is embedded as part of source code.
  3. In its full capacity we can expect a database of size 1 GB or around.

Note: I know that it good to use any other RDBMS where access control is available, but question are very specific to SQLite. Please answer them in that light. Thanks.

Solution

  • There is no access control (meaning GRANT / REVOKE table level access control) in SQLite. You must provide any access control you require through your application code. Encryption supplies access control only at the very grossest level — you can either access the database or not.