Concern about blanket privileges for JChem Cartridge owner

Is it absolutely necessary for the JChem Cartridge owner have such a dangerous blanket privilege as DELETE ANY TABLE? In our production environment we try to steer away from something like that, even if it involves a more detailed list of grants made to a "privileged" user. Can we focus the permissions to include tables of only those accounts of Jchem Cartridge users?





Thanks for any comments you have to offer,





Julie Myhre

Theoretically, you do not necessarily have to grant these blanket privileges. Theoretically, it would be sufficient to grant more restricted variants of the privileges listed in the documentation -- restricted to the schemata of the cartridge users.





However, I do not see a way how to do this in practice. For example: during index creation, the index table is created and immediately populated with the precomputed chemical information. Do you know of a way to grant INSERT privilege on a table which does not exist yet? I do not. (Do not completely trust my Oracle DBA experience, it does not go far beyond what developers typically need.)





Looking for other options to avoid this kind of privilege problems, I can think of a simple mechanism to use the jchem cartridge users' own identity in the JChem Server (Tomcat) -- instead of the identity of a "superuser". The mechanism would consist of a PL/SQL procedure accepting as parameter the password of the current jchem cartridge user. This PL/SQL procedure could be called at the beginning of each session and would pass the password to the JChem Server (Tomcat) where it could be used when the credentials of the given jchem cartridge user are needed. (The password would be kept in a transient fashion in main memory and could be reused across several sessions until it is changed via the same PL/SQL procedure or until Tomcat is shut down.)





This mechanism would certainly not be very expensive to implement. If it was a solution to your concerns over privileges and otherwise you were happy with the cartridge, I am sure we could consider implementing this solution.





Peter

Understood, since those tables are created on the fly. So, we are wondering if it is possible that the query user do the query...creating and maintaining the tables. Why does the cartridge owner do the work? There is probably a part of the cartridge environment we are not understanding - we picture it as a special set of packages/scripts to be executed.





Thanks for your patience - it does seem like the PL/SQL mechanism might work pretty well if the query user cannot directly do his own querying.

It is a speficity of the JChem Cartridge implementation that, in order for our core chemical functions (implemented in Java) to perform with acceptable speed, most of the computation has to be moved into a Java runtime capable of native compilation (I call this external computing server JChem Server). This means a fairly strong decoupling from the Oracle environment including the necessity to open database sessions in the JChem Server (running in Tomcat). In other words, separate database sessions are opened and used in the JChem Server on behalf of database user operations initiated in Oracle stored procedures in internal sessions.





The immediate solution (currently in place) is that the identity of an all-powerful "superuser" is used for all database connections which are opened in the JChem Server. (This "super" user does not necessarily have to be the JChem Cartridge owner. It can be any database user having the required privileges. It is just a natural choice to invest the cartridge owner with these privileges.)





My proposed PL/SQL mechanism makes it possible to open sessions in the JChem Server using the identity of the initiator user: the credentials of the user are simply passed from the Oracle stored procedures to the JChem Server. (The user id of the current sessions's user can be programatically obtained, but not the password.) With the proposed mechanism the user application must (immediately after it opened a session for a given user) perform the extra step of calling the PL/SQL procedure with the same password that was used to open the session.