You need to grant DBA priveleges to the user that you created in order to change the schema from SQL92.

Hi Kevin. Please tell me where to grant DBA privileges. I am new at

this. As a note, I am using a Progress DB and connecting via JDBC driver

in Architect. Also in Data Administration's Edit Data Security dialog

box, all entries have a *.

The permissions on the 4GL tables have no effect when connecting via SQL92.

There is a SQL table called SYSDBAUTH which contains the system wide priveleges to the schema. All the 4GL tables are in the schema 'pub' so you need to grant DBA priveleges to the user you created.

Run the SQL explorer tool (sqlexp -db -S ) (on Windows you must use the DOS prompt) and connect to the DB using the user that created the DB. Then use the GRANT statement to grant DBA to the user you have created:

grant DBA to ;

and you should be good to go.

I get this error:

C:\OpenEdge\test\DB\QA>c:\progress\openedge\bin\sqlexp -db myDB -S 7004

OpenEdge Release 10.1C as of Sat Feb 9 17:20:03 EST 2008

Connecting user "Administrator" to URL "jdbc:datadirect:openedge://localhost:7004;databaseName=myDB"... (8920)

Error: [OpenEdge JDBC Driver][OpenEdge] Access denied(Authorisation failed). (8933)

See "C:\OpenEdge\test\DB\QA\sqlexp.log" and "C:\OpenEdge\test\DB\QA\SQLExplorer.exceptions" files for more information. (8950)

C:\OpenEdge\test\DB\QA>

If I delete the new user then I can get into sql explorer. Then I recreated the user and then granted him DBA access. But now in Architect I still get the privileges error.

This http://www.oehive.org/node/954 might help you set up the privileges.

I don't understand why in Architect using the new user of SYSTEM, I

still can't edit schema because the user seems to have access:

SQLExplorer>select * from sysprogress.sysdbauth;

GRANTEE DBA_ACC RES_ACC

I see column labels and no data.  Did you do the GRANT?  COMMIT?

I still can't edit schema because the user seems to have access:

Did you create a SYSPROGRESS user using Data Dictionary? Anyway you have to do that before proceeding with editing schema. SYSPROGRESS  is a special userid which has DBA access to SQL area of OpenEdge Database.

In my last post I replied via the email but it didn't save the results portion. Anyways here it is below.

SQLExplorer>select * from sysprogress.sysdbauth;
GRANTEE                  DBA_ACC RES_ACC
------------------------------   ---------------  ---------------
Administrator              y               y
SYSPROGRESS        y               y
SYSTEM                    y               y
SQLExplorer>

By looking at this it would seem that the SYSTEM user I created should have access to edit table data in Architect. Yet I still get the "You do not have privileges..." message.

Sergey: I don't know if the SYSPROGRESS user was created in the Data Dictionary. I don't even know it's password.

One thing I noticed in Architect under the Privileges tab for my app tables, Is all the privileges rows have a grantor of ADMINISTRATOR and the grantee has ADMINISTRATOR and PUBLIC.

I dont' see any entries in the _User table?

The SYSPROGRESS user is not in _User by default, but it and the userid that created the database exist with special privileges by definition.  Since I use _User in my databases, I always create _User entries for root (who created the DB), sysprogress, and one or more "application" users such as Actuate, or EA.

My last post was incorrect. I do see only my "system" user in the _User table. Still searching for a fix to my problem.

I think I just have to grant all table access to PUBLIC. But to do this I have to login to sqlexp as Administrator. But I don't know the password?

If it isn't in the _User table, it doesn't have a password yet.  If you are going to use _User at all, you need to define the DB owner id and sysprogress in _user.

I guess you haven't seen my latest posts. I do have my "system" user I created, it is in the _User table, but only it. But in my latest post I said that I think I have to grant table privileges to PUBLIC, right now it is only ADMINISTRATOR. But I don't know what ADMINs password is to get into sqlexp?

And, what I am telling you is that you need to go into the dictionary system security and create a user for sysprogress and a user for administrator (if that is who created the DB) and give them passwords.  Then you will be able to log into SQL as administrator.

Somehow, though, I'm not sure that is the problem.  I would do that and then try all three user ids in Architect to see if you get different results.

I created a sysprogress and administrator user. Verified they are now in the _user table. Then I tried using them in my architect sql connection, but still cannot update table schema by getting a message "You do not have privileges to launch the edit schema wizard as only DBAs may access this option...". I also logged into sqlexp as both and still cannot grant by getting a message "... permission denied...".

I noticed the following in the Architect help:

Working with application data

To view application schema using Progress DB Navigator, you must have READ access privileges. To edit application schema, you must have WRITE access privileges.

Note: For OpenEdge databases, users with DBA privileges can view and edit application schema.

Well I can read the schema but not edit to it.

Note that before I created any system user IDs, I was using the default Administrator user to connect in Architect with no password, and could edit schema with ease.

The problem was that I created a sysprogress userid with an integer and username of "sysprogress". I should have entered "sysprogress" also in the userid field. Apologies for the confusion.

Aha!