Re: [Q] Security Paranoia....

From: Rick Wessman <Rick.Wessman_at_oracle.com>
Date: 30 Aug 2001 09:07:35 -0400
Message-ID: <soc7kvl3dd4.fsf@rwessman-pc.us.oracle.com>

Hi, Donovan:

donovan_at_namsys.com.au (Donovan J. Edye) writes:

> G'Day,
>
> I am the database owner of a database called MyDB but not the database
> adminisitrator for the oracle server. The information that is
> contained in MyDB is sensitive needs to be secured from prying eyes.
> The only access to the database would be via a set of stored
> procedures.
>
> So is it possible to:
>
> - Encrypt the data in the database

Yes, using the DBMS_OBFUSCATION_TOOLKIT package.

> - Still write SQL such as SELECT * FROM MyTable WHERE AField = 'blah'
> within the stored procedures

Yes. However, you need to use views and a stored procedure. For example, what you want to do would have to re-written as:

CREATE OR REPLACE PACKAGE encryption AS

    FUNCTION decrypt(data VARCHAR2);
END encryption;

CREATE OR REPLACE PACKAGE BODY encryption AS

    MyKey RAW(16) := INSERT KEY HERE
    FUNCTION decrypt(data VARCHAR2) IS
    BEGIN
        RETURN DBMS_OBFUSCATION_TOOLKIT(data, MyKey);     END decrypt;
END encryption;

CREATE OR REPLACE VIEW MyTable AS

   SELECT encryption.decrypt(AField) FROM TheActualMyTable;

> - Encrypt the stored procedures

No, but you can obfuscate them using the WRAP utility. It does an effective job of making them unreadable.

>
> In short I don't want the dba or any other super user to be able to
> interact or see the data in the db in any way (tables, stored procs
> etc) and only want external users to access the data from a stored
> procedure interface. Users would be granted access to the stored
> procedure interface via a specified user name and password. The only
> things they would be able to see would be the stored procedures and
> execute them, but not the content of the stored procedures. If this is
> achievable then are there any other additional considerations should
> this database participate in replication?
Replication should not be affected as the data would just be copied over to the second database - not decryption or encryption would be needed.

All that said, I would think long and hard about whether or not you want everything to be encrypted. It's slower (obviously) and may prevent administrators from doing necessary operations.

Now comes the topper: key management. Somehow, you need to secure the key of the DBA. One method is to put the key into a wrapped package body. But what happens if the package is accidentally dropped. You have to ensure that you can recover the package.

Another factor is that the key should be changed periodically to lessen the chance of compromise. For the interval that the key is being changed, user activity will have to be suspended. Also, you need to be aware of the possibility that the database may crash (What? Oracle crash? Never! :-) during the key switch and that your data may become unavailable without manual intervention. Oracle is very robust, but unexpected events do happen.  

Check out http://technet.oracle.com/deploy/security/ for information.

                                        Rick

>
> TIA
>
> -- Donovan
> donovan_at_namsys.com.au

-- 
                                Rick Wessman
                                Security Assessment Group
                                Oracle Corporation
                                Rick.Wessman_at_oracle.com

     The opinions expressed above are mine and do not necessarily reflect
                         those of Oracle Corporation.

Received on Thu Aug 30 2001 - 08:07:35 CDT