4 Replies Latest reply: Jul 22, 2011 2:13 PM by vivalavida RSS

    database permissions: privilege to truncate a table

      we moved some database-schemas from our french location to our german location. These database schemas are needed and used for some projects realized by the IBM InfoSphere software. We are not able to change or modify the InfoSphere processes and have to keep them as they are. InfoSphere needs a user which connects to an Oracle database. This user must have the ability to truncate tables in all of the InfoSphere schemas. The collegues in France realized that by granting DBA privileges to that user. The problem is that we have several other schemas - used by other applications as InfoSphere - in our database (that circumstance must be accepted and is not the point of this thread). Therefore granting DBA privleges to that user is certainly not a pretty good idea.

      Is it possible to give a user the ability to truncate a table just for certain schemas? We were thinking about a procedure which proves the schema- and table-name, meaning that only certain tables of a given user (e.g. stored in a lookup-table) are able to get truncated. Might be database vault a possible solution?

      Any suggestions would be truly appreciated