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?
Does option 8 show the name?
Will the code below return YES?
MESSAGE CAN-FIND(FIRST DICTDB._User) VIEW-AS ALERT-BOX.
Adriano - No - Option 8 does not show the remote user name.
George - YES - it does.
I believe that I've found the answer here:
knowledgebase.progress.com/.../P14172
"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:
knowledgebase.progress.com/.../20143
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?
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)
My guess is that the previous database was created by a UNIX userid named "sc".
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.
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.
Hmmm... maybe it is in OE10 and I just don't see it? Where is it in 11.6?
Data Admimistration -> Admin -> Security -> Edit User List...
And since V11 the _User table has the _User-sql-only-user index.
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
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.