Big problems with OE and ODBC/SQL/ and local database users

[dlangschied]

I am having a great deal of difficulty with OE! Our upgrade to OE included the need to create SQL users for ODBC connectivity. The initial impact of this was that promon did not show the users name, so disconnecting a user session on the OS (linux) required more work. Then, all of sudden, on the linux side, the user connection showed up and no longer appeared when logging into PE. It does not impact the ODBC connection (thank God). When ever we bring down the database (every week in test), the ODBC connection stops working and once we restart the server it returns.

My latest issue is with batch jobs on the server. We have an app (using different db then above) that has batch agents that connect to the database and process maintenance tasks at scheduled times. These agent connections sometimes hang. We try to discourage root login to disconnect users in promon (could inadvertantly shutdown db, BAD). So, we login as the batch user to disconnect. Only, the user name is not there and won't allow for a disconnect. Tried to create the user in the database with negative results. In fact, I get a "no resources error". I am at a loss!

I need to:
1. Figure out why my user names suddenly appeared in my ERP database, with the ODBC agents intact (and how to return to the previous disposition).
2. Figure out why a restart of the database causes the ODBC connections to fail, but return on restart (second time).
3. Figure out how to get the user name to appear in promon with a local connection (this would also eliminate 1, if it is possible to do it without creating a database user).

I know this is a long post, but I am finding the OE ODBC connection for SQL to be a mighty troublesome development.

Thanks!

 

[RealHeavyDude]

Just asking:

Do you use a secondary login broker for the SQL connections?
Do you have the watchdog running?


RealHeavyDude.

 

[dlangschied]

We are using Native Progress ODBC. Is this an issue?
We have watchdog running. Is this an issue?

 

[RealHeavyDude]

Native Progress ODBC has nothing to do with a secondary login broker. In a productive environment ( and IMHO in a test environment too ) 4GL clients and SQL clients should not connect to same login broker. Each of these types of clients should use it's own dedicated login broker. You can use "secondary login broker" as a search string in the Progress knowledge base and it will give you results which describe in detail why and how to set up the secondary login broker.

The watchdog supports the primary login broker ( that's what everyone is referring too when they mean that they start the database [server] ) in freeing up resources from disappeared shared memory connections and hung servers and secondary login brokers.

To address your questions: Neither is the native Progress ODBC driver an issue nor is the watchdog.


Heavy Regards, RealHeavyDude.

 

[dlangschied]

Okay...
I am now starting a separate broker for my ODBC connections. I login using the 4GL server and check promon; still no user id shows up. This is really the crux of my original post, but I do agree that I needed to take the steps to have a separate SQL port for those user types.

Any ideas why I am not seeing my user id in promon?

 

[TomBascom]

When you have no users in the _user table the userid is the OS userid.

As soon as you add an _user that goes away. If there are _users then to have a userid the session must either be started with -U or it must go through a login process that calls the setuserid function. If "allow blank users" is permitted (in the data dictionary) then when a background session starts without -U or the user cancels the login process you end up with no name showing.

 

[dlangschied]

So sorry!
It has been some time since I looked at the issue and forgot the main thrust of the user identity. The actual problem is that we added a user for SQL, which caused all users (OS) to be blank. Then, for some reason, the users started showing up again, even though the SQL user is still in the database. That is the issue at this point. If this situation can exist, then I prefer to have the user id be listed in promon. Since I do not know how this is happening, I cannot apply it to the other databases that I have.

 

[TomBascom]

How do you know that the SQL user is still in the database?

What query do you run and what is its output?

What does:

for each _user no-lock:
  display _user.
end.

Result in?

What are your connection parameters? (For both 4gl and SQL users...)

 

[dlangschied]

[FONT=r_ansi][FONT=r_ansi]I did not check the _user records, but I find that the SQL user is gone! Yet, I am still making an ODBC connection to the database with a user id! How can this be?
[/FONT][/FONT]

 

[vinod_home]

You can use any userid to make the odbc connection, it doesnt validate it during the connect, but it checks when you want to retrieve data from the tables. This is because the SQL user is still in the sql-access tables (like systabauth). The password is not used (found that out a while ago) by the sqlexp during connect if you dont use the _user security.

 

[dlangschied]

Okay...
How can I "break" this for all of my other databases? I only need the ODBC connection to read data (which must be different from retrieving data since I am not having a bunch of angry shipping people yelling at me that they can't ship). I really hate the fact that I do not see the user id in promon anyway. I cannot even kill a session logged in as the user who created it. I am having a very big issue with EAM (a QAD product) as it has a batch agent that runs scheduled jobs. The only way I can kill the batch user if it hangs (too frequently too believe), is to do this as root.

So, for me, this "break" is a BREAK.

 

[vinod_home]

you can start seeing the user-id in promon, just by cleaning up all the _user records. Check what user-id's you have in _user, you may only have 'sysprogress' and the odbc user-id that you created. You can delete both of them and that will allow promon to show the user-id's like you are used to.

I did not understand what you mean by " how can I 'break' this for all my other databases ". Do you mean disconnect the odbc connection, just use promon and disconnect the user.

 

[dlangschied]

I was being facetious. That fact is that prior to going to Open Edge, we were not using ODBC. The intorduction of ODBC caused us to have to create a user id in the database. Once this happened, we lost the ability to let the OS provide the userid. The blank user id created challenges for us when trying to identify locked records, by user. And, where necessary, disconnecting them. I have a process that I have created to pair up the PID in the VST with the user who created it on the OS, but it is real messay on Windows where some of our user sessions begin. In addition, our batch user for EAM (QAD product) could no longer kill its own sessions, which was part of our internal process to fix hangs inherent to EAM's Batch Job Scheduler.

So, to make a long story short, I have one database in which the user is no longer blank and yet ODBC still works. I have 8 other databases that have blank user ids that i sure wish didn't. If I knew the reason why the one database is the way it is, I would be happy to get the others in the same boat.

 

[vinod_home]

Its because you dont have any users within that one database in the _user table. If you check the other 8 databases, you will find that you have a user-id in the _user table (QAD uses usr_mstr table to maintain users). If you remove the users in each of the 8 databases from _user table, your scripts should start to work fine.

 

[RealHeavyDude]

Just a note from my side:

Out-of-the-box only the OS account under which the database was created (which the database considers it's owner ) has access via ODBC/JDBC to the database. The philosophy of the SQL world is completely opposite to the Progress world: In SQL if it is not granted you can't do ( access ) it, whereas in the Progress world it's if you don't revoke it you can do ( access ) it. There is only one other user account that automatically has DBA privileges as soon as it exists in the _User table ( needs to be created manually ): SYSPROGRESS.

So if you access a Progress database via the SQL engine the first time and you don't know the credentials of the OS account under which the database was created, your only option is to create the SYSPROGRESS user in the _User table manually. As soon as you are able to access the database you may then create SQL users and grant them or users that are defined in the _User SQL privileges ( including DBA ).

In the SQL world you don't have any choice other than having user accounts in the database against which a client is authenticated. The only exception I am aware of is the PKI solution from Oracle where you can use SSL client certificates to authenticate against a client against the database - but I am not aware of the gory details. In the OpenEdge world it is completely up to the application how security is handled. Basically you have three options ( from the database's point of view): No authentication at all ( blank _User table ) and the Progress database will accept the OS user account as identity, authenticating against the _User table and from OE 10.1A onwards the client principal object authenticating against a trusted domain.

I've seen so many Progress applications taking care about security ( IMHO ) in a very sloppy way, either do they have no authentication at all or they use few non-personal accounts to authenticate against the database. Either way, identifying users authenticated against the database becomes nearly impossible - which is not only a bad thing when it comes to identifying the user, but it will become a real nightmare when you need to have an audit trail that conforms to external auditor's standards. Not to speak about the ( next ) big requirement you may hit: LAAC - location aware access control ...

Not that any of my thoughts may help you in your current situation since you are running an application over which development you don't have any control. But if more customer would stress security more Progress applications would be compliant with "common" security standards.


Heavy Regards, RealHeavyDude.