In this topic, we will add the connection role access control to the database so that only named and approved users can access the database.

Complete the following steps to implement authorization roles on the database:

  • Add system users to the Authentication Gateway machine
  • Verify that test user accounts can use the operating system login to access the database
  • Enable connection roles for authorization on the database
  • Add roles for authorization on the database
  • Test roles for authorization on the database

Add system users to the Authentication Gateway machine

There are different rules for adding users to different operating systems. Consult an administrator if you do not have the proper permissions to add users. Assuming you are running on a test system as an Administrator account, you should be able add users according to a standard process that is defined by your specific operating system.
  1. Add a few users to the operating system of your Authentication Gateway server machine, for example:
    Username: testuser1
Password: testuser1

Username: testuser2
Password: testuser2

Username: testuser3
Password: testuser3

Verify that test user accounts can use the operating system login to access the database

There should be a few test OS accounts that you defined on the Authentication Gateway server machine, in addition to the standard Administrator account.

Verify each user can login to the database using the local OS domain:
  1. (Optional step. The database should already be running and using the Authentication Gateway. Only required if you are unsure about the state of your database.) On the client database machine, ensure your database is pointed at the Authentication Gateway, and that the Gateway is enabled:
    proenv>cd %WRKDIR%\db

proenv>proshut sports2020 -by
OpenEdge Release 12.2 as of Tue Mar 17 19:02:26 EDT 2020
Shutdown is executing. (1613)
Shutdown complete. (1614)

proenv>proutil sports2020 -C disableauthgateway
OpenEdge Release 12.2 as of Tue Mar 17 19:02:26 EDT 2020
fmaFeatureDisable: The feature Authentication Gateway has been disabled. (11175)
Authentication Gateway has been disabled for database sports2020. (12490)

proenv>stsurlutil update -url https://yourgatewayhostname:8443 -ssl -nohostverify -db sports2020 -1 -U myAdmin@mySecurityDomain -P myAdmin
[2020-07-30T20:32:30.696-04:00] DBSTSURL INFO OPERATION: UPDATE
[2020-07-30T20:32:30.696-04:00] DBSTSURL INFO SESSION:PARAM: URL:https://yourgatewayhostname:8443,SSL-OPTIONS:-nohostverify
[2020-07-30T20:32:30.697-04:00] DBSTSURL INFO URL: https://yourgatewayhostname:8443
[2020-07-30T20:32:30.697-04:00] DBSTSURL INFO TEST-URL? yes
[2020-07-30T20:32:30.697-04:00] DBSTSURL INFO SSL-OPTIONS: -nohostverify
[2020-07-30T20:32:30.698-04:00] DBSTSURL INFO SERVERNAME:
[2020-07-30T20:32:30.793-04:00] DBSTSURL INFO STS Url created for "sports2020"
[2020-07-30T20:32:30.794-04:00] DBSTSURL INFO OPERATION COMPLETE

proenv>proutil sports2020 -C enableauthgateway
OpenEdge Release 12.2 as of Tue Mar 17 19:02:26 EDT 2020
Warning: It is recommended that connection role security enforcement be configured when using the OpenEdge Authentication Gateway. (18519)
Authentication Gateway has been enabled for database sports2020. (12479)

proenv>proserve sports2020 -nohostverify
OpenEdge Release 12.2 as of Tue Mar 17 19:02:26 EDT 2020
20:33:06 BROKER     This broker will terminate when session ends. (5405)
20:33:06 BROKER     The startup of this database requires 40Mb of shared memory.  Maximum segment size is 1024Mb.
20:33:06 BROKER  0: Multi-user session begin. (333)
20:33:06 BROKER  0: Before Image Log Initialization at block 10  offset 1083. (15321)
20:33:06 BROKER  0: Login by Administrator on CON:. (452)
    Note: Start the database using the -nohostverify option, because we have not yet implemented valid digital certificates on the Authentication Gateway.
  2. From the client database machine, test the database connections of the defined users on your Authentication Gateway server's operating system, for example:
    proenv>mpro sports2020 -U testuser1@local -P testuser1
proenv>mpro sports2020 -U testuser2@local -P testuser2
proenv>mpro sports2020 -U testuser3@local -P testuser3
proenv>mpro sports2020 -U adminOScredentials@local -P adminOSpassword

All valid accounts on the machine have access to the database.

Enable connection roles on the database

Enable connection roles to grant a limited list of users access to the database.

  1. On the database machine in the /db directory, use the stsconnroleutil utility with the enable option, along with the credentials of the Administrator account of your Authentication Gateway server: 
    proenv>stsconnroleutil enable -db sports2020 -U adminOScredentials@local -P adminOSpassword
[2020-07-31T10:48:17.233-04:00] DBCONNROLE INFO OPERATION: ENABLE
[2020-07-31T10:48:17.233-04:00] DBCONNROLE INFO SESSION:PARAM: "
[2020-07-31T10:48:17.233-04:00] DBCONNROLE INFO Role name: _db.connection
[2020-07-31T10:48:17.234-04:00] DBCONNROLE INFO Grantee:
[2020-07-31T10:48:17.280-04:00] DBCONNROLE INFO Connection authorization now enabled for "sports2020"
[2020-07-31T10:48:17.281-04:00] DBCONNROLE INFO OPERATION COMPLETE
  2. Verify that the connection roles are enabled using the status option:
    proenv>stsconnroleutil status -db sports2020 -U adminOScredentials@local -P adminOSpassword
[2020-07-31T10:49:32.899-04:00] DBCONNROLE INFO OPERATION: STATUS
[2020-07-31T10:49:32.899-04:00] DBCONNROLE INFO SESSION:PARAM: "
[2020-07-31T10:49:32.900-04:00] DBCONNROLE INFO Status file: C:\OpenEdge\WRK/dbconnection_status.txt
[2020-07-31T10:49:32.911-04:00] DBCONNROLE INFO OPERATION COMPLETE

    This command generates a dbconnection_status.txt file in the OpenEdge %WRKDIR%.

  3. Open the C:\OpenEdge\WRK\dbconnection_status.txt to see that the status was enabled:
    #DbName,Enabled,RoleName
"sports2020",yes,"_db.connection"
  4. As the user who enabled the use of connection roles, you can grant roles to have access to the database using the grantuser command, for example:
    proenv>stsconnroleutil grantuser -grantee testuser1@local -can yes -db sports2020 -U adminOScredentials@local -P adminOSpassword

[2020-07-31T10:53:35.100-04:00] DBCONNROLE INFO OPERATION: GRANT
[2020-07-31T10:53:35.100-04:00] DBCONNROLE INFO SESSION:PARAM: GRANTEE:testuser1@local
[2020-07-31T10:53:35.101-04:00] DBCONNROLE INFO Grantee: testuser1@local
[2020-07-31T10:53:35.101-04:00] DBCONNROLE INFO Can-grant? no
[2020-07-31T10:53:35.129-04:00] DBCONNROLE INFO Connection authorization now granted for user "testuser1@local" on db "sports2020"
[2020-07-31T10:53:35.129-04:00] DBCONNROLE INFO OPERATION COMPLETE
    Note: The -can option is for the priviledge to grant other users, not the connection role itself. Use of the -can switch should be limited to database administrators.
  5. You can verify the list of grantees with the list option:
    proenv>stsconnroleutil list -db sports2020 -U adminOScredentials@local -P adminOSpassword
[2020-07-31T10:54:47.700-04:00] DBCONNROLE INFO OPERATION: LIST
[2020-07-31T10:54:47.701-04:00] DBCONNROLE INFO SESSION:PARAM: "
[2020-07-31T10:54:47.701-04:00] DBCONNROLE INFO List file: C:\OpenEdge\WRK122/dbconnection_list.txt
[2020-07-31T10:54:47.729-04:00] DBCONNROLE INFO OPERATION COMPLETE

    This command produces a dbconnection_list.txt file in the OpenEdge %WRKDIR%.

  6. Open the C:\OpenEdge\WRK\dbconnection_list.txt to see the authorized roles:
    #DbName,Grantee,CanGrant,Grantor,RoleName
"sports2020","testuser1@local",no,"administrator@local","_db.connection"
"sports2020","administrator@local",yes,"administrator@local","_db.connection"
  7. Try logging into the database with the credentials of the user you granted authorization to:
    proenv>mpro sports2020 -U testuser1@local -P testuser1
  8. Try logging into the database with another user, who you haven't yet authorized to use the database:
    proenv>mpro sports2020 -U testuser2@local -P testuser2

    This produces a "password and user ID do not match" error. You could grant this user authorization using the stsconnroleutil grantuser command that was previously described in this topic.

Summary

In this topic, you enabled the use of connection roles to add a layer of authorization on top of the authentication of users. The stsconnroleutil has options to enable the functionality, grantuser to name users, and status to check the status of the enabled roles. Once you have established a valid list of connections roles you can then write custom code to limit access once authenticated and authorized for access to the database.