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. And all databases are not enabled for OpenEdge Authentication gateway(OEAG). For SQL permissions on an OEAG enabled database reference article:
SQL permissions on an OpenEdge authentication gateway (OEAG) enabled databaseIt 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.
Use SQL to grant different types of privileges to various database users through DCL. Usually, the privileges are restricted, allowing users the ability to retrieve, update, or delete the data needed to perform their assigned jobs.
The authentication is only enabled after users are created in the _user table. If there are no created users for an OpenEdge database, the authentication check is not performed when a user tries to connect to the database. This means no password is required (you can input any text value for the password) to connect to the OpenEdge database.
Progress Documentation -> OpenEdge SQL Quick Start Guide -> Configure SQL security and privileges -> OpenEdge SQL security -> SQL authorization check:
https://docs.progress.com/bundle/openedge-sql-quickstart/page/SQL-authorization-check.htmlIn 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:
