加密和解密示例

--   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 ;
	create role high_privileges_group ;
	grant role high_privileges_group to PrivUser ;

	grant connect to low_privileges_group ;
	create role low_privileges_group ;
	grant role 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 (High_Group) 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