Username not displayed in promon

Posted by Betty Hardin on 17-Oct-2016 10:24

Progress v10.2B / Syteline v6.03 

We had an issue a week ago where 'hidden tables' were not loaded into the database.   I thought I had fixed that issue (by fixing the schema) but apparently not everything has been fixed.  We are not seeing username in promon - see the attached screenshot.   Any idea how to fix that?

All Replies

Posted by Adriano Correa on 18-Oct-2016 11:13

Does option 8 show the name?

Posted by George Potemkin on 18-Oct-2016 12:16

Will the code below return YES?


Posted by Betty Hardin on 19-Oct-2016 22:42

Adriano - No - Option 8 does not show the remote user name.

George - YES - it does.

I believe that I've found the answer here:

"When NO database security is set, then Progress uses the O/S userid. As soon as database security is initialized, the mode is changed and we use the entries in the _user table. So, if a blank userid is allowed to be used to access the database, a blank userid is shown."

Have verified that to be true by removing 'sysprogress' from _user.  

Sysprogess was added to _user to enable ODBC connections after the failed dump / load.  

Using a copy of the database I am working through this:

I can see the username who ran the dump / load is now in sysdbauth - with dba and resource permissions.  I also see sysprogess there.  I understand that sysprogress is the default dba and that the dump / load effectively disabled that making the new user the default dba.   Using that 'new' dba I can see data in tables via ODBC.

The thing that confuses me is that the ODBC is setup to connect as user sc with password sc... and 'sc' doesn't exist anywhere that I can see.   The people who setup that ODBC connection don't remember why they used 'sc'.  I believe that may have come from old document on a v8 installation; this is Syteline v6.03 on Progress v10.2B - migrated recently from Progress v8.3E.

I have attempted to add 'sc' and set permissions for that user using these commands:

create user 'sc','sc'

grant dba to 'sc'

grant resource to 'sc'

After doing that I see that 'sc' has been added to sysdbauth - but the dba permission has not been granted and the user who was created by the dump/load process is now effectively disabled - so I end up with NO dba user - and have had to restore the database again to get back to where I was.

Am I on the right path?   Is there something different that I need to do?

Posted by Betty Hardin on 19-Oct-2016 22:45

I should mention - the 'sc' user previously 'worked' - but not really I don't think because when I try to login as 'sc' to SQL-92, I get a "connect successful' message (or something like that) but user 'sc' cannot access any data.

(I am trying to make it work with 'sc' so that users do not have to change their odbc connection)

Posted by ChUIMonster on 20-Oct-2016 08:15

My guess is that the previous database was created by a UNIX userid named "sc".

Posted by Betty Hardin on 20-Oct-2016 19:18

As it turns out - the documentation they provided to me is wrong.   "SC" is really not a user.   It does not work and has never 'worked'.   So - there's that bit of confusion resolved.  

The solution to this problem is to remove the 'sysprogress' user from the _user table and use something else for the ODBC.   Which leads to the next problem (that I also found out about today) - and it is an issue with Syteline's configuration product - which apparently has 'sysprogess' hard coded in all of the configuration models.  

Back to infor I go ..  

Thank you all for your help.

Posted by Adriano Correa on 21-Oct-2016 08:04

So, in OE10, or you have no _user created, or you must authenticate the user to show the id in promon/proshut.

I just tested this issue with OE 11.6. There is an option to create a user only for SQL connection. With this option checked, promon/proshut shows the OS user like there was no _user created.

Posted by Betty Hardin on 22-Oct-2016 14:01

Hmmm... maybe it is in OE10 and I just don't see it?   Where is it in 11.6?

Posted by George Potemkin on 22-Oct-2016 14:24

Data Admimistration -> Admin -> Security -> Edit User List...

And since V11 the _User table has the _User-sql-only-user index.

Posted by Betty Hardin on 25-Oct-2016 07:07

This is the solution:

ODBC users connect as “sysprogress” without having any _User table user defined for “sysprogress” .  This works if sysprogress is the only sql DBA defined in the db – in table _sysdbauth.

Step 1:   Add 'sysprogress' to the _user table to all progress dbs used by ODBC

Step 2:   Connect to the db via ODBC

Step 3:   View users with access to the database

               select * from sysprogress.sysdbauth

Step 4.   Revoke permissions from the 'new' dba user (which was added by the dump / load)

               revoke resource,dba from 'newuser'

Step 5.   Remove sysprogress from the _user table in all dbs used by ODBC

Posted by Betty Hardin on 10-Nov-2016 07:21

Have done a lot of testing with this and have not found any issues.   Is anyone aware of any issues that may result from revoking permissions from the 'newuser' (the user who created the db)?  Just wondering if there is something else I need to test.  

This thread is closed