Feedback
Did this article resolve your question/issue?

   

Article

Basic Guide to Defining Progress SQL-92 Database Permissions & Security

Information

 
TitleBasic Guide to Defining Progress SQL-92 Database Permissions & Security
URL Name20143
Article Number000120378
EnvironmentProduct: Progress
Product: OpenEdge
Version: 9.x, 10.x, 11.x, 12.x
OS: All supported platforms
Other: SQL92
Question/Problem Description
Basic Guide to Defining Progress SQL-92 Database Permissions & Security
How SQL-92 Security works
How to define / grant SQL92 Permissions
How to troubleshoot error 7512 and 8933
SQL-92 privileges
SQL permissions
 
Steps to Reproduce
Clarifying Information
Error MessageAccess denied (Authorization failed) (7512)
Error: <command name | SQL Exception message>. (8933)
Defect Number
Enhancement Number
Cause
Resolution
This Article provides a basic understanding of Progress database security for SQL-92 by way of example.

This article uses the sports2000 database for all examples.
It is advisable to perform the examples within this article from a PROENV shell.
For instructions on creating a copy of the sports2000 database refer to this article:

To perform many of the following steps it is advisable to use a DBA account.
For information about default DBA accounts see this article:
The SQL92 side of the database can only be accessed using a remote connection to the database (via ODBC or JDBC) so the database must be started to listen on a TCP port: s.)

Example:

proserve sports2000 -S 2500


To issue commands to the SQL92 side of the database an ODBC or JDBC client which can define free-form queries is necessary.  A barebones Java client is installed within Progress / OpenEdge named SQL Explorer.


To Connect to database via SQL Explorer please refer to the following articles:
When is a user and password needed when logging into SQL92? To List what user accounts have DBA privileges refer to the SQL statements in the following article:
To List all users defined in the _user table:  
If no users exist in the _user table then it is not necessary to supply any user or password.
Any connection will be able to connect to the database, however only the creator of the database will be able to read data within the database

In order to review the database security, the tables (sysprogress.sysdbauth) and (pub."_user") can be used.  
Example:
select * from sysprogress.sysdbauth;
Other tables that list security information are sysprogress.systabauth and sysprogress.syscolauth.

In order to grant permissions and create users, use the SQL CREATE USER and GRANT statements.
Review the  REVOKE and DROP USER statements to know how to revert these operations.
Security can be increased by using the Security Administrators and Disallow Blank UserID Access options in  the 4GL Data Administration tool.
Refer to the following article for more information on disallowing blank users:

Granting permissions on tables to regular users:

Revoking permissions on tables to regular users:
Creating users in the database:  
Users can be created from the 4GL Data Administration tool, however, in this case these users are  regular users without special permissions within SQL-92.

When users are created in the database, the default DBA (the user who created the database) becomes  disabled. It is important to grant DBA access to at least one user so you will have a valid DBA account.  For example, the default DBA can be created as a user to have at least a valid DBA account.
create user 'dba1','password';
grant dba to 'dba1';
create user 'user1','x';
create user 'user2','x';
commit;

NOTE: When using the SQL Explorer tool, if AutoCommit is not enabled remember to commit these or any other changes.
Many 3rd party ODBC interface do not need  a commit statement because in ODBC, AutoCommit is enabled by default.

Permissions granted or revoked from the SQL side only apply to users that access the database using SQL.

A common problem encountered either attempting to connect to a database using SQL92 or accessing tables:
For more information on usage of SQL92 refer to our online documentation:
Workaround
Notes
Last Modified Date5/30/2021 7:14 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.