This example of the AES_ENCRYPT and AES_DECRYPT functions is written in commented SQL.
-- This example of aes_encrypt and aes_decrypt function use is presented in three parts:
--
-- Part I: Preliminary description of target tables and users as DDL
-- Part II: Example schema changes motivated by introduction of encryption
-- Part III: Use of views and stored procedures to protect encryption keys
--
-- Part I: Define target tables and users
-- Assume two classes of user, represented here by the instances
-- PrivUser and NonPrivUser, assigned to groups reflecting differing
-- privileges.
-- The initial state reflects the schema prior to the introduction
-- of encryption.
-- Set up the starting context: There are two tables with a common key.
-- Some columns contain sensitive data, the remaining columns do not.
-- The usual join column for these tables is sensitiveA.
-- There is a key and a unique index.
grant connect to PrivUser identified by 'verytrusted' ;
grant connect to NonPrivUser identified by 'lesstrusted' ;
grant connect to high_privileges_group ;
grant group to high_privileges_group ;
grant membership in group high_privileges_group to PrivUser ;
grant connect to low_privileges_group ;
grant group to low_privileges_group ;
grant membership in group low_privileges_group to NonPrivUser ;
create table DBA.first_table
(sensitiveA char(16) primary key
,sensitiveB numeric(10,0)
,publicC varchar(255)
,publicD date
) ;
-- There is an implicit unique HG (HighGroup) index enforcing the primary key.
create table second_table
(sensitiveA char(16)
,publicP integer
,publicQ tinyint
,publicR varchar(64)
) ;
create hg index second_A_HG on second_table ( sensitiveA ) ;
-- TRUSTED users can see the sensitive columns.
grant select ( sensitiveA, sensitiveB, publicC, publicD )
on DBA.first_table to PrivUser ;
grant select ( sensitiveA, publicP, publicQ, publicR )
on DBA.second_table to PrivUser ;
-- Non-TRUSTED users in existing schema need to see sensitiveA to be
-- able to do joins, even though they should not see sensitiveB.
grant select ( sensitiveA, publicC, publicD )
on DBA.first_table to NonPrivUser ;
grant select ( sensitiveA, publicP, publicQ, publicR )
on DBA.second_table to NonPrivUser ;
-- Non-TRUSTED users can execute queries such as
select I.publicC, 3*II.publicQ+1
from DBA.first_table I, DBA.second_table II
where I.sensitiveA = II.sensitiveA and I.publicD IN ( '2006-01-11' ) ;
-- and
select count(*)
from DBA.first_table I, DBA.second_table II
where I.sensitiveA = II.sensitiveA and SUBSTR(I.sensitiveA,4,3)
BETWEEN '345' AND '456' ;
-- But only TRUSTED users can execute the query
select I.sensitiveB, 3*II.publicQ+1
from DBA.first_table I, DBA.second_table II
where I.sensitiveA = II.sensitiveA and I.publicD IN ( '2006-01-11' ) ;
-- Part II: Change the schema in preparation for encryption
--
-- The DBA introduces encryption as follows:
--
-- For applicable tables, the DBA changes the schema, adjusts access
-- permissions, and updates existing data. The encryption
-- keys used are hidden in a subsequent step.
-- DataLength comparison for length of varbinary encryption result
-- (units are Bytes):
--
-- PlainText CipherText Corresponding Numeric Precisions
--
-- 0 16
-- 1 - 16 32 numeric(1,0) - numeric(20,0)
-- 17 - 32 48 numeric(21,0) - numeric(52,0)
-- 33 - 48 64 numeric(53,0) - numeric(84,0)
-- 49 - 64 80 numeric(85,0) - numeric(116,0)
-- 65 - 80 96 numeric(117,0) - numeric(128,0)
-- 81 - 96 112
-- 97 - 112 128
-- 113 - 128 144
-- 129 - 144 160
-- 145 - 160 176
-- 161 - 176 192
-- 177 - 192 208
-- 193 - 208 224
-- 209 - 224 240
-- The integer data types tinyint, small int, integer, and bigint
-- are varbinary(32) ciphertext.
-- The exact relationship is
-- DATALENGTH(ciphertext) =
-- (((DATALENGTH(plaintext)+ 15) / 16) + 1) * 16
-- For the first table, the DBA chooses to preserve both the plaintext and
-- ciphertext forms. This is not typical and should only be done if the
-- database files are also encrypted.
-- Take away NonPrivUser's access to column sensitiveA and transfer
-- access to the ciphertext version.
-- Put a unique index on the ciphertext column. The ciphertext
-- itself is indexed.
-- NonPrivUser can select the ciphertext and use it.
-- PrivUser can still select either form (without paying decrypt costs).
revoke select ( sensitiveA ) on DBA.first_table from NonPrivUser ;
alter table DBA.first_table add encryptedA varbinary(32) ;
grant select ( encryptedA ) on DBA.first_table to PrivUser ;
grant select ( encryptedA ) on DBA.first_table to NonPrivUser ;
create unique hg index first_A_unique on first_table ( encryptedA ) ;
update DBA.first_table
set encryptedA = aes_encrypt(sensitiveA, 'seCr3t')
where encryptedA is null ;
commit
-- Now change column sensitiveB.
alter table DBA.first_table add encryptedB varbinary(32) ;
grant select ( encryptedB ) on DBA.first_table to PrivUser ;
create unique hg index first_B_unique on first_table ( encryptedB ) ;
update DBA.first_table
set encryptedB = aes_encrypt(sensitiveB,
'givethiskeytonoone') where encryptedB is null ;
commit
-- For the second table, the DBA chooses to keep only the ciphertext.
-- This is more typical and encrypting the database files is not required.
revoke select ( sensitiveA ) on DBA.second_table from NonPrivUser ;
revoke select ( sensitiveA ) on DBA.second_table from PrivUser ;
alter table DBA.second_table add encryptedA varbinary(32) ;
grant select ( encryptedA ) on DBA.second_table to PrivUser ;
grant select ( encryptedA ) on DBA.second_table to NonPrivUser ;
create unique hg index second_A_unique on second_table ( encryptedA ) ;
update DBA.second_table
set encryptedA = aes_encrypt(sensitiveA, 'seCr3t')
where encryptedA is null ;
commit
alter table DBA.second_table drop sensitiveA ;
-- The following types of queries are permitted at this point, before
-- changes are made for key protection:
-- Non-TRUSTED users can equi-join on ciphertext; they can also select
-- the binary, but have no way to interpret it.
select I.publicC, 3*II.publicQ+1
from DBA.first_table I, DBA.second_table II
where I.encryptedA = II.encryptedA and I.publicD IN ( '2006-01-11' ) ;
-- Ciphertext-only access rules out general predicates and expressions.
-- The following query does not return meaningful results.
--
-- NOTE: These four predicates can be used on the varbinary containing
-- ciphertext:
-- = (equality)
-- <> (inequality)
-- IS NULL
-- IS NOT NULL
select count(*)
from DBA.first_table I, DBA.second_table II
where I.encryptedA = II.encryptedA and SUBSTR(I.encryptedA,4,3)
BETWEEN '345' AND '456' ;
-- The TRUSTED user still has access to the plaintext columns that
-- were retained. Therefore, this user does not need to call
-- aes_decrypt and does not need the key.
select count(*)
from DBA.first_table I, DBA.second_table II
where I.encryptedA = II.encryptedA and SUBSTR(I.sensitiveA,4,3)
BETWEEN '345' AND '456' ;
-- Part III: Protect the encryption keys
-- This section illustrates how to grant access to the plaintext, but
-- still protect the keys.
-- For the first table, the DBA elected to retain the plaintext columns.
-- Therefore, the following view has the same capabilities as the trusted
-- user above.
-- Assume group_member is being used for additional access control.
-- NOTE: In this example, NonPrivUser still has access to the ciphertext
-- encrypted in the base table.
create view DBA.a_first_view (sensitiveA, publicC, publicD)
as
select
IF group_member('high_privileges_group',user_name()) = 1
THEN sensitiveA
ELSE NULL
ENDIF,
publicC,
publicD
from first_table ;
grant select on DBA.a_first_view to PrivUser ;
grant select on DBA.a_first_view to NonPrivUser ;
-- For the second table, the DBA did not keep the plaintext.
-- Therefore, aes_decrypt calls must be used in the view.
-- IMPORTANT: Hide the view definition with ALTER VIEW, so that no one
-- can discover the key.
create view DBA.a_second_view (sensitiveA,publicP,publicQ,publicR)
as
select
IF group_member('high_privileges_group',user_name()) = 1
THEN aes_decrypt(encryptedA,'seCr3t', char(16))
ELSE NULL
ENDIF,
publicP,
publicQ,
publicR
from second_table ;
alter view DBA.a_second_view set hidden ;
grant select on DBA.a_second_view to PrivUser ;
grant select on DBA.a_second_view to NonPrivUser ;
-- Likewise, the key used for loading can be protected in a stored procedure.
-- By hiding the procedure (just as the view is hidden), no-one can see
-- the keys.
create procedure load_first_proc(@inputFileName varchar(255),
@colDelim varchar(4) default '$',
@rowDelim varchar(4) default '\n')
begin
execute immediate with quotes
'load table DBA.second_table
(encryptedA encrypted(char(16),' ||
'''' || 'seCr3t' || '''' || '),publicP,publicQ,publicR) ' ||
' from ' || '''' || @inputFileName || '''' ||
' delimited by ' || '''' || @colDelim || '''' ||
' row delimited by ' || '''' || @rowDelim || '''' ||
' quotes off escapes off' ;
end
;
alter procedure DBA.load_first_proc set hidden ;
-- Call the load procedure using the following syntax:
call load_first_proc('/dev/null', '$', '\n') ;
-- Below is a comparison of several techniques for protecting the
-- encryption keys by using user-defined functions (UDFs), other views,
-- or both. The first and the last alternatives offer maximum performance.
-- The second_table is secured as defined earlier.
-- Alternative 1:
-- This baseline approach relies on restricting access to the entire view.
create view
DBA.second_baseline_view(sensitiveA,publicP,publicQ,publicR)
as
select
IF group_member('high_privileges_group',user_name()) = 1
THEN aes_decrypt(encryptedA,'seCr3t', char(16))
ELSE NULL
ENDIF,
publicP,
publicQ,
publicR
from DBA.second_table ;
alter view DBA.second_baseline_view set hidden ;
grant select on DBA.second_baseline_view to NonPrivUser ;
grant select on DBA.second_baseline_view to PrivUser ;
-- Alternative 2:
-- Place the encryption function invocation within a user-defined
-- function (UDF).
-- Hide the definition of the UDF. Restrict the UDF permissions.
-- Use the UDF in a view that handles the remainder of the security
-- and business logic.
-- Note: The view itself does not need to be hidden.
create function DBA.second_decrypt_function(IN datum varbinary(32))
RETURNS char(16) DETERMINISTIC
BEGIN
RETURN aes_decrypt(datum,'seCr3t', char(16));
END ;
grant execute on DBA.second_decrypt_function to PrivUser ;
alter function DBA.second_decrypt_function set hidden ;
create view
DBA.second_decrypt_view(sensitiveA,publicP,publicQ,publicR)
as
select
IF group_member('high_privileges_group',user_name()) = 1
THEN second_decrypt_function(encryptedA)
ELSE NULL
ENDIF,
publicP,
publicQ,
publicR
from DBA.second_table ;
grant select on DBA.second_decrypt_view to NonPrivUser ;
grant select on DBA.second_decrypt_view to PrivUser ;
-- Alternative 3:
-- Sequester only the key selection in a user-defined function.
-- This function could be extended to support selection of any
-- number of keys.
-- This UDF is also hidden and has restricted execute privileges.
-- Note: Any view that uses this UDF therefore does not compromise
-- the key values.
create function DBA.second_key_function()
RETURNS varchar(32) DETERMINISTIC
BEGIN
return 'seCr3t' ;
END
grant execute on DBA.second_key_function to PrivUser ;
alter function DBA.second_key_function set hidden ;
create view DBA.second_key_view(sensitiveA,publicP,publicQ,publicR)
as
select
IF group_member('high_privileges_group',user_name()) = 1
THEN aes_decrypt(encryptedA,second_key_function(),
char(16))
ELSE NULL
ENDIF,
publicP,
publicQ,
publicR
from DBA.second_table ;
grant select on DBA.second_key_view to NonPrivUser ;
grant select on DBA.second_key_view to PrivUser ;
-- Alternative 4:
-- The recommended alternative is to separate the security logic
-- from the business logic by dividing the concerns into two views.
-- Only the security logic view needs to be hidden.
-- Note: The performance of this approach is similar to that of the first
-- alternative.
create view
DBA.second_SecurityLogic_view(sensitiveA,publicP,publicQ,publicR)
as
select
IF group_member('high_privileges_group',user_name()) = 1
THEN aes_decrypt(encryptedA,'seCr3t', char(16))
ELSE NULL
ENDIF,
publicP,
publicQ,
publicR
from DBA.second_table ;
alter view DBA.second_SecurityLogic_view set hidden ;
create view
DBA.second_BusinessLogic_view(sensitiveA,publicP,publicQ,publicR)
as
select
sensitiveA,
publicP,
publicQ,
publicR
from DBA.second_SecurityLogic_view ;
grant select on DBA.second_BusinessLogic_view to NonPrivUser ;
grant select on DBA.second_BusinessLogic_view to PrivUser ;
-- End of encryption example
The ciphertext produced by AES_ENCRYPT differs for two different data types given the same input value and same key. A join of two ciphertext columns that hold encrypted values of two different data types may therefore not return identical results.
For example, assume:
CREATE TABLE tablea(c1 int, c2 smallint); INSERT INTO tablea VALUES (100,100);
The value AES_ENCRYPT(c1, 'key') differs
from AES_ENCRYPT(c2,'key') and
the value AES_ENCRYPT(c1,'key') differs from AES_ENCRYPT(100,'key').
To resolve this issue, cast the input of AES_ENCRYPT to the same data type. For example, the results of these code fragments are the same:
AES_ENCRYPT(c1, 'key');
AES_ENCRYPT(CAST(c2 AS INT), 'key');
AES_ENCRYPT(CAST(100 AS INT), 'key');
