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

Facts:

•All Supported Operating Systems
•Progress 9.x
•OpenEdge 10.x

Fixes:
This Solution provide a basic understanding of Progress database security for SQL-92.

First, create a database. There are several methods to create a database. A simple method is use of the prodb command from the command prompt. But before executing 'prodb', define the following variables to access to the Progress commands from the command line:

1. Start PROENV.
This starts a command prompt session that sets the Progress environment by default. If using the standard command prompt, then the following variables should be defined

On MS-Windows:
SET DLC=DLC PATH
SET PATH=%DLC%BIN;%PATH%
NOTE: The icon Proenv can be used to start a command prompt session that sets the Progress environment by default.

On UNIX:
SET Environment Variables
DLC=DLC PATH
PATH=$DLC/bin:$PATH
export DLC PATH


2. Create a database
prodb sports2000 sports2000


The default DBA:
The user who creates the database becomes the default DBA. This user can be used to set additional database Administrator accounts. The sysprogress account is disabled by default. 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 log in as a DBA in a SQL-92 database.


3. Start database server. To start the database through proserve or through the Progress Explorer. Either way, make sure to enable TCP/IP connections.
proserve sports2000 -S 2500
The -S parameter indicates the TCP/IP port where the database server listens for client connections.

4. Connect to database via SQL Explorer:
Start the SQL Explorer by either using the icon in the Start menu or via SQL Explorer (sqlexp) from the command line. Use the Connect on the File menu and specify the Host, Service or Port, Database name, User name, and Password.

For example, if the database is running on the same system as SQL:
Explorer :
Host : localhost
Service : 2500
Database : sports2000
User : name of the user that creates the Database
Password : no password is required if there are no users created.

If the database runs on a different machine than SQL Explorer, specify this name for the Host parameter.


Authorization Enabled vs. No Authorization Check:
In the previous step, you are able to connect to the database without specifying a password. Actually, any text could have been used and you still could access the database because there is no authorization check at this time.

Authorization is only enabled when you create users with a defined password. If there are no users in the database, there is no authorization check to connect the database. 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.

Notice that privileges can be granted even when there are no users created in the database.

To enable authorization, create at least one user in the database. Remember to 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 to sysprogress user or connection will fail.

Listing the Database Authorization Table:
Use the following command to list the Database Authorization Table:
select * from sysprogress.sysdbauth


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. 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.


Listing the Database User Table:
Use the following command to list the Database User Table:
select "_userid", "_password", "_user-name" from pub."_user"


_Userid _Password _User-Name
------- ----------- --------------
This time, the pub._user table should list no users.


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

The 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 (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.


Granting permissions on tables to regular users:
In the SQL-92 database, as well as in other SQL databases, regular users do not have access to database objects until the proper permissions are granted to them.
In order to grant privileges, connect to the database using a DBA account. In this case, use the default DBA that is the user who created the database.

Perform the following SQL statements to grant select access on the pub.customer to user1, and full access to the pub.state table to all users:

- grant select on pub.customer to user1;
- grant all on pub.customer to user2;
- grant select on pub.state to public;
- commit;

The revoke SQL statement can be used to revert permissions given by the grant statement.

Add a commit statement to make the database changes permanent. In sqlexp, the AutoCommit option is false by default and if or when the user disconnects, the changes done on the database are rolled back.

Notice that permissions can be granted to users even though the users have not been explicitly created.
Validation of user access (when no entries exist in the _user table) are based on operating system user accounts.
When a user requests a login and no _user table entries exist then the database server engine will query the operating system user accounts to see if one matches the user name and password being submitted for login.
If a user name and password match at the Operating System level then the user is logged into the database and any defined permissions (grant statements made for that user or to public) are allowed.

NOTE: The SQL-92 Guide and Reference book provides additional information on the GRANT and REVOKE statements.

Try the following SQL statements and connect as user1 first and then user2:
select * from sysprogress.sysdbauth

Users (user1) and (user2) should be able to access this table because it is public.

Both user1 and user2 should not be able to access this table because it is not public:

select "_userid", "_password", "_user-name" from pub."_user"

Both users should be able to access records in pub.customer:

select * from pub.customer

User1 and user2 should be able to access this table because it is public:

select * from pub.state

Only user2 should be able to update the pub.customer table:

update pub.customer set creditlimit = 70000 where custnum = 10

User1 should get an error message:


Enabling authorization or creating users in the database:

In order to enable authorization, 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';
- commit;

NOTE:

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 can not 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.