Home » RDBMS Server » Security » Security & Encryption (Oracle 12c SE2)
Security & Encryption [message #689211] Wed, 18 October 2023 10:55 Go to next message
LandonStatis
Messages: 3
Registered: October 2023
Junior Member
Hey all, I was looking to get some input from anyone who may have been in our shoes before, and am curious on how they resolved this business requirement.

We have a number of Oracle database instances running.  We are running Oracle 12c SE2.   A new requirement has come forward and we need to isolate and encrypt all PII information in the different instances.   Standard Edition does not have any of the security features that Enterprise has.  And, the cost of purchasing and running Enterprise on the existing machine due to the number of cores is not something the company is in a position to do.

The ideal situation is to have the PII isolated, and no other instance can query the PII data over any DB Link.   Currently we have PII data scattered in different tables, in different schemas, and queries that join PII & non PII tables.

So, our thought was to move this PII data to another database instance, perhaps a smaller machine running Enterprise with a minimal number of cores, or maybe set up a Postgres DB and have all the PII data kept there.  Procedures & queries would have to be changed / moved from the current location to the PII instance, and queries that require PII data would have to be initiated from there.  In either case, it will mean a lot of changes to stored procedures, as well as to the application.  This will affect application performance, customer experiences, etc.

We're sure that we are not the first company to be in this position, and while Oracle wants to sell us licenses to Enterprise (of course), we are wondering what others have done to overcome this.

Many thanks for any useful input.
Re: Security & Encryption [message #689212 is a reply to message #689211] Wed, 18 October 2023 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The first point to address is: you want to protect against what?
Server or disk theft or hack, backup theft, illegal access from illegal way, illegal access from legal way, user access, DBA access, sysadmin access...
Each of these are optimally addressed via different methods.

The only method that protects the data from all these is data encryption by the application (above all if you have not EE) then the data are never in clear in the database server or its backup.
Now the problem is to protect the application from illegal access .
And if you have multiple applications having to access the PII you have to spread the encryption/decryption method in those ones: the data are in clear only where they are used... but then they are in the computer memory so, if you used an application server how to protect it from sysadmin, and if you used a direct access from client to database, how to protect the application against a hacker...

So the second point to address is: how far you want to protect the data?

Re: Security & Encryption [message #689213 is a reply to message #689211] Wed, 18 October 2023 12:58 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Hi there - I don't understand what you mean by "isolation", but a reasonably standard way to encrypt without the EE Advanced Security Option is to cover the tables with views that decrypt on select and use triggers to encrypt on UPDATE and INSERT. It should be transparent to your application. I've configured this for customers a few times, drop my boss a note if you'ld like any assistance.
--
John Watson
Oracle Certified Master DBA
Oracle Certified Professional OCI Architect
http://skillbuilders.com
Re: Security & Encryption [message #689214 is a reply to message #689213] Wed, 18 October 2023 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I did that too, the drawbacks are:
The encryption algorithm is known for the DBA which has access to view and procedure/package definitions.
Encryption key and parameters as well as data in clear are in the server memory (SGA...) and so accessible to sysadmin and DBA.

If these people are trusted this is a good method... if you encrypt the network.

Re: Security & Encryption [message #689215 is a reply to message #689214] Wed, 18 October 2023 16:11 Go to previous messageGo to next message
LandonStatis
Messages: 3
Registered: October 2023
Junior Member
When we refer to isolating the data, we mean to remove it from the unencrypted instance and place it in an instance where the data would be encrypted, perhaps behind a firewall or some other measure, and not accessible from the outside.

Procedures and queries that currently select from or join to those PII tables would have to be moved to this secure instance.   So rather than querying the PII data over a DB Link, the application would need to be changed to connect to this secure instance and could query the non-PII data over the DB Link.   That's one way of doing it.

The application could be broken into 2 calls, 1 to the PII data and another to the non-PII data, but then all the joining and combining of data would be at the application layer, which would probably not perform too well and be difficult to maintain.

Postgres allows column level encryption, but then we have to deal with FDW and that in itself is slow and would not perform well.  It would also mean tons to application changes as well.

So, trying to see what options are out there that would help us meet this business need, at a reasonable cost in terms of dollars and / or resources.



Re: Security & Encryption [message #689216 is a reply to message #689215] Thu, 19 October 2023 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, you first have to answer the 2 questions I mentioned.

The solution John suggested would work without changing anything in your application(s) where the data reside with the drawbacks I said.

Re: Security & Encryption [message #689218 is a reply to message #689215] Thu, 19 October 2023 03:01 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
To what standard do you need to conform? For example, PCI DSS compliance requires encryption for data on public networks (that's easy - just one parameter in your sqlnet.ora file) and data at rest (also easy - either programmatically, or have your OS do it for the file system), but does not require it to be encrypted in memory. There must be some doc detailing the requirement you are facing.
Re: Security & Encryption [message #689219 is a reply to message #689218] Thu, 19 October 2023 06:47 Go to previous messageGo to next message
LandonStatis
Messages: 3
Registered: October 2023
Junior Member
PCI compliance, yes this is one reason.   Protection against any SQL Injection and other attacks, another reason.

Our goal is to have the data encrypted at rest in the DB, and when the data is called for pass it through some API or other stored procedures to unencrypt.   The data may be called for from the application, or from internal batch cycles, etc.   But it would be in its own PII instance, protected via firewall and / or other security measures.

Postgres has something called PG Sodium that we've been testing with, does anyone have experience with this?  It's free, and allows column level encryption.  We would rather stay within Oracle, as the changes needed would be easier to implement, but just wondering.

We can move all the PII data to a new instance and use AES128 or some other SALT type of algorithm and do it ourselves, but we have over 100 web applications that connect to us, so it would be a huge undertaking to implement it that way.  Any other more cost-effective ways, outside of EE?

Thanks!
Re: Security & Encryption [message #689220 is a reply to message #689219] Thu, 19 October 2023 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The one we suggested?

Re: Security & Encryption [message #689221 is a reply to message #689219] Thu, 19 October 2023 12:25 Go to previous message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
You really need to define your requirement. As Michel keeps saying! To what standard must you comply? I suggested one, PCI, but it seems that is not correct. So which standard is it?

For example: Encryption. If you tell your OS to do it, then if someone steals your disc array and sells it on eBay, you are protected. And it costs you nothing. Is that good enough? Would that comply with the standard?

ps - you mentioned SQL injection. That is nothing to do with anything mentioned so far. For that, you need code reviews. You can do them yourself, or pay a well reputed company to do it for you. Better open a fresh post for that.  
Previous Topic: USB security token login
Next Topic: ORA-01031 permission denied
Goto Forum:
  


Current Time: Mon Apr 22 06:42:56 CDT 2024