CODEWITHSUNDEEP
cencryptionsqliteaccess-control
Favonius
Favonius

Reputation: 13974

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.

Upvotes: 3

Views: 9398

Answers (4)

polyphemus11
polyphemus11

Reputation: 113

While looking up this exact topic I thought of this idea (I'm using python to interface with the database):

  1. Create an 'admin' table
  2. Store a list of usernames and passwords (hashed of course)
  3. Create a username/password prompt in python, quit if hash and password do not match
  4. Create a column with privilege levels
  5. Create a table with those privilege levels (to normalize) and the associated query types that level can perform. Optional - create another column in that table to name the permission levels
  6. Pass each query through a function that matches that check's that user's permission level and sees if the query contains any of the approved commands. If not, given access restricted prompt.

Obviously not in stone, and I think there are ways around the 'contains check' so I'll be brainstorming that too. Something to think about.

Upvotes: -2

DaveM
DaveM

Reputation: 734

I don't know how well this idea would work, if at all, so others comments are very welcome.

Could you do something like the following...

On each terminal have a local embeded SQLite database.

When the application closes, or on each update to the internal data (choice is yours I guess), create a trigger into the 'master DB' that resides 'on the network' somewhere. Here you insert the value that the update has, and you have a second column for each field to store the name of the user (taken from the local terminal login name for instance).

I'm not sure if this would work or not? You could then implement a table within each 'local' sqlite file that was a list of usernames and passwords, to control access to the back end master file (and possible to the sqlite file itself, using your application to demand the name of the user and their specific password). You could then even enable a 'view only' guest user who (throug you application) can only perform lookups on the data. This table would obviously need to be only written from the connection to the master DB.

You would then of course need to build in a method to update each local copy with the copy on the server, and control any corruption / integrity issues... which may begin to create other problems and cause your application front end to become considerably more complex (as to the best of my understanding SQLite can't do this sort of testing internally, as sqlite isn't a client / server type RDBMS).

Of course once you have done all this hard work you could share it the SQLite team, and then sqlite will be even more practical for lots of instances.

David

Upvotes: 0

Martin Samson
Martin Samson

Reputation: 4090

First, how are you going to share the SQLite database? SQLite does not have a server-client architecture like mysql/postgresql.

You will probably want to move to MySQL or PostgreSQL if you want multiple computers to use the same database at once, otherwise it will be the same as sharing a microsoft Access database.

SQLite tends to grow very big over time if you do a lot of inserts/update/deletes. The file will always grow but never shrink down.

Upvotes: 2

Larry Lustig
Larry Lustig

Reputation: 50980

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.

Upvotes: 10

Related Questions