SDE and DBMS_CRYPTO

[EmadAl-Mousa]

why execute permission on DBMS_CRYPTO package needed by "sde" user while performing Geodatabase upgrade ?

this privilege is required since ArcGIS Version 10.1

[EmadAl-Mousa · Accepted solution]

starting from ArcGIS version 10.1 while upgrading your ArcSDE Geodatabase in Oracle, one of the pre-requisite required â??sdeâ?? user privilege is direct execution to the Package DBMS_CRYPTO.

grant execute on SYS.DBMS_CRYPTO to sde;

according to esri documentation: http://resources.arcgis.com/en/help/main/10.1/index.html#//002n0000002v000000

This granting has a relation to st_geometry configuration:

1. Allows the creation of a map member function for the ST_Geometry type, which is called whenever a spatial union or intersection is performed.

2. Upgrade a map member function for the ST_Geometry type, which is called whenever a spatial union or intersection is performed.

the question is: has this privilege has any relation with data encryption ?

Most Probably â??YES â?? : according to Oracle Documentation â??DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications. It provides support for several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm.â??

So, if you enable data encryption at rest on your database management system, then sde will be able to decrypt data for your geodatabase system.

my blog reference: http://geodatamaster.wordpress.com/2014/01/24/sde-and-dbms_crypto/

if anybody has any further information/correction regarding this topic, please share it.

Regards,

[MarcoBoeringa]

Emad,

You raise a good question. The Help is not very clear, at least, I don't understand this sentence:

"Allows the creation of a map member function for the ST_Geometry type, which is called whenever a spatial union or intersection is performed."

This leaves me puzzled. To be honest, I am not an Oracle expert, so I had to look up what the "map member function" is, I found the below linked Oracle Help page and the section / headline "Map Methods" therein, most useful. It seems Map Methods are special cases of sorting functions, that allow you to define a custom method of sorting, as in the example in this page based on an area calculation of a geometry:

Basic Components of Oracle Objects

I am not entirely sure how this relates to the "spatial union or intersection" operations as listed in the ArcGIS Help? It is all a bit "cryptic" :eek:

Anyway, real data should never be owned by the SDE account in your database, as Vince has stressed a dozen times here on the Forums, and this may be another reason. If the SDE account doesn't have privileges on real data, the decrypting privileges probably are "harmless"...

The NSA can probably decrypt everything, but if you pull the plug of your internet connection, I don't think they will have much to explore. 😉