Decrypting a specified column using key in SAS DI studio

[upadhi]

there a case of encrypting a specific column on DB (Oracle)?

there  is an encryption on a specific column in DB (oracle). We will recieve the decryption key from client.

Can we decrypt the column in SAS DI studio?

[Patrick]

SAS DI Studio is mostly a SAS Metadata driven code generator. There isn't any out of the box transformation for your decryption task but you can always use user written code. So if you can solve something in the SAS environment you're in, you will also always be able to implement such a solution using DIS.

Your decryption question as such needs a bit more detail so here just on a high level:

If you're pulling the data directly out of Oracle then I believe how that normally works is:

- View in Oracle which surfaces the data unencrypted (but view only accessible by user under which the process runs)

- Encrypted data transfer from Oracle to SAS (consult the SAS/Secure manual for this)

If you would have to do the decryption on the SAS side then I guess besides of the decryption key (if symmetrical)  you would also need to know the algorithm used AND if the code pages between Oracle and the SAS session are not the same (to be expected) then you would also need to convert the de-crypted plain text string to the code page of your session.

[upadhi]

Hi Patrick,

Thanks for the info. Will get more updates from client on this one.

Also, will be any effect on the performance if decryption is done on SAS side.?

[Patrick]

@upadhi 

I would ask your client if they've implemented already this way with other applications consuming data from Oracle and if yes get the details how they've done it. I would assume it requires calling some decryption software out of whatever application (i.e. SAS).

Impact on performance? Well, yes, it's additional processing so I guess it will impact but can't tell you to what extend. I've never implemented something like this.

I believe the way to go is via Oracle side decryption (=clear text for you) and secured data transfer. I would try to understand the customer's reasons in detail if they don't want to take such an approach.