CURRVAL/NEXTVAL - Permission denied

sql92

New Member
Hello,

My database includes a table named Customer, which has a sequence that is also named Customer. I'm trying to execute:
SELECT PUB.Customer.CURRVAL FROM PUB.Customer

but getting:
[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]permission denied (7677)

PUB is the database owner. Table updates work fine, without any permission issue.

What am I doing wrong?
Thanks!
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
What privileges does your user account have on the customer table?
 

sql92

New Member
PUB is the database owner and can read and update the Customer table, but PUB.Customer.CURRVAL and PUB.Customer.NEXTVAL fail with "Permission denied".
I'm new to OpenEdge. Do I need a specific privilege to work with sequences?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
"PUB" is the name of the database schema that contains any database objects that are created via ABL aka 4GL, e.g. objects created with the Data Dictionary or Data Administration tools. A Progress database may contain more than one schema, but only one may contain tables visible to ABL users and it is called PUB. Are you saying that you have also created a user called PUB?

What SQL client are you using to connect to the database? How are you providing user credentials in this client? Did you do any security setup (e.g. creating users) in this database prior to connecting?
 

sql92

New Member
What SQL client are you using to connect to the database? How are you providing user credentials in this client?
I use MS SQL with linked server via ODBC that uses username/password authentication. This is the MS SQL statement:

EXEC('SELECT PUB.Customer.CURRVAL FROM PUB.Customer') AT OPENEDGE

Did you do any security setup (e.g. creating users) in this database prior to connecting?
No.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Is there a Progress database administrator you can talk to? I suspect "PUB" is not the username you should be using.

This KB article may help you as a starting point:
Basic Guide to Defining Progress SQL-92 Database Permissions and Security
http://knowledgebase.progress.com/articles/Article/20143/p
 

sql92

New Member
I suspect "PUB" is not the username you should be using.

PUB user CAN update records in the Customer table. Is there a specific privilege that needs to be granted to use sequences?
 

sql92

New Member
BTW, I ran
select * from PUB."_sequence"

and _Seq-Owner of "Customer" sequence is PUB.
 

sql92

New Member
Are you saying that you have also created a user called PUB?
Yes
 

sql92

New Member
Has anyone used CURRVAL/NEXTVAL via ODBC? I wonder if it works at all...
 

sql92

New Member
I've tried to execute the same statement from a .NET program connecting via ODBC, and got the same result:

Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]permission denied (7677)
 

aaimaai

New Member
Syntax for assigning sequence privileges
Use the following syntax to assign sequence privileges:
GRANT [SELECT | UPDATE]
ON SEQUENCE schema.sequence
TO user_name[,user_name]...


See: OpenEdge 11.6 Documentation
 
Top