Kbase P131308: Cannot access 4GL sequences from SQL92 stored procedure owned by PUB schema
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/1/2010 |
|
Status: Verified
SYMPTOM(s):
Cannot access 4GL sequences from SQL92 stored procedure owned by PUB schema
SELECT PUB.<sequence name>.CURRVAL FROM <table name> fails from a stored procedure
SELECT PUB.<sequence name>.NEXTVAL FROM <table name> fails from a stored procedure
If the PROTRGRDBG environment variable is set to "Y", the file sqlJavaDebug.out contains the following errors:
ERR sql_ssm_t::Prepare st= -20121
genSQLException errStr= prepare: Error in DB->Prepare
Statements execute correctly from SQL Explorer
Table PUB._Sequence exists
PUB is OWNER of the stored procedure
FACT(s) (Environment):
Windows 32 Intel
Progress 9.1E
OpenEdge 10.x
CAUSE:
Stored procedures execute with the privileges of their owners, PUB in this case. SELECT privilege on a sequence is necessary in order to access the value of a sequence with CURRVAL or NEXTVAL, but PUB does not have SELECT privilege on sequences by default even though it is the owner of the sequences.
FIX:
Grant SELECT privilege on the sequence to PUB.