The shocking truth: You can't create a read-only user on your E1 database!

Did you know that by default all users created on Oracle and MSSQL databases have full permission on all JDE tables? It's true. Not only that, this permission takes precedence over any restrictions you might place on a particular DB user such as making them read-only. This is hard-coded in to EnterpriseOne and Oracle has no plans to change this behavior.

Why is this? JDE is designed to insulate users and administrators from the hardware and database. This is part of the spirit of CNC - Configurable Network Computing. Yes, CNC actually means something other than "Person Who Gets Blamed For Everything". The CNC concept means that with only JDE tools a user can perform select, insert, update, delete, create, and drop operations on any table.

Because you don't have to know what database or operating system is being used, you don't need to have any specific database skills. Without ever leaving EnterpriseOne you can do full environment refreshes, table copies, add new indexes, and much more. The best part is that it is all controlled by JDE security. You never have to touch the database to do your day-to-day job.

As flexible as that is, there are often good reasons to directly access the database. One example is to do faster refreshes. In the middle ground are SQL fixes due to data issues. On the other end are bad reasons which (in my opinion) include providing read-only access and large exports of raw data. I'll talk about why those are bad in another post.

No matter what the justification is for providing direct access, the shocking truth is that every database user can view and manipulate all JDE tables and data on the system - and there is nothing you can to do change that.

I take that back. There actually is something you can do to change it. It's called Oracle Public Shutdown (OPS), a somewhat complicated procedure that removes these global permissions and allows administrators to have better security control at the database level. Once you've run OPS you can now create read-only users as well as any other levels of access you can think of.

Unfortunately for your administrative workload, OPS is not permanent. Every time a table is created or regenerated in OMW, every user gets full permissions reassigned to that object. Every ESU, ASU, and Update that contains tables will reset those permissions. An upgrade will reset many permissions. What this all means is that you have to come back to your database every so often and re-run OPS.

The next time someone asks for a database user, make sure there is a really good justification for their access. You'd be surprised how often they can do what they want inside E1. Plus, you don't want to give what is essentially DBA authority to just any user. Related tip: Make sure you have good backups.

If your client is concerned about the security of their database - especially if they have SOX or FDA requirements - they may need Oracle Public Shutdown. The GSI Database Team can help you evaulate your client's needs and work to secure your database.

Protecting your data from end-to-end

It’s pretty difficult to have a database without some protection for the data. You need a database user name and password to access the data at the very least. If you don’t want someone to see your data, you just don’t give them the credentials. That seems pretty simple, doesn’t it? Well, it is simple, but having a single user doesn’t allow for any flexibility in determining different levels of access.

Does everyone really need read/write access to every column in every table? Normally that answer is “no”. You’re probably going to need a read-only user, and maybe other users that are prevented from seeing sensitive data. It could get complicated setting up all those additional users and roles.

I’m not like the others

Maybe your installation is different and users don’t access the database directly. Their access is controlled by an application which handles all the security and permissions across the board. That application only needs a single user to connect to the data, and nobody but you knows the password. Well, you and the application administrator. That should be fine, right?

Even in a perfect world, there will be mistakes that can’t be corrected within an application. Will you, as the DBA, be performing all those corrections? What about analyzing the raw data to determine what corrections to make? Will you also be exporting raw data for business analysts to pore over? Chances are you won’t have the time or inclination to do all of that. You’re still going to need some additional database users with varying levels of authority.

Your data is still vulnerable

You’ve got users with specific permissions, you’ve got security in the application, and maybe you even have auditing enabled. The only people who can use the application or access the database are on the corporate network or VPN. You feel pretty safe right about now, don’t you?

What about operating system credentials? Who has access to the root or oracle account? What users have sudo rights? Anyone on the operating system can view or manipulate your data with OS superuser privileges. They don’t even have to access the database directly! Now imagine if someone outside your company – a hacker or corporate spy for example – finds an exploit that gives them access to the server operating system. Even if they can’t find a way to use SQL, they can get your data using OS commands as well.

Data encryption

Data is stored in the database in plain text. Despite all the user security you might have in place, the fact of the matter is that if someone gains access to your data files they’re going to be able to see the info. Even inside the company with application security you may be able to prevent people from seeing certain columns of data, but that data is still stored in the database in plain text. Unless you encrypt the data, your data is vulnerable.

The data sitting in a database is considered data at rest. This can be recent information or historical, archived data on a data warehouse. Data that is currently being manipulated or used in any way is data in use. Data moving across a network or other medium is data in transit. Data in in transit is usually encrypted by VPN or other active methods. Data in use is going to be in memory, and that is normally not encrypted. Data at rest is also generally not encrypted and depending on your database, you might not even have the capability to encrypt it at all.

Oracle Advanced Security

How do you protect your data in all its various states? To encrypt data at rest, you can use Oracle Advanced Security in Oracle 12c. Transparent Data Encryption (TDE) helps prevent attacks that attempt to bypass the database and read sensitive information from data files at the operating system level, from database backups, or from database exports. Oracle Advanced Security Data Redaction complements TDE by reducing the risk of unauthorized data exposure in applications, redacting sensitive data in query results before the data leaves the database.

What about data in use or in transit? For that you have to get serious. The Oracle Supercluster’s SPARC M7 processor protects, verifies, and encrypts data in use with built in, dedicated encryption accelerators. Oracle Solaris 11 includes cryptographic APIs which the ZFS uses to benefit from AES algorithm hardware acceleration on SPARC processors.

Data protection isn’t simple

Protecting your data isn’t as easy as having strong, secure passwords. Even with that your data is still unprotected and exposed. Whether that data is actively being used or if it’s sitting on an old, slow SAN somewhere, it’s vulnerable to a determined attacker. With Oracle 12c and Supercluster you can protect your data from end-to-end with little to no performance compromises.