Feedback
Did this article resolve your question/issue?

   

Article

How to grant privileges to SQL-92 users from SQL explorer?

« Go Back

Information

 
TitleHow to grant privileges to SQL-92 users from SQL explorer?
URL Name19170
Article Number000120713
EnvironmentProduct: Progress
Version: 9.x
Product: Openedge
Version: 10.x, 11.x
OS: All supported platforms
Other: SQL-92
Question/Problem Description
How to grant privileges to SQL-92 users from SQL explorer?
Steps on how to grant privileges to SQL-92 users?
How to verify SQL-92 users privileges
Steps to Reproduce
Clarifying Information
SQL users who have not been granted necessary SQL privileges on tables will receive the error:
 
Access denied (Authorization failed)

when trying to query Progress Database with a user_ID via ODBC.
Error Message
Defect Number
Enhancement Number
Cause
Resolution
Use the GRANT command to give any kind of privileges to users (DBA, RESOURCE, SELECT, INSERT, etc..). 

Commit the changes made by the GRANT command to the database in one of the following ways:
  1. Use the "Auto commit transactions" option in the Progress version 9 GUI SQL Explorer "Connection Control" window (View menu->Options), OpenEdge Architect, Progress Developer Studio for OpenEdge, or similar tools.
  2. Execute the COMMIT statement after granting privileges to users. It is not necessary to execute the COMMIT immediately after the GRANT statement, but it must be executed before closing the database connection. Otherwise, the changes will apply only to the current connection.

Below is an example of how to use the GRANT statement:

GRANT DBA TO myuser;
GRANT SELECT ON customer TO myuser2;
COMMIT;


To make sure that the commands were properly executed, connect again to the database using SQL Explorer Tool and check the following:

- SYSDBAUTH view from SYSPROGRESS schema to see DBA and RESOURCE privileges:

SELECT * FROM sysprogress.sysdbauth;

- SYSTABAUTH view from SYSPROGRESS schema to see specific table privileges. For example:

SELECT * FROM sysprogress.systabauth WHERE grantee = 'myuser2';
Workaround
Notes
References To Written Documentation:

OpenEdge® Data Management: SQL Development : Data Control Language and Security : Working with database security
Last Modified Date12/14/2018 10:28 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.