Overview   Technical   FAQ   APIs

• Why would I want to use column encryption APIs?
• How do I use the APIs in SQL 2000?
• How do I use the APIs in SQL 7?
• I want to distribute my application with or without column encryption.
• Can I use the APIs to protect columns from the SA?
• Where can I see a list of the APIs?

The main purpose of the APIs is to protect columns, fields, or rows from certain users who are otherwise authorized to access a table. For example, while you may want everyone to be able to access the customer table, you may want only certain users or roles to access the credit card column. You can accomplish this with the column encryption APIs. 

While there are many ways of implementing this, one way is to create a view, function, or procedure which retrieves decrypted column data. Then you only grant rights to the procedure or view to certain users/roles.

There are also APIs for encrypting and decrypting files. There are many possible uses for these, but one example is secure log shipping. If you are using xp_sendmail to ship a log, you could encrypt the log file before sending it.  And since Encryptionizer is so scalable, the receiver would not necessarily need Encryptionizer for SQL Server. They could have Encryptionizer for MSDE or Encryptionizer DE instead.

In SQL Server 2000 or MSDE 2000, column encryption APIs are implemented as User Defined Functions, or UDFs.  They can be used in: sql commands (Select, Update, etc), stored procedures, user defined functions, views, triggers, jobs, etc.

For example, the following is a UDF that retrieves the unencrypted credit card number column.

Create Function fnGetCreditCardData

    (@nCustID Integer, @nKeyHandle Integer)

    Returns Varchar(16)

As Begin

    Declare @cc As Varchar(16)

    select @cc = ( Select fn_n_decrypt(CreditCard,0,@nKeyhandle)

		      from Customer where CustId = @nId )

    Return @cc

End

or even just in-line as

    select @cc = ( Select fn_n_decrypt(CreditCard,0,@nKeyhandle)

                        from Customer where CustId = @nId )

You can make it even harder for unauthorized users/roles to access the decrypted data by selectively granting execute permission to fnGetCreditCardData and/or fn_n_decrypt.

In SQL 7 or MSDE 7, column encryption APIs are implemented as Extended Stored Procedures.  (Although you can also use them in SQL 2000.) They can be used in: stored procedures, triggers, jobs, user defined functions (SQL 2000 only). Extended stored procedures cannot be used in Views or in-line SQL Commands (except for Execute).  For example, the following has the same result as the example above:

Create Procedure spGetCreditCardData

    @nCustId Integer, @nKeyhandle Integer,

    @cCreditCard Varchar(16) Output

As

    Declare @cc As Varchar(16)

    Select @cc = ( Select CreditCard from Customer Where CustId = @nId )

    exec xp_n_decrypt @cc, 0, @nKeyhandle, @cc Output

    Set @cCreditCard = @cc

You can make it even harder for unauthorized users/roles to access the decrypted data by selectively granting execute permission to spGetCreditCardData and/or xp_n_decrypt.

You can avoid maintaining two different versions of your source code. Besides the APIs, we also include "stub APIs" which you would distribute with releases that don't include column encryption. 

Yes! Implemented properly, APIs can be used to protect certain columns even from an SA or other system administrator. The evaluation version includes a sample VB.DBO application that shows one way of doing it.

Click here for a list of all the Encryptionizer APIs.

Overview   Technical   FAQ   APIs