OE 11.5: SQL and ABL, performance, problems

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.

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)

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

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.

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

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]

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.

"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!