We have a mature product that has traditionally been sold on SQL Server and Oracle, where such concepts as creating synonyms for sequences was a valid concept.
We have now ported it to DB2, and all works fine when the Application and Serucity Schemas that are the objects owners are also the Schemas that connect from the Application. However, we need to add an extra layer of security, namely dummy schemas that do not own any objects, but merely have S/I/U/D grants and synonyms to the object owner schemas.
This system has approx 300 sequences and the code is set in stone.
How do you suggest a stored procedure would help?
e.g. INSERT INTO TLOCK (C,I,blah,blah2) VALUES(106,SLock.Nextval,'blah','blah2');
would be an example of some of our code....
If you've just ported this, how come changing the code is not an option? The migration must have involved some changes?
The idea of using an SP would be to create procedure referencing whichever objects you require. the SP would bind to those objects (i.e. your sequence) and you could grant execute on the SP's package to a user that otherwise wouldn't have access to those objects.
Maybe this won't fit your scenario but it is a workaround for this kind of problem.