Kbase P24790: Error 7872 when trying to GRANT or REVOKE user permissions
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
2/3/2010 |
|
Status: Verified
SYMPTOM(s):
Error 7872 when granting SQL-92 privileges
GRANT of SQL privileges to a user fails with error:
REVOKE of SQL privileges to a user fails with error:
Failure to acquire exclusive schema lock for DDL operation. (7872)
SQLState=HY000
ErrorCode=-210017
[JDBC Progress Driver]:Failure to acquire exclusive schema lock for DDL operation. (7872)
FACT(s) (Environment):
There are other users connected to the database
Progress 9.x
OpenEdge 10.x
CAUSE:
Bug# OE00080420
CAUSE:
In any version prior to OpenEdge version 10.2A to add, delete or otherwise modify a users authorization record there can not be any transactions active on the system. in affect the system is in single user mode. This is because a schema lock is taken out on the entire schema while the operation is performed.
As per the SQL standards, a transaction is any action which is accessing database data. If a user is running a transaction, the tables accessed in that transaction will be locked regardless of whether the user manipulating the table definitions or not. Prohibiting schema updates to the database. This is to prevent table definitions being changed while a user is actually working with records from a table.
Even though a transaction may just be reading data it is still considered a transaction.
The classic 4GL viewpoint is that a transaction is an action changing database data. This differs considerably from the standard SQL concept. If the 4GL application has a share lock, then it also has at a minimum, a share lock on the database's schema.
FIX:
Upgrade to OpenEdge 10.2A or higher.
To resolve the problem prior to OpenEdge version 10.2A perform one of the following two options:
1. Perform the GRANT or REVOKE operation when the database is not in use (the clients transaction has completed) or disconnect the clients that are running transactions.
Users may remain connected to the database unless they are running the Data Dictionary. Updating a table or field from the Data Dictionary will lock the entire Schema.
Or
2. Have the users modifying Schema, or performing share locks, commit the Transactions. This will release the schema lock and a database connection can then be made.