futureRollbackSQL generates an Update permission denied when running as a read only user


My understanding is that futureRollbackSQL  does not actually make updates in the database, and this is the functionality that I am seeing when I run a futureRollbackSQL against a database that does not yet have a databasechangelog table. However if I run it against a database that already has a Liquibase change run against it at a previous date, so the databasechangelog table already exists with data on it, I get an error telling me that my user does not have update permissions. The error is correct in that the user I am allowed to use for liquibase is not allowed to write anything to the database, but the problem is that I want to generate a script that can be run with a user that has full access to the database. 

The UpdateSQL part runs just fine and I get a script that can be run, but I have to submit a rollback script as well or they will not run my change script.

What makes this even stranger is that it generates the error halfway through the middle of the rollback generation and not at the end of the particular changeset.

Here is an extract showing the error message:

----------------------Start of file 

     [exec] – Rolling Back ChangeSet: ./changelog/ovaflo.changelog.CHM01199162.xml::ME FORM - DATA - NEW TAB - CHM01199162::DAVID::(Checksum: 3:6019853962771adb94c8ab719db2c24f)

     [exec] UPDATE [dbo].[ofp_Tab] SET [Disabled] = ‘Y’ WHERE Application = ‘of_MF_ASPNETRedirect.asp?pagename=/MF.Proj.MotorAssessmentForm.Web/MLForm.aspx’

     [exec] GO

     [exec] DELETE FROM [dbo].[DATABASECHANGELOG]  WHERE ID=‘ME FORM - DATA - NEW TAB - CHM01199162’ AND AUTHOR=‘DAVID’ AND FILENAME=’./changelog/ovaflo.changelog.CHM01199162.xml’

     [exec] GO

     [exec] – Rolling Back ChangeSet: ./changelog/ovaflo.changelog.CHM01199162.xml::ME FORM - SP - DC_SP_IsFormComplete - CHM01199162::DAVID::(Checksum: 3:e8497cf2a5deb1cf4d4c8c9dfa7d79b5)

     [exec] ALTER PROCEDURE dbo.DC_SP_IsFormComplete (

     [exec]  @userRef int,

     [exec]  @claimNo varchar(50),

-----------------------------Cut out large procedure

-----------------------------Return halfway through procedure

     [exec]    IF @checkAllSections = 1

     [exec]    BEGIN


     [exec]     SELECT

     [exec]      @fLiquibase Update Failed: Error executing SQL UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 0, [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL WHERE [ID] = 1: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.

     [exec] SEVERE 8/11/11 11:54 AM:liquibase: Error executing SQL UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 0, [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL WHERE [ID] = 1: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.

     [exec] liquibase.exception.LockException: liquibase.exception.DatabaseException: Error executing SQL UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 0, [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL WHERE [ID] = 1: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.

     [exec]  at liquibase.lockservice.LockService.releaseLock(LockService.java:152)

     [exec]  at liquibase.Liquibase.futureRollbackSQL(Liquibase.java:456)

     [exec]  at liquibase.integration.commandline.Main.doMigration(Main.java:723)

     [exec]  at liquibase.integration.commandline.Main.main(Main.java:115)

     [exec] Caused by: liquibase.exception.DatabaseException: Error executing SQL UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 0, [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL WHERE [ID] = 1: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.

     [exec]  at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)

     [exec]  at liquibase.executor.jvm.JdbcExecutor.update(JdbcExecutor.java:230)

     [exec]  at liquibase.executor.jvm.JdbcExecutor.update(JdbcExecutor.java:207)

     [exec]  at liquibase.lockservice.LockService.releaseLock(LockService.java:138)

     [exec]  at liquibase.Liquibase.futureRollbackSQL(Liquibase.java:457)

     [exec]  … 2 more

     [exec] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.

     [exec]  at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)

     [exec]  at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:246)

     [exec]  at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)

     [exec]  at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1491)

     [exec]  at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)

     [exec]  at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:677)

     [exec]  at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)

     [exec]  at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)

     [exec]  at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)

     [exec]  at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)

     [exec]  at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:634)

     [exec]  at liquibase.executor.jvm.JdbcExecutor$1UpdateStatementCallback.doInStatement(JdbcExecutor.java:222)

     [exec]  at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)

     [exec]  … 6 more

     [exec] For more information, use the --logLevel flag)

BUILD FAILED - 0 non-fatal error(s), 28 warning(s)

C:\Users\jrussell.MUFEP\Desktop\MF Work\Liquibase\DATABASE\PROD\Ovaflo\prod.ovaflo.build(36,16):

External Program Failed: C:\Users\jrussell.MUFEP\Desktop\MF Work\Liquibase\DATABASE\tools\ikvm\bin\ikvm.exe (return code was -1)

Total time: 13.9 seconds.

----------------------------------------End of file

If I illegally change the username to one with rights to write to the database, the rollback file generates perfectly, however I don’t see any evidence of it having actually changed anything in the DB.

I am using Liquibase 2.0.1

OH, and I forgot to mention, I saw the post at http://forum.liquibase.org/#topic/49382000000026023 which looks very similar to my error in that my ChangeLog looks like this





Jonathan Russell

The problem appears to be because liquibase of the databasechangeloglock table. That is a table that has a boolean value to allow only one instance of liquibase to run against a given database at the same time. It must be not attempting to lock it at the beginning (or you would have gotten an error right away) but liquibase has a bug that is making it attempt to unlock it still.

I created http://liquibase.jira.com/browse/CORE-994 to track it.