RE: column-level encryption
Thomas J. Girsch wrote
> I have a requirement to do column-level encryption in an
> Informix 9.30.UC6
> database. If possible, I'd like to avoid datablades (although Stored
> Procedures / Functions would be okay). In a perfect world it
> would be a
> fast algorithm. The encryption algorithm doesn't need to be
> super-secure --
> just enough to mask the contents from the casual observer and
> keep the data
> in some format other than readable text.
>
> Has anyone already invented this wheel, such that I don't
> need to re-invent
> it? Any advice or assistance would be greatly appreciated.
>
> Oh, and I should mention that for this application, a "secret
> key" algorithm
> would be fine. A public key algorithm is probably overkill.
>
Simple bit rotation would probably do you. I think there is a standard ROT13 that is often used in email. (check with Google).
I you want to devise you own, here is something I copied a while back from this news group. ----
Colin Bull
c.bull@videonetworks.com
From: owner-informix-list@iiug.org on behalf of Bryan Castillo
[rook_5150@yahoo.com]
Sent: 26 May 2003 06:34
To: informix-list@iiug.org
Subject: fast bitmask manipulation
(Perhaps someone in the future will find this helpful)
I searched for ways to manipulate bitmasks from sql statements, and
found some references to the stored function SYSMASTER:bitval.
I noticed this was very slow, and there weren't ways to manipulate
bitmasks from SQL or SPL. This procedure is written in SPL and uses
normal math operations to check for a specific bit.
(I wanted to put some business logic into stored procedures that used
bitmasks and I needed to be able to turn bits off and on in a bit
mask)
Out of all the posts I read, no one mentioned implementing stored
functions in C. This can be done fairly easily, with a nice
performance increase. (the library listed below does alot more than
just check if a bit is on, it exposes all of the bit operations
including bit shifting.)
(for example here is some SQL to turn off bit 15 in a bitmask)
> update checks
> set tablemaskid = bit_and(tablemaskid, bit_not(16384))
> where bit_on(tablemaskid, 16384);
65 row(s) updated.
Here are some stats on various bitwise procedures. Comparing
SYSMASTER:bitval, functions in C and using the bitval SPL routine in
the local database.
- This table has 40000 rows and tablemaskid is not indexed
# Check C function speed
select count(*) from checks
where bit_on(tablemaskid, 16384)Count: 65
Time: 7.83151197433472 seconds
# Check C function speed (2nd arg is a bit position)
select count(*) from checks
where bit_pos_on(tablemaskid, 15)Count: 65
Time: 7.87693309783936 seconds
# Check speed of SYSMASTER:bitval (installed locally)
# (you can get the SPL from dbaccess and load it into your own db)
select count(*) from checks
where bitval(tablemaskid, 16384) = 1Count: 65
Time: 12.5782849788666 seconds
# Check the speed of SYSMASTER:bitval
select count(*) from checks
where SYSMASTER:bitval(tablemaskid, 16384) = 1Count: 65
Time: 19.85014295578 seconds
# Check speed of C (various of bit_on)
select count(*) from checks
where bit_and(tablemaskid, 16384) = 16384Count: 65
Time: 7.84405207633972 seconds
(Here is the C source it has to be compiled into a shared library
and then it can be loaded into the database)
[bit_routines.c]
#include <mi.h>
mi_boolean bit_on(mi_integer item, mi_integer mask) {
return ((mask & item) == mask) ? '\\1' : '\\0';
}
mi_boolean bit_pos_on(mi_integer item, mi_integer bitpos) {
long mask = 1 << (bitpos-1);
return ((mask & item) == mask) ? '\\1' : '\\0';
}
mi_integer bit_or(mi_integer left, mi_integer right) {
return left | right;
}
mi_integer bit_and(mi_integer left, mi_integer right) {
return left & right;
}
mi_integer bit_xor(mi_integer left, mi_integer right) {
return left ^ right;
}
mi_integer bit_not(mi_integer value) {
return ~value;
}
mi_integer bit_shiftl(mi_integer left, mi_integer right) {
return left << right;
}
mi_integer bit_shiftr(mi_integer left, mi_integer right) {
return left >> right;
}
To compile it use this Makefile (just typing make will compile and
register it)
This is on Sun Solaris using WorkShop Pro. If use gcc it shouldn't be
to
hard to modify this.
[Makefile]
CC = cc
LD = ld
LFLAGS = -G
CFLAGS = -xO2 -xCC -DMI_SERVBUILD
INC = -I$(INFORMIXDIR)/incl/public
LIBRARY = bit_routines.so
OBJECTS = bit_routines.o
DATABASE = saproto
REGISTER = ./sp_bit_routines.sh
all: register
.SUFFIXES: .c .o .so
.c.o:
$(CC) -c $(CFLAGS) $(INC) -o $@ $<
$(LIBRARY): $(OBJECTS)
$(LD) $(LFLAGS) -o $@ $(OBJECTS)
-@nm $(LIBRARY) | grep FUNC
register: $(LIBRARY)
-make drop_funcs
$(REGISTER) | dbaccess $(DATABASE)
clean:
-rm -rf core *.o *.so
-make drop_funcs
drop_funcs:
-echo "drop function bit_on" | dbaccess $(DATABASE)
-echo "drop function bit_pos_on" | dbaccess $(DATABASE)
-echo "drop function bit_or" | dbaccess $(DATABASE)
-echo "drop function bit_and" | dbaccess $(DATABASE)
-echo "drop function bit_xor" | dbaccess $(DATABASE)
-echo "drop function bit_shiftl" | dbaccess $(DATABASE)
-echo "drop function bit_shiftr" | dbaccess $(DATABASE)
-echo "drop function bit_not" | dbaccess $(DATABASE)
$ cc -c -xO2 -xCC -DMI_SERVBUILD
-I/informix/distr/infx.9.21.UC3/incl/public -o bit_routines.o
bit_routines.c
$ ld -G -o bit_routines.so bit_routines.o
[sp_bit_routines.sh]
This script will do the registering of functions
#!/bin/sh
so_file=`pwd`/bit_routines.so
{ echo "CREATE FUNCTION bit_on(INTEGER, INTEGER) "
echo " RETURNS BOOLEAN "
echo " WITH (NOT VARIANT) "
echo " EXTERNAL NAME "
echo " '${so_file}'"
echo " LANGUAGE C;"
echo
echo "CREATE FUNCTION bit_pos_on(INTEGER, INTEGER) "
echo " RETURNS BOOLEAN "
echo " WITH (NOT VARIANT) "
echo " EXTERNAL NAME "
echo " '${so_file}'"
echo " LANGUAGE C;"
echo
echo "CREATE FUNCTION bit_or(INTEGER, INTEGER) "
echo " RETURNS INTEGER "
echo " WITH (NOT VARIANT) "
echo " EXTERNAL NAME "
echo " '${so_file}'"
echo " LANGUAGE C;"
echo
echo "CREATE FUNCTION bit_and(INTEGER, INTEGER) "
echo " RETURNS INTEGER "
echo " WITH (NOT VARIANT) "
echo " EXTERNAL NAME "
echo " '${so_file}'"
echo " LANGUAGE C;"
echo
echo "CREATE FUNCTION bit_xor(INTEGER, INTEGER) "
echo " RETURNS INTEGER "
echo " WITH (NOT VARIANT) "
echo " EXTERNAL NAME "
echo