SQL Bible [Electronic resources] نسخه متنی

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.

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.

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.

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.

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

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.

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.

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:

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.

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.