Oracle XMLType and AES_Encrypt() field
93 views
Skip to first unread message
Dan Davis
Sep 12, 2015, 8:01:08 AM9/12/15
to Django users
I'm working on a Django project template to be used by a sub-department of my employer. We are an Oracle shop, and make reasonably heavy use of Oracle's XMLType. Another issue that has come up is encryption. It is trivial to create an EncryptedTextField field type. What I want to do is related to my solution for Oracle's XMLType:
For both my EncryptedTextField and XMLTypeField, I want to to alter the SQL that will be generated to query, insert, and update the field so that XMLType field will be converted to a CLOB or text value with .getClobValue() function. Similarly, I want the value to save to be generated from calling AES_Encrypt() with the project's secret key and to be decrypted by calling AES_Decrypt(). The benefit of using the database encryption/decryption functions should be clear enough - encryption and decryption is offloaded from the application, and the programmer is assured of being able to see the values by constructing a simple view.
May I alter the way a query is constructed using a custom Field class in this way? If so, can anyone provide an example?
Finally, if I don't have to do this work, and Django ORM already supports Oracle XMLType through cx_Oracle, please let me know ... Dr. Google was unable to help me, and I know my boss wants to stick with Django as much as he can because he has to bring 25+ programmers with me into Python/Django.
Carl Meyer
Sep 12, 2015, 6:23:15 PM9/12/15
to django...@googlegroups.com
Hi Dan,
On 09/11/2015 09:39 PM, Dan Davis wrote:
...
where Field classes gained more control over SQL generation; I suspect
it would be quite a bit more difficult if you need to support older
versions). It does require exploiting some undocumented internal APIs.
For an example, you can look at my django-pgcrypto-expressions project,
which does the same thing for Postgres' built-in symmetric encryption
functions: https://github.com/orcasgit/django-pgcrypto-expressions
You should be aware, though, that depending on your threat model,
encrypting/decrypting in the database can be significantly more risky.
It potentially exposes your encryption key in several additional places:
on the wire between the application server and the database server and
in database server query logs or activity monitors. If your threat model
includes the possibility of an attacker with access to the running
database server (who may not have gained access to the app server),
encryption on the application side is better than on the database side.
It's possible to make application-side encryption in Django almost as
transparent to the developer as database-side encryption; see
django-fernet-fields: https://github.com/orcasgit/django-fernet-fields
The main difference is that with application-side encryption you can no
longer perform lookups or ordering in the database against the encrypted
field. But this isn't really much of a difference, because even with
database-side encryption, lookups/ordering on an encrypted field cannot
be indexed, meaning they won't scale to large table sizes anyway.
HTH,
Carl
On 09/11/2015 09:39 PM, Dan Davis wrote:
...
> For both my EncryptedTextField and XMLTypeField, I want to to alter the
> SQL that will be generated to query, insert, and update the field so
> that XMLType field will be converted to a CLOB or text value with
> .getClobValue() function. Similarly, I want the value to save to be
> generated from calling AES_Encrypt() with the project's secret key and
> to be decrypted by calling AES_Decrypt(). The benefit of using the
> database encryption/decryption functions should be clear enough -
> encryption and decryption is offloaded from the application, and the
> programmer is assured of being able to see the values by constructing a
> simple view.
>
> May I alter the way a query is constructed using a custom Field class in
> this way? If so, can anyone provide an example?
Yes, this is possible, and not even that hard (at least in Django 1.8,
> SQL that will be generated to query, insert, and update the field so
> that XMLType field will be converted to a CLOB or text value with
> .getClobValue() function. Similarly, I want the value to save to be
> generated from calling AES_Encrypt() with the project's secret key and
> to be decrypted by calling AES_Decrypt(). The benefit of using the
> database encryption/decryption functions should be clear enough -
> encryption and decryption is offloaded from the application, and the
> programmer is assured of being able to see the values by constructing a
> simple view.
>
> May I alter the way a query is constructed using a custom Field class in
> this way? If so, can anyone provide an example?
where Field classes gained more control over SQL generation; I suspect
it would be quite a bit more difficult if you need to support older
versions). It does require exploiting some undocumented internal APIs.
For an example, you can look at my django-pgcrypto-expressions project,
which does the same thing for Postgres' built-in symmetric encryption
functions: https://github.com/orcasgit/django-pgcrypto-expressions
You should be aware, though, that depending on your threat model,
encrypting/decrypting in the database can be significantly more risky.
It potentially exposes your encryption key in several additional places:
on the wire between the application server and the database server and
in database server query logs or activity monitors. If your threat model
includes the possibility of an attacker with access to the running
database server (who may not have gained access to the app server),
encryption on the application side is better than on the database side.
It's possible to make application-side encryption in Django almost as
transparent to the developer as database-side encryption; see
django-fernet-fields: https://github.com/orcasgit/django-fernet-fields
The main difference is that with application-side encryption you can no
longer perform lookups or ordering in the database against the encrypted
field. But this isn't really much of a difference, because even with
database-side encryption, lookups/ordering on an encrypted field cannot
be indexed, meaning they won't scale to large table sizes anyway.
HTH,
Carl
Dan Davis
Sep 15, 2015, 6:44:44 PM9/15/15
to Django users
On Saturday, September 12, 2015 at 2:23:15 PM UTC-4, Carl Meyer wrote:
Hi Dan,
... <snip>...
Yes, this is possible, and not even that hard (at least in Django 1.8,
where Field classes gained more control over SQL generation; I suspect
it would be quite a bit more difficult if you need to support older
versions). It does require exploiting some undocumented internal APIs.
For an example, you can look at my django-pgcrypto-expressions project,
which does the same thing for Postgres' built-in symmetric encryption
functions: https://github.com/orcasgit/django-pgcrypto-expressions
I've looked at your github project and the code for Django 1.8 fields and written some code.
I'm wondering how I would get python to run an interactive shell similar to what I'd get from running ./manage.py shell.
I tend to test/debug that way before I run py.test or nose...
Carl Meyer
Sep 15, 2015, 6:49:06 PM9/15/15
to django...@googlegroups.com
On 09/15/2015 12:44 PM, Dan Davis wrote:
> I'm wondering how I would get python to run an interactive shell similar
> to what I'd get from running ./manage.py shell.
Why not just run `./manage.py shell`?
> I'm wondering how I would get python to run an interactive shell similar
> to what I'd get from running ./manage.py shell.
In general, `DJANGO_SETTINGS_MODULE=myproject.settings python` is
effectively equivalent to `./manage.py shell` (except for the latter
will use IPython by default if installed).
Carl
Dan Davis
Sep 15, 2015, 9:15:26 PM9/15/15
to Django users
Indeed. That works.
Reply all
Reply to author
Forward
0 new messages