Using Stored Procedures and Triggers for Security

Stored procedures and triggers allow for a very finely grained object-level security. Both are compiled modules implemented in some procedural language and stored inside the RDBMS server.

The idea behind using stored procedures for security is to encapsulate certain business logic inside persistent modules that are stored server-side, and restrict a user's database communication to the use of these procedures only. For example, you can implement a set of stored procedures in such a way that every SELECT, UPDATE, and DELETE would go through the stored procedures. Users could be granted these privileges only through stored procedures and denied direct access to the tables that these stored procedures are based upon. Inside your stored procedures you can implement business security rules that govern the way data is inserted, queried, updated, or deleted. You can even use stored procedures for creating database objects (though there might be some implementation-specific restrictions).

The facilities provided by standard vanilla SQL to implement sophisticated business logic are not adequate. While SQL92 and SQL99 both specify persistent server-side modules, their implementation details developed by the various database vendors are far from being standardized — both in syntax and language. Oracle uses its own PL/SQL procedural extension for the SQL, the Microsoft SQL Server 2000 uses its own Transact-SQL dialect, and IBM uses its own IBM SQL.

Note

Oracle and IBM also allow for using Java for to create stored procedures, and the MS SQL Server 2000 sponsors DTS (Data Transformation Services) that, while not being a stored procedure equivalent, could be used to access and transform data through VBScript and ActiveX objects.

Procedural extensions (as well as Java Programming Language) are beyond the scope of this book, which provides only very basic examples on how they can be used for security purposes.

Here is a simple procedure that handles insert into the CUSTOMER table of the ACME database.

Note

This example assumes existence of the table DELINQUENT_CUSTOMER, which collects information about former customers that were dropped due to nonpayment; the actual ACME database does not contain such a table.

In MS SQL Server syntax, this stored procedure might be implemented as follows:

CREATE PROCEDURE sp_cust_insert
		@cust_id INT, @cust_paytermsid INT, @cust_salesmanid INT, @cust_status
		VARCHAR(1), @cust_name VARCHAR(50), @cust_alias VARCHAR(15), @cust_credhold
		VARCHAR(1) AS IF NOT EXISTS( SELECT cust_id_n
		FROM delinquent_customer WHERE cust_name_s = @cust_name) INSERT INTO customer (
		cust_id_n, cust_paytermsid_fn, cust_salesmanid_fn, cust_status_s, cust_name_s,
		cust_alias_s, cust_credhold_s ) VALUES ( @cust_id, @cust_paytermsid,
		@cust_salesmanid, @cust_status, @cust_name, @cust_alias, @cust_credhold ) ELSE
		RAISERROR ('Delinquent customer',19,2)

What is actually happening here is that the application that calls this stored procedure (sp_cust_insert) passes seven parameters to it, one of the parameters being new customer's name. Before inserting the data into the table CUSTOMER, the procedure checks whether this customer is not already on the delinquent customer list. It allows the record to be added only if no such customer exists there; otherwise, it produces an error and passes it back to the calling application with a description of the error and the severity of it.

This procedure uses Transact-SQL language (which is generally out of scope of this book). The syntax in the sample code was made as simple as possible, and kept to a bare minimum. The syntax for the Oracle RDBMS and IBM DB2 UDB would be quite different, though the idea would be the same.

Cross-References

Stored procedures, user functions, and triggers are discussed in Chapter 14.

The same functionality can be implemented as a trigger. A trigger is a special kind of stored procedure that executes automatically, in response to a certain event. In the previous example, the event is the INSERT statement executed against the CUSTOMER table. You could set up a trigger to fire (execute) whenever an application tries to insert, update, or delete data from the table.

Note

The ACME database has an example of using a trigger for security-related auditing purposes — it fires whenever an update takes place and records data about the user who made the changes.

Data encryption

Encryption is a method to convert information from a human readable format into a format that is unreadable by humans. The encrypted data normally can be decrypted using the same process (algorithm) that was used to encrypt it. Encryption is not a part of the SQL standard; therefore each vendor provides different encryption-related services.

The data inside the RDBMS is stored as plain text(ASCII, Unicode), or binary (BLOBS, IMAGE, and similar data types). To prevent this data from being viewed by unauthorized users (who happen to be granted access to the table that contains it), or to send a data extract over an unsecured network, the data could be encrypted. The data also could be encrypted via some client software before it is entered into the database, or it could be done inside the RDBMS using its own facilities.

This provides an additional level of security, when in order to view data in human readable format — be it text or pictures, audio files, or executable files — a user would need a password and decrypting facilities, either on RDBMS or inside his/her client software.

While maintaining high security for authentication, user access, public key infrastructure, and so on, Oracle 9i does not provide much of the user-accessible encryption functionality within the database itself, but it compensates with add-on products. The only things you can encrypt using the RDBMS-supplied functionality are the PL/SQL code contained in Oracle's package specifications and package bodies, and stand-alone procedures and functions using the utility wrap.exe (found in directory $ORACLE_HOME/bin on Unix, and \Oracle9\bin on Windows machines). Oracle 9i also provides an obfuscation package (DBMS_OBFUSCATION_TOOLKIT), which provides a means to hide the source code and data from prying eyes by converting the code into ASCII gibberish. It uses the DES implementation algorithm. Obfuscation differs from encryption by being more secure and not limited to a range of human readable characters.

IBM DB2 UDB 8.1 provides several functions for data encryption (listed in Table 12-13). For example, this query returns product brand from the ACME database table PRODUCT:

SELECT prod_brand_s FROM
		  product PROD_BRAND_S ---------------- SPRUCE LUMBER STEEL NAILS

Table 12-13: IBM DB2 UDB Encryption Functions
Function	Description
ENCRYPT (<data to encrypt>, <password>, <hint>)	Encrypts CHAR or VARCHAR data (up to 32633 bytes long) using a password — CHAR or VARCHAR string (at least 6 bytes, no more than 127 bytes long). A HINT (CHAR or VARCHAR, up to 32 bytes long) is an optional parameter; if used, it provides capability to recall a password using a hint expression via the GETHINT function.
DECRYPT_BIN (<encrypted data>, <password>)	Decrypts binary data (BLOB,CLOB, etc.) encrypted with ENCRYPT function.
DECRYPT_CHAR (<encrypted data>, <password>)	Decrypts character data encrypted with ENCRYPT function.
GETHINT (<encrypted data>)	This function returns a hint for the encrypted data, if such a hint was found. Hopefully, the user could recall the password using the hint.

To produce encrypted data — for example, a list of brands from the PRODUCT table (to be sent to a branch over an unsecured network) — in IBM DB2 UDB, the following SQL statement could be used:

SELECT ENCRYPT(prod_brand_s,
		  'PASSWORD') encrypted FROM product ENCRYPTED
		  --------------------------------------------------
		  x'00E61AFFE404A6D596757C7CC7AC70467884E127B6A50726'
		  x'00DC24FFE404A0D5F736C8A4156922A6709DD5D609EBE762'

To decrypt the above seemingly senseless string of characters, use the DECRYPT_CHAR function (since we are using character data), with exactly the same password, to restore the data into its original form. Numeric data cannot be encrypted with this function directly (you can also encrypt binary or character representation of numbers).

The Microsoft SQL Server 2000 allows you to encrypt (or encrypt by default) the following:

Login and application role passwords (stored server-side)
Stored procedure body (the actual implementation code)
User-defined functions body (the actual implementation code)
View definitions (the actual SQL statements)
Triggers (the actual implementation code)
Rules and defaults definitions
Data packets sent between the SQL Server and the client application

Logins and passwords are stored in MS SQL Server 2000 system tables and are always encrypted. The algorithm used for this is proprietary, and passwords cannot be viewed directly (unless NULL is used as a password).

When a stored procedure, function, view, or trigger is compiled and saved in the SQL Server, the creator has an option to encrypt the actual implementation code to prevent it from being viewed by other users or third parties who have access to the database system objects. The encryption option is in the CREATE statement. For example, to encrypt one of the ACME database views inside the MS SQL Server 2000, you would use the following statement:

CREATE VIEW v_customer_status
		  ( name, status ) WITH ENCRYPTION AS SELECT cust_name_s, cust_status_s FROM
		  customer

The WITH ENCRYPTION option saves the actual Transact-SQL code inside the SYSCOMMENTS system table in encrypted form rather than in standard plain text. There is a catch to encrypting SQL Server objects — once encrypted, the object cannot be modified; if you need to do so, you would have to drop the object and recreate it.

Note

There are literally hundreds of data encryption algorithms — both custom and public. To devise and implement an encryption algorithm requires familiarity with programming principles — in addition to advanced math. Here are some popular algorithms in use today: DES (designed by IBM in 1970, adopted by NIST in 1976 for unclassified data), RC5 (from RSA Data Security), CMEA (developed by the Telecommunication Industry Association to encrypt digital cellular phone data), FEAL (developed by Nippon Telephone & Telegraph), TEA, MD5, Tiger, and CAST — to name just a few.

The data sent between the SQL Server 2000 and a client application can be encrypted using Secure Socket Layer (SSL) encryption if TCP/IP is chosen as a communication protocol — which is usually the case for most networks and Internet connections. When multiprotocol is employed, an application must specifically call the Windows RPC encryption API (application programming interface). The actual strength of such an encryption (the length of the encryption key) depends on the version of the Windows OS where the software is installed.

Note

SSL (Secure Socket Layer) is a protocol initially developed by Netscape Communications to secure the transfer of documents over the Internet. It uses so-called public key encryption data. By convention, the SSL connection Internet link starts with the prefix https:// as opposed the standard http:// (Hypertext Transfer Protocol).

A very simple password-based data encryption can be implemented in Transact-SQL using the Microsoft SQL Server 2000 bitwise operator XOR (logical, exclusive OR). This type of encryption is very easy to implement — and break.

XOR encryption uses a key (password) to encrypt the text. Each letter in the computer world is represented by a specific ASCII/Unicode or ECBCD number. When encrypting data with a key, each character of the data is XORed with a corresponding character of the key; reversing the operation restores the original data.

The XOR operation follows rules of Boolean logic:

0 XOR 0 = 0 0 XOR 1 = 1 1
			 XOR 1 = 0 1 XOR 0 = 1

The operation applies to one bit at the time. For more a detailed introduction of Boolean algebra, refer to Appendix L.

Here is an example of encrypting string ABC with a password B. In the ASCII codes table these letters are assigned codes 65, 66, and 67, respectively. In binary notation they would look like this:

A 1000001(ASCII 65) B
			 1000010(ASCII 66) C 1000011(ASCII 67)

The result of XORing these with B (binary 1000010), bit by bit, would look as follows:

	A	B	C
Characters to encrypt	1000001	1000010	1000011
Password 'B'	1000010	1000010	1000010
Results of the XOR operation	0000011	0000000	0000001
Result in binary format	ASCII (3)	ASCII (0)	ASCII (1)

The result of the XOR operation gives three nonprintable characters, which could be converted into their previous form using the same operation (XOR) and password (1000010) — shown in bold.

	ASCII ( 3 )	ASCII ( 0 )	ASCII ( 1 )
Encrypted characters	0000011	0000000	0000001
Password 'B'	1000010	1000010	1000010
Result in binary format	1000001	1000010	1000011

The corresponding SQL statement in the SQL Server 2000 is:

SELECT 65 ^ 66 'a_XOR_b' ,66
			 ^ 66 'b_XOR_b' ,67 ^ 66 'c_XOR_b' a_XOR_b b_XOR_b c_XOR_b -----------
			 ----------- ----------- 3 0 1 (1 row(s) affected)

The result gives a string of ASCII codes, which represent nonprintable characters( since ASCII codes for 3, 0, and 1 are End of Text, NULL, and Start Of Heading, respectively). For passwords longer than one character, you may apply XOR to the consecutive letters in the password with that of the data.