ODBC connections - Disable read-committed

DBA users via ODBC is a recipe for disaster. I think you really need to be quicker than in the next few months, today would be good, yesterday would be better.

I've had a customer play around with g/l transactions in an Access result set, delete it - because something was wrong and then call support in a panick that half of the g/l transactions in the company had gone missing...

If you do not want to sort everything out today:

1. create an odbc user and grant this user SELECT rights on all tables

2. change the root password

After this you can get back to the next few months timetable.

Thanks Stefan,

I've had a customer play around with g/l transactions in an Access result set, delete it - because something was wrong and then call support in a panick that half of the g/l transactions in the company had gone missing...

Yep it's only a matter of time until something like this happens.

create an odbc user and grant this user SELECT rights on all tables

Reading through "Basic Guide to Defining Progress SQL-92 Database Permissions & Security" in the knowledgebase this can be achieved using the Create User statement? So:

/* Create DBA user */
create user 'dba1','password';
grant dba to 'dba1';
/* create standard users for sql access */
create user 'user1','x';
create user 'user2','x';
commit;
/* Now grant select access on the tables required */

Am I correct in thinking that this will not affect entries in the _user table? (there aren't any _user records at the moment - web client authenticate via a different method).

I figure the only way to sort SQL access properly is to cut the root access off and add individual users as required.

Chris

Yes, first priority is to get rid of dba access via odbc, so start with one user (for example odbcuser) who has select rights on all tables. You can fine tune this further (different users and different select rights per user) afterwards.

I don't do much of this myself, so the following is based on some experimentation in OpenEdge Architect.

CREATE USER 'odbcuser', 'password'

Does indeed create a user in _user, so if this table is empty, adding a record can / will change behavior on the ABL side of the database. This is not really my area since we always have _user filled with users, but I recall something about the operating system user being shown in as user in various places if no _user records exist.

Regards,

Stefan

Just thought about this a bit more. If you currently cannot handle the impact of _user records being created I think you can also REVOKE all write rights on all tables for the dba user - but I'm not sure.

An alternative could be to revoke dba rights for the dba user. If you need to get in as dba again you can always use the sysprogress back door to get back in, perform your dba stuff and then return things to how they were.

Another thing that crossed my mind, but not sure if this is at the right level, is to lower the lock table entries on the SQL92 broker to the minimum of 32 - if possible that will make it pretty difficult for report users to work with a read-commited odbc setting.

One option a customer selected was to install OE Replication Plus and let the ODBC user only access the target server. This read-only mode cannot be broken.

Replication Plus has some side effects:

costs (license + hardware)

hot standby system included

if RO reporting can complete removed from the production system, also perfomance improvements on the production site. Because of the other hardware handle this.

others pro and cons...

A progress kb article was just published which addresses the subject: https://progress.my.salesforce.com/articles/Article/P125338?popup=true

As the DBA user you can grant rights to users that do not exist and this will implictly create them on the SQL side only:

GRANT SELECT ON pub.Customer to user2;

Just what I was after.  Thanks!