OE 11.5: SQL and ABL, performance, problems

Posted by Stefan Marquardt on 10-Mar-2016 01:23

It's painful that I can't access tables for SQL only from the ABL.
Currently I need to grant or revoke rights on tables for many users.
I like to have an ABL program to set them but this is not possible.

The solution is to create an input file for sqlexp and run it.
But the performance to set the rights for all tables for all existing users (read only or not) is very poor.

And on the top during the execution of the sqlexp the schema is locked very often and ABL users are getting a schema locked error during the try to login (connect).
Sorry - but this may not happen that an sql command blocks ABL users!
And on the top I am missing more general Rules in the sql access, like DataReader, this would reduce the amount of command for sqlexp.

If I can't access SQL rights from ABL why SQL is blocking ABL the other way around?
This isn't a question, this is a design error - my opinion.


Try it on your own, take every user from _user and create a revoke select command for every table.
(I have <500 tables and ~ 500 users)
The result is a very long running sqlexp and during this time schema locks.
(revoke select on PUB."Table" from "USER" ;

All Replies

Posted by Mohd Sayeed Akthar on 10-Mar-2016 04:45

Hi Stefan,

I looked at this problem and I have following observations.

1. When I ran the revoke command on multiple users for multiple tables, I too observed that, sqlexp is taking significant amount of time to complete the execution. Is it possible for you to create an RFA, so that we can spend some time to understand the reason behind slow execution. This should not be this slow!!

2. Revoke command takes locks on certain system tables (like, "_db", "_systabauth") which are also used by ABL, because of which ABL users are not able to login when revoke is executing. This is expected.

3. >> And on the top I am missing more general Rules in the sql access, like DataReader, this would reduce the amount of command for sqlexp.

Are you talking about SQL Roles? if that is the case, SQL does not have such capability as you know. If you are mentioning about something else, can you please explain little more about this?

Please let me know if you have any questions.

Thanks,

Akthar.

Posted by Tinco on 10-Mar-2016 06:52

To have OpenEdge implement an equivalent of the MS SQL 'Database role membership' called 'db_datareader' that give members of that predefined db_datareader database role the permission to run a SELECT statement against any table or view in a database please make sure to submit a Progress Enhancement Request through the Progress Community IDEAS:

knowledgebase.progress.com/.../P11255

For now the existing workarounds are

- use 'grant tablename select to PUBLIC' to grant SQL select access to a table to all users that can access the database and a 4GL script like

def stream s.

output stream s to grant.sql.

for each _file where (0 _file-num).

put stream s unformatted "grant select on pub." _file-name " to public;" skip.

end.

put stream s unformatted "commit work;" skip.

output close.

to generate the grant commands needed for all database tables.

(it is then not possible to revoke the access to a table to an individual database user though)

- create a SQL user having select rights on all database tables and let the database users use this SQL user account instead of their own regular (4GL) database account.

- modify a script like the one described on

knowledgebase.progress.com/.../000039715

to generate SQL grant commands for all users defined in the database.

and use OpenEdge commands like

sqlschema -u sysprogress -a sysprogress -g PUB."tablename" -o outputFileTablename progress:T:localhost:5555:databaseName

and remove the SQL grant commands already mentioned in those files outputFileTablename.dfsql from the SQL grant script.

- implement a trigger on the user table to generate the needed SQL grant select statements for new users and use a custom script after the addition of a new table in the database. (need custom coding)

Posted by Stefan Marquardt on 10-Mar-2016 08:05

Hi Akthar,

what is a RFA?

I have already an open case 00340592.

I have now so many "workaround" but you are right, it's too slow - this I the real problem.

And on top the locks, expected (not from me) but bad in practice.

Stefan

Posted by Keith Sudbury on 10-Mar-2016 08:17

We have similar issues. I also consider this a bug/design issue that needs to be fixed. There is really no logical reason why changing SQL permissions for a user should block other users from accessing the database... or invalidate the schema cache.

A few things I have found to help out (but not solve) the issue...

1) Only one commit for the entire script (not one per grant) to reduce the number of schema locks required.

2) Use the -schlockwq DB startup param to reduce some of the impact of schema locks.

Posted by Mohd Sayeed Akthar on 10-Mar-2016 08:26

Hi Stefan,

Please ignore the request of opening an RFA. RFA is internal to Progress, and we will take care of that.

If we can reduce the execution time of a Revoke command, i expect that the locking time should reduce a lot.

Thanks,
Akthar.

Posted by Stefan Marquardt on 10-Mar-2016 08:36

One commit for all would be a problem, having one line user/table creates in the moment 15MB input file. (grant or revoke for every user)

This runs in my env ~ 14 minutes, too long. In other versions I  have each user in brackets for each table, but this doesn't really help.

That's the cause to get a DataReader, then I would have one line each user, nothing more.

Can you take over the support case and have a look in dev please?

I will verify schlockwq, understanding pro and contra

Posted by steve pittman on 10-Mar-2016 08:53

Keith, Stefan -

Thanks for making the point about the performance impact of sql authorization DDL (grant/revoke), and how it makes sense to consider this a defect.

In recent releases, with features like Multi-tenancy and Table Partitioning, our db architecture has introduced new ways to manage concurrent, online schema access.  The MT and TP schema locking is narrowly targeted so that it does not hamper online performance (at least, not very much we believe).

We should be able to use some of these methods for sql authorization so that Grant/Revoke don't have to have so much locking impact.

We will do some investigation to better understand such a change (i.e., more narrowly targeted locking). We'll consider this a performance defect.

As always, hearing more about how an improvement would impact customers has a significant influence on when we implement and deliver changes!

Thanks again!                  .....steve pittman   [OE sql software architect]

Posted by bbrennan on 10-Mar-2016 09:14

I agree with everything Stefan has reported.  We too have very similar issues.

Our typical use case is a customer with an OE application from some other vendor.  The end customers typically have little to no IT help on site.  They have at best minimal knowledge of the Progress database and no knowledge of SQL.  At some point, someone decides to use a SQL reporting tool like Crystal and then heads down the path of SQL users, permissions, GRANTing and REVOKEing.  

They also stumble on the command line mess that is SQLEXP.  Using SQLEXP for very large scripts is taxing since you have no feedback on where it is or what it is doing.  It is as likely not to work based on the locking Stefan described.  I am not aware of any mechanism that will predict if my script will run cleanly without hitting a lock. That then leads to needing to kick everyone off a production server just to implement the proper updates.  If you find you made a mistake, head back to the start and re-do it again including perhaps taking the server back from the users.

TS may suggest using a third part tool like WinSQL.  Very nice looking tool but the syntax required for a script is different than what you would feed SQLEXP.  

Both WinSQL and SQLEXP assume you are able to connect to the database as a DBA.  That is not always a valid assumption.

There remains a great deal on confusion still about the default DBA account SYSPROGRESS and the need to replace it with a user defined DBA privileged account.  The security models between OE and SQL are opposite of each other.  It would be spectacular to have a single tool to administer security in both realms.

This typically gets set once, after a lot of mistakes and rework, to the point that it finally supports running Crystal.  Flash forward 3,4 or 5 years and the VAR/reseller builds a new server most likely not including any of the SQL bits.  All of this repeats again on how to migrate SQL users and permissions to the newly built or upgraded database.

Lastly, the user name that created the database may not actually be a a valid user on the end user's system.  'Chuck' from the reseller company may create and then zip up the new structures to send to the end user.  The end user has no idea of 'Chuck's' credentials.

A valid but flawed (IMO) solution is to create a _User record for SYSPROGRESS/SYSPROGRESS.  It has always struck me as a bit of a security hole being able to do this and then gain access to the entire SQL side of the database.

This problem is like getting sand in your eye.  It won't kill anyone but is sure is irritating every time.

Posted by Stefan Marquardt on 23-Mar-2016 05:48

"performance impact of sql authorization DDL (grant/revoke) ...

One additional comment:

If I run the same sql inp twice without any change it needs the same time, locking all the time although there is no need for any update!

This thread is closed