Q
Problem solve Get help with specific problems with your technologies, process and projects.

Storing documents in BLOBs vs. external FTP server

We were "sold" by Oracle consultants on the advantages of storing all our documents into BLOBs in the database instead of the external FTP server method we were using. We now find that you cannot trap an update to a BLOB field in a trigger.

We were "sold" by Oracle sales and technical consultants on the advantages of storing all our documents into BLOBs in the database instead of the external FTP server method we were using. We just converted 500,000 documents. We now find that you cannot trap an update to a BLOB field in a trigger. Anyone with just read access can select it into a local blob variable and change the contents of the blob and we would never know. These are legal documents and we can't audit the changes from the database side. Do you know any workaround for this?
The only way I know of how to do this is to create the table to hold the BLOBs in a new schema. Your application users do not have privileges to do anything with this table. Then create a stored procedure in this new schema which can update the BLOB contents. This stored procedure will also log an audit entry into an audit trail table so that you know who invoked the procedure and when. Grant EXECUTE on this stored procedure to your users. They only way they can update the BLOB is through the stored proc which also enforces your auditing requirements.

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close