This section contain an example of an interactive session designed to show you how to use data encryption in your applications. Comments have been added to explain each operation.
-- Encryption using DB2eCLP
--
-- This is an example encryption session using the provided sample
-- command line interface program DB2eCLP.
--
-- We only show the return code of a statement if it
-- failed, if it completed successfully we only show the results
-- of selects.
-- Commands which can be typed into DB2 Everyplace are
-- prefixed by the string "CLP:> ".
--
-- -- (CLI:SQLConnect, SQL:CREATE TABLE, SQL:GRANT, SQL:REVOKE)
--
-- When you start DB2eCLP you are automatically
-- connected to the default database (in the current directory).
-- This is equivalent to:
--
CLP:> CONNECT TO anything;
-- Since no specific path is given, just a name "anything", it connects
-- to the current directory.
--
-- We will now create a non-encrypted table containing a mapping of
-- some numbers to Swedish counting words.
CLP:> CREATE TABLE swedish(nummer INT, ord VARCHAR(32));
CLP:> INSERT INTO swedish VALUES(1, 'ett');
CLP:> INSERT INTO swedish VALUES(3, 'tre');
CLP:> INSERT INTO swedish VALUES(4, 'fyra');
CLP:> INSERT INTO swedish VALUES(5, 'fem');
CLP:> INSERT INTO swedish VALUES(7, 'sju');
CLP:> INSERT INTO swedish VALUES(99, 'nittionio');
-- Just have a look at the data
CLP:> SELECT * FROM swedish;
NUMMER ORD
----------- --------------------------------
1 ett
3 tre
4 fyra
5 fem
7 sju
99 nittionio
6 row(s) returned.
-- We will now try to create the corresponding table for English,
-- but using encryption.
--
CLP:> CREATE TABLE english(number INT, word VARCHAR(32)) WITH ENCRYPTION;
Statement failed [sqlstate = 42501].
-- This fails because we are not authorized yet. As indicated by the error code.
-- So we need to connect again:
--
CLP:> CONNECT TO something USER jsk USING hemligt;
-- This connects to the same database (default/current directory) but with
-- a specific user identity "jsk" and using the password "hemligt".
-- The CONNECT TO command is not an SQL statement, thus is
-- interpreted by the DB2eCLP application. It will
-- disconnect and connect again to the DB2 Everyplace database
-- using:
-- SQLConnect(hdbc, "something", SQL_NTS, "jsk", SQL_NTS, "hemligt", SQL_NTS);
--
-- Now, we have to create the first authorized user. When the
-- first user is created it has to have the same name as the
-- logged in user and the same password:
--
CLP:> GRANT ENCRYPT ON DATABASE TO "jsk" USING "hemligt" NEW "hemligt";
-- Notice that for GRANT the name and passwords need to be inside
-- double quotes. This is because they are case-sensitive, and
-- the statement is passed directly to DB2 Everyplace.
--
-- Now that we have an authorized encryption user we can create the
-- encrypted table:
--
CLP:> CREATE TABLE english(number INT, word VARCHAR(32)) WITH ENCRYPTION;
CLP:> INSERT INTO english VALUES(1, 'one');
CLP:> INSERT INTO english VALUES(3, 'three');
CLP:> INSERT INTO english VALUES(4, 'four');
CLP:> INSERT INTO english VALUES(5, 'five');
CLP:> INSERT INTO english VALUES(7, 'seven');
CLP:> INSERT INTO english VALUES(99, 'ninety nine');
-- Just have a look at the data.
CLP:> SELECT * FROM english;
NUMBER WORD
----------- --------------------------------
1 one
3 three
4 four
5 five
7 seven
99 ninety nine
6 row(s) returned.
-- Select a large random number in Swedish:
--
CLP:> SELECT * FROM swedish WHERE nummer > 42;
NUMMER ORD
----------- --------------------------------
99 nittionio
1 row(s) returned.
-- Select a large random number in English:
--
CLP:> SELECT * FROM english WHERE number > 42;
NUMBER WORD
----------- --------------------------------
99 ninety nine
1 row(s) returned.
-- Translate 'fyra' to english:
--
CLP:> SELECT word FROM swedish, english WHERE number = nummer AND ord = 'fyra';
WORD
--------------------------------
four
1 row(s) returned.
-- Get a translation table:
--
CLP:> SELECT number, ord, word FROM swedish, english WHERE number = nummer;
NUMBER ORD WORD
----------- -------------------------------- --------------------------------
1 ett one
3 tre three
4 fyra four
5 fem five
7 sju seven
99 nittionio ninety nine
6 row(s) returned.
--Attempt to authorize another user to access the encrypted data with her
-- own password:
--
CLP:> GRANT ENCRYPT ON DATABASE TO "xin" USING "notKnown" NEW "notKnown";
Statement failed [sqlstate = 42506].
-- Failed because the user who is logged in must validate himself
-- in order to add a new user, this is done by providing his password
-- after the USING clause.
--
CLP:> GRANT ENCRYPT ON DATABASE TO "xin" USING "hemligt" NEW "notKnown";
-- Let's reconnect with the new user:
--
CLP:> CONNECT TO samething USER xin USING notknown;
Statement failed [sqlstate = 42505].
-- This fails, because the password is not the same, thus will not generate
-- the same key and access is denied.
--
CLP:> CONNECT TO samething USER ksin USING notKnown;
-- This will not fail, because the user ksin does not exist, and we therefore
-- do not attempt to authenticate the user.
-- However, using SQLGetInfo one can distinguish this case
-- from the case where an user was successfully authenticated.
--
CLP:> SELECT * FROM swedish;
NUMMER ORD
----------- --------------------------------
1 ett
3 tre
4 fyra
5 fem
7 sju
99 nittionio
6 row(s) returned.
-- Selecting non-encrypted data works fine, however encrypted data cannot
-- be read/updated unless an authorized user is connected:
--
CLP:> SELECT * FROM english;
Statement failed [sqlstate = 42501].
-- Connect as the new user, finally with correct username and password.
--
CLP:> CONNECT TO samething USER xin USING notKnown;
-- Verify that we are authenticated and can access the data.
--
CLP:> SELECT * FROM english;
NUMBER WORD
----------- --------------------------------
1 one
3 three
4 four
5 five
7 seven
99 ninety nine
6 row(s) returned.
-- Add another user:
--
CLP:> GRANT ENCRYPT ON DATABASE TO "thf" USING "notKnown" NEW "heimlich";
-- List currently existing users:
--
CLP:> SELECT username, grantorname FROM "DB2eSYSUSERS";
USERNAME GRANTORNAME
------------------- -------------------
jsk jsk
xin jsk
thf xin
3 row(s) returned.
-- Again connect as "jsk":
--
CLP:> CONNECT TO itagain USER jsk USING hemligt;
Statement completed successfully.
-- Attempt to change the password to "secret":
--
CLP:> GRANT ENCRYPT ON DATABASE TO "jsk" USING "secret" NEW "secret";
Statement failed [sqlstate = 42506].
-- Ah, we failed because we need to supply first our old password and then
-- the new password:
--
CLP:> GRANT ENCRYPT ON DATABASE TO "jsk" USING "hemligt" NEW "secret";
-- Try the new password:
--
CLP:> CONNECT TO itagain USER jsk USING secret;
-- Make sure we can access encrypted ata:
--
CLP:> SELECT * FROM english;
NUMBER WORD
----------- --------------------------------
1 one
3 three
4 four
5 five
7 seven
99 ninety nine
6 row(s) returned.
-- Let's remove encryption privilege from "xin":
--
CLP:> REVOKE ENCRYPT ON DATABASE FROM "xin";
-- List users
--
CLP:> SELECT username, grantorname FROM "DB2eSYSUSERS";
USERNAME GRANTORNAME
------------------- -------------------
jsk jsk
thf xin
2 row(s) returned.
-- Connect again to the now non-existing user, without error.
--
CLP:> CONNECT TO thedatabase USER xin USING idontknow;
-- Attempt to do encryption operations without authorization:
--
CLP:> SELECT * FROM english;
Statement failed [sqlstate = 42501].
CLP:> DROP TABLE english;
Statement failed [sqlstate = 42501].
CLP:> REVOKE ENCRYPT FROM "jsk";
Statement failed [sqlstate = 42601].
CLP:> GRANT ENCRYPT ON DATABASE TO "xin" USING "idontknow" NEW "idontknow";
Statement failed [sqlstate = 42502].
-- Connect as "thf":
--
CLP:> CONNECT TO thedatabase USER thf USING heimlich;
-- Check that we can read encrypted data:
--
CLP:> SELECT * FROM english;
NUMBER WORD
----------- --------------------------------
1 one
3 three
4 four
5 five
7 seven
99 ninety nine
6 row(s) returned.
-- Let's remove the connected user's privilege:
--
CLP:> REVOKE ENCRYPT ON DATABASE FROM "thf";
-- Make sure he cannot access the data anymore:
--
CLP:> SELECT * FROM english;
Statement failed [sqlstate = 42501].
-- If we connect to the database as the only remaining user "jsk"
--
CLP:> CONNECT TO thedatabase USER jsk USING secret;
-- We remove the connected user, that user can not access the data anymore.
-- Actually, there is no way to access the encrypted data ever again.
--
CLP:> REVOKE ENCRYPT ON DATABASE FROM "jsk";
-- Make sure there are no users left:
--
CLP:> SELECT username, grantorname FROM "DB2eSYSUSERS";
USERNAME GRANTORNAME
------------------- -------------------
0 row(s) returned.
-- We should now not be able to access the encrypted data.
--
CLP:> SELECT * FROM english;
Statement failed [sqlstate = 42501].
-- This concludes the example session.
Zugehörige Tasks