Client-server database security (allowing remote database servers to accept connections from PASOE) - Forum - OpenEdge Development - Progress Community

Client-server database security (allowing remote database servers to accept connections from PASOE)

 Forum

Client-server database security (allowing remote database servers to accept connections from PASOE)

This question is answered

Can someone please point me to whitepapers about how Progress recommends that *remote* ABL clients be authenticated & authorized to access an OE database?  Most of the database connectivity I've seen has been from local, "shared-memory" applications that use the blank user and password. 

In the case of blank user and passwords, the security is typically implemented in the application layer (ie. in the appserver logic).  And no remote connections are allowable to the OE database itself. 

I like doing custom security in the application layer, but now that we are moving the application layer to another server (a remote PASOE server) there needs to be some database security too.  The PASOE database connections will need a way to be authenticated to the database.  That type of database authentication should allow PASOE applications to connect from certain production servers (possibly with a shared account.)  But the authentication should definitely *disallow* connections from unauthorized clients (eg. developer workstations should be prevented from connecting to a production OE database).

Moreover we'd like our local, "shared-memory" connections to continue to work as they do today with the blank user and password.  But we don't want this loose level of security in the case that a remote connection is made to the database.

I suspect there is a normal progression where an older OE database (that has blank user id's) needs to begin to serving *remote* ABL applications as well.  What are the steps that are needed to support that?  I was considering the use of I.P. filtering so that only an approved list of remote servers would be able to connect.  That would be somewhat analogous to the way that any local process is trusted if it connects with a blank user.

I should probably know all this by now given how long I've worked with OE databases.  But it seems like the OE RDBMS allows developers to easily defer security concerns.

I would be very grateful if someone could point me in the right direction.  Again, the scope of the problem is to find a way to allow certain PASOE servers to connect remotely, and disallow any other remote connections.  I originally thought I.P. filtering might be a good approach, but I'm not finding a lot of google hits for that (at least not in the context of the OE RDBMS).  So I'm guessing that is a non-standard approach and that there might be some better options.

Any help would be appreciated.

Thanks, David

PS. Here are two distinct authentication models that I've started investigating but haven't gotten very far with either of them yet.  I'm looking for some initial direction from the community, especially from someone that has already visited this topic in the context of PASOE.

Legacy ("_user") authentication and authorization:

https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dpabl%2Fsetting-table-and-field-permissions.html%23

OE Authentication Gateway

https://www.pugchallenge.eu/docs/default-source/presentations-2017/progress---barbarians-at-the-gate-oeauthgateway.pdf?sfvrsn=917659f_2

Verified Answer
  • Hello David,

    Your choosing the OE Authentication Gateway as your ultimate destination is a good decision.   It will give you a single point of user authentication for ABL database clients, PASOE, and OEM.  The resulting Client-Principal can then be used across all of your product’s ABL components to control access to your DB and application.  I think another important part of the OE Auth Gateway is that it can give your DBA Role based control of physical DB connections from all forms of ABL ( & SQL ) clients.   The DBA can be in control of who can connect and from where that connection can originate from – defeating IP address spoofing and other types of DB connection spoofing.

    If you start by requiring your existing DB clients to use -U/-P for the initial connection, the OE Authn Gateway will be easier to integrate later.  After the connection you can use Client-Principal objects to change that connection ID as needed.   The OE Auth Gateway supports stronger user logins like LDAP & AD, but migrating your existing built-in security should be viable also and allow changes to be staged in smaller increments.

    I really don't have a good short-term idea other than the traditional port connection solutions.

    Hope that information helps.

All Replies
  • It looks like the "OE Authentication Gateway" is probably the best approach for authenticating remote ABL database clients who need to connect to an OE database from PASOE.  (Or I should say that if this is not the best approach then it is at least the latest approach.)  

    I haven't heard too many discussions in the community about this "oe auth gateway", but maybe it is because it isn't widely used yet. Here is the guide for getting started.

    https://documentation.progress.com/output/OpenEdge117/pdfs/oeag/oeag.pdf

    The OpenEdge Database includes support for enhanced Client connection security beginning in the OpenEdge
    
    11.6.2 release through a network of interdependent OpenEdge product security features. This enhanced
    
    database connection security puts the Database Administration (DBA) in control of which OpenEdge clients
    
    may establish and use those connections in order to access their server’s data

    I would still love to hear if someone can recommend alternative approaches.  All we really need is security that would allow certain remote PASOE servers to connect, while restricting other remote ABL connections (that are not production PASOE servers).  I was hoping there might be something easier than the "oe auth gateway" that we could do in the short-term.  A short-term solution would allow us to move ahead with a migration from classic appserver to PASOE, and then circle back later to implement the "oe auth gateway" at a later time.   

    It would be nice to hear any opinions about IP filtering.  I'm wondering it that is a reasonable approach, or if it is too janky for OpenEdge database authorization.

  • Have you looked at using a client-principal object and defining an authentication system on the OE db with a callback procedure? I'm not sure of the exact mechanics for your use-case, but the callback procedure is ABL thus allowing for some flexibility.

    Jeff Ledbetter
    Product Architect | Roundtable Software

  • Thanks for bringing this up. I am looking forward to reading more about this topic.
     
     
  • Jeff, I had not looked at the "client-principal" stuff yet.  It sounds like it involves some custom programming.  IE. it looks like it allows a software developer to introduce their own custom authentication scheme into the database.  

    This "client-principal" approach appears to be the third database authentication option that I've heard of now.  It seems like a relatively new alternative to "_user" authentication table (but older than "oe auth gateway").  Here are some KB's I've found; the earlier ones seem to be from 2014 or so: 000037324, 000051566, 000053131, 000053242,

    Do you know if there are any articles that describes the various approaches that Progress has proposed for database security over the years?  It would be nice to get a list, along with the pros/cons for each.   I'm less interested in SQL92 connectivity than ABL connectivity (particularly remote "client/server" connectivity in ABL).

    Thanks, David

    PS.  I'm fairly convinced that the "oe auth gateway" is the right direction for us to take in the long run, but was looking for something simpler in the short term.  Any help would be very much appreciated.

  • The CLIENT-PRINCIPAL is the ABL’s security token. It can be created in a variety of ways, including automagically when you use -U/-P  or SETUSERID. You can see this via the GET-DB-CLIENT() function or the SECURITY-POLICY’s GET-CLIENT() method.
     
    The C-P is independent of the authentication method used: you can use _User, an ABL table, LDAP, OS login security, etc  for the authentication. Different parts of the product allow the use of different authentication schemes.
     
    Also, the C-P has been available since 10.1A, though I’d say use at least 11.1 and obviously, the later, the more authentication schemes will  be supported.
     
     
     
     
  • Hello David,

    Your choosing the OE Authentication Gateway as your ultimate destination is a good decision.   It will give you a single point of user authentication for ABL database clients, PASOE, and OEM.  The resulting Client-Principal can then be used across all of your product’s ABL components to control access to your DB and application.  I think another important part of the OE Auth Gateway is that it can give your DBA Role based control of physical DB connections from all forms of ABL ( & SQL ) clients.   The DBA can be in control of who can connect and from where that connection can originate from – defeating IP address spoofing and other types of DB connection spoofing.

    If you start by requiring your existing DB clients to use -U/-P for the initial connection, the OE Authn Gateway will be easier to integrate later.  After the connection you can use Client-Principal objects to change that connection ID as needed.   The OE Auth Gateway supports stronger user logins like LDAP & AD, but migrating your existing built-in security should be viable also and allow changes to be staged in smaller increments.

    I really don't have a good short-term idea other than the traditional port connection solutions.

    Hope that information helps.