querying tables

[cl0udman]

Hi,
I have a problem and am searching for advice/tips. This is our first attempt at accessing the data in a Progress database, and we are having difficulty. The database is in production at a client site. The vendor of the app that uses the Progress db is unwilling to provide assistance (to us or anyone else), although the data belongs to the client, not the vendor.

I have the credentials, names, ports, etc needed to run Progress tools such as SQL Explorer, Data Dictionary, etc. Using SQL Explorer, I can run queries against system tables such as:
select * from sysprogress.systables

Using SQL Explorer, if I try to query a table that the application uses, such as:
select AccountID from Account
I get an "access denied" error. I apologize, it was 2 days ago that I was onsite, and I did not capture the exact error number and verbage. I believe it included "authorization failed".

I know that the table "Account" exists and contains a column named "AccountID"; I discovered this in the Data Dictionary.

I have tried altering the query to include an owner, such as:
select AccountID from pub.Account
select AccountID from PUB.Account
and other guesses.

I have a few ideas about what the problem may be, and am hoping for some guidance.

1) the table that the client application uses are restricted and only accessible through a higher authority user than the credentials I have. My user is *not* listed if I execute:
select * from sysprogress.sysdbauth
(note that my user *can* execute the above query)

2) the tables are only accessible through views/procedures, not direct queries

3) I am missing a schema that will give visibility to the tables



My goals are, first, to query the application's tables using SQL Explorer, and then, to query from a Windows application using ADO.

I apologize if this is a common post, I've searched many newsgroups, and threads on this site, but have not come up with a solution.

Thanks very much in advance.

 

[plus_marca]

Hi.

by default Progress denies access to the data when using SQL, to access the data you have to grant access privileges to the users.
To do this you have to connect to the db using the id of the user who
have DBA privileges.

To check DBA / Resource privileges in use, execute the following SQL
statement in the SQL explorer:
SELECT * FROM sysprogress.sysdbauth;

To check Table/View/Synonyms/SQL-92 Stored Procedure privileges in
use, execute the following SQL statement in the SQL explorer:
SELECT * FROM sysprogress.systabauth;

To grant access for the users to view data, you have to do something like this.

1) connect using the id of the user who have DBA privileges
2) grant select privileges to the table(s) needed

Example.

GRANT SELECT ON PUB.Account TO User1.


Hope this helps.

 

[murLee]

Shows only system tables in SQL Server2K

Hi there,
I have created a DSN using Merant32 ODBC Driver, using this DSN, i am writing a DTS in sql server to import the data from progress to SQL Server2K. But this connection shows only the system tables.
As i have select the tables and schedule the import process.

The login name & pwd used for connecting to progress have admin rights.

Please guide me to solve this problem.

Thanks in advance.

Regards,
MurLee

 

[Tarby777]

Who are you connecting as when you execute the query? It's a while since I've done it, but I seem to remember that doing it as sysprogress is the easiest way. You can grant the necessary permissions to other users, but if memory serves, I typically connected as sysprogress and everything seemed to go well enough...