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

Basic Guide to Defining Progress SQL-92 Database Permissions & Security
Product: OpenEdge
Version: All Supported Versions
OS: All Supported Platforms
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
Access denied (Authorization failed) (7512)
Error: <command name | SQL Exception message>. (8933)
This Article provides a basic understanding of Progress database security for SQL-92 by way of example.

Create a database.  

There are several methods to create a database. A simple method is use of the PRODB command from the command prompt.

1.  Start PROENV
In order to set environment variables in order to access to Progress commands from the command line.
If unfamiliar with starting a PROENV session please refer to the following Article:
What is the purpose of the proenv utility?    

2.  Create a Progress database
Example use PRODB to create a copy of the sports2000 database.
If unfamiliar with prodb and its usage please refer to the following Article:
How to create a database with PRODB?    

The default DBA:  

The user who creates the database becomes the default DBA. The default DBA can be used to set additional database Administrator accounts.

There is another account which has superuser privileges which is inactive by default, sysprogress. The sysprogress account should be used only in cases where there is no other SQL-92 DBA. 
For example:
If the database was converted from a Progress Version 8.x database with defined users, create a sysprogress user to be able to login as a SQL-92 DBA.

3.  Start the Database Server.  

Start the database through PROSERVE or through the AdminServer. Either way, ensure to enable TCP/IP connections, i.e. make sure to use the -S startup parameter with a valid value. (The -S parameter indicates the TCP/IP port where the database server listens for client connections.)

proserve sports2000 -S 2500

4.  Connect to database via SQL Explorer:  

Please refer to the following Articles for further information on what SQL Explorer is:
Authentication Enabled vs. No Authentication Check:  

In a database where no users have been created within the database _user table no password is necessary to connect to the database.
Any text value may be supplied for the password and the connection to the database would be allowed because there is no authentication check at this time.

Authentication is only enabled when users are created in the _user table.  If there are no users in the database, there is no authentication check performed when connecting. However, if a user other than the DBA connects, this user has access only to public tables or tables where a DBA has granted privileges to the user.

NOTE: Privileges can be granted even when there are no users created in the database. If users are added to the _user table they must be granted privilegies from the SQL side by a DBA account in order to work with data from the SQL side. Failure to do so will result in queries returning error 7512.  

To enable Authentication, create at least one user in the database. Always grant DBA access to at least one user of the database.  In the event that at least one user was created but DBA access rights are not given, this user can create a sysprogress user via the 4GL Data Administration Tool to allow DBA access to the database.  Make sure that password is defined for sysprogress user or connection will fail.

Listing the Database Authorization Table:  
Listing the Database User Table:  
Listing the content of the Customer Table:  

Use the following command to list the content of the customer table:
select * from pub.customer

The record content of the pub.customer table should be listed.

Connecting to the Database as sysprogress when there are no users:  

Try to connect to the database using sqlexp and specify the following connection parameters:
Host     : localhost
Service  : 2500
Database : sports2000
User     : sysprogress
Password : 

Since there are no users created, a connection attempt using sysprogress should fail

Connect to the database as a regular user:  

Connect to the database using sqlexp and specify the following connection parameters:
Host     : localhost
Service  : 2500
Database : sports2000
User     : user1
Password : x

For this connection use user user1, password x.

Checking Database Security and Table Access:  

Perform these steps:

1) Using SQL Explorer, connect to the database and use any of the valid users to connect to the database  (eg. DBA1, user1, or user2).
2) Perform the following SQL statements to check the database security and table access:
select * from sysprogress.sysdbauth
select * from pub."_user"
select * from pub.customer
select * from pub.state
update pub.customer set creditlimit = 70000 where custnum = 10
In order to review the database security, the tables (sysprogress.sysdbauth) and (pub."_user") can be used.  
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.

Listing the content of the database tables using a regular user:

Perform the following SQL statement to access tables from the database:
select * from sysprogress.sysdbauth
The regular user should be able to access this table because sysprogress.sysdbauth is a public table.
GRANTEE                          DBA_ACC   RES_ACC
-------------------------------- -------   -------
administrator                          y         y
SYSPROGRESS                            y         y

The administrator user name corresponds to the user who created the database.
The SYSPROGRESS account is used internally by the SQL engine and is not initially active within a database.
The column DBA_ACC corresponds to the DBA access that a user can have  and RES_ACC corresponds to the resource access, that is, permission to create objects in the database.
If a DBA user is revoked from any of these permissions, the user name remains in the sysprogress.sysdbauth  table, however, the permission is displayed as blank.

Granting permissions on tables to regular users:

Revoking permissions on tables to regular users:
Enabling authentication or creating users in the database:  

In order to enable authentication, users should be created so the engine will know the passwords for these  users. To create users use the SQL statement CREATE USER.

Users can also be created from the 4GL Data Administration tool, however, in this case these users are  regular users without special permissions from 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';

If you are using the SQL Explorer, remember to commit these changes. An ODBC interface does not need  a commit statement because in ODBC, AutoCommit is enabled by default. Even though, it is standard to  create users to restrict the access to unauthorized users, for some applications, it may not be required  to enable authorization.

If you wish to add a SQL DBA with resource rights to the database, it cannot be a user created on the 4GL side when security was turned on. There is a uniqueness required for the SQL user.

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

References to Other Documentation:
Progress Article(s):
SQL: How to change a Progress/OpenEdge user's password?
8/21/2020 3:37 PM