"Oracle user needs execute access on DBMS_LOCK" error messages are back

Eric Hagfors ●

Hi Stefan,

Thanks for reaching out to support. was there a previous ticket on this issue? Were there updates to the Oracle setup in addition to Yellowfin upgrades? Any observed behavior in the system outside the errors in the logs?

In order to get a bug to developers I'll need detailed replication steps, eg

1. Setup Yf version x on Oracle DB version y with user z

2 Upgrade- access issue

I'd recommend restoring previous working setup if this is causing issues.

Thanks,

Eric

Reply URL

1

Stefan Hall ●

Hi Eric,

I think I have already shared all the information with you.

YF needs special permissions to be able to set its LOCKs in an Oracle DB. Otherwise the YF error message will appear in the logs.

I have set the permissions exactly as YF requires and still the error message occurs. This was not the case in a previous version. Which one I can't tell you anymore. Here are the answers to your questions, even though they may not all be relevant in this case.

YF 9.9/8, Oracle DB 19.16-18, YFAdmin or any other name for the DB User
no, no error messages

A rollback to version 9.8.x is not possible.

Let's shorten the discussion a bit here. YF is requesting certain permissions for the DB User in the logs, I have set these and YF is still requesting these permissions, Please submit the case to the developers.

Thanks in advanced

;) Stefan

URL

1

Eric Hagfors ●

HI Stefan,

Thanks for the reply,

Just for context, this was showing, then not showing for a bit after the permission change, and now showing again? What was the timelineline / changelog on that if possible? Is this a new implementation?

It looks like there's a previous related ticket here-

https://community.yellowfinbi.com/topic/26862-warnings-in-the-log-related-to-dbms_lock-oracle-but-dbms-is-deprecated

It sounds like there may have been for a particular code change here, is this what you're referring to?

Would you be able to provide a compressed copy of your logs folder for analysis? You can upload to our FTP https://ftp.yellowfin.bi using the "send files" option, just reply here with a filename and any relevant timestamps.

Also, would you be able to provide a copy of your info.jsp page for reference in this case?

Thanks,
Eric

Reply URL

1

Stefan Hall ●

Hi Eric,

I have created a little video for you. It shows:

the error message in the log
the set user permissions for YF DB User in the Oracle DB
that these permissions work, using selects on the views given in the error message

and a complete YF start logging in DEBUG mode
and info.jsp.

I really hope that this is enough to actually get a developer involved. file name: "yf_error_dbms_lock_complete.zip"

;) Stefan

URL

1

Eric Hagfors ●

Hi Stefan,

Based on the previous developer comment,

"This is a function of the Liquibase session locking library that was added recently to Yellowfin (which works for MySQL, Postgres and Oracle). Prior to Session Locking being added, liquibase is locked by writing a row to the DATABASECHANGELOCK table. However this cause problems if the process crashes and leaves the record there.

The Session Lock uses a database lock to lock, and block other processes from processing data at the same time.

The Oracle lock does require permissions.. However Yellowfin will test for permissions, and if it doesn't have access it will revert to the table row locking mechanism."

I suppose if you wanted the the warning investigated we could look into it, but it doesn't sound like this has any impact on functionality. Hope this helps, let me know if you need anything additionally.

Thanks,

Eric

Hi Stefan,

Based on the previous developer comment,

"This is a function of the Liquibase session locking library that was added recently to Yellowfin (which works for MySQL, Postgres and Oracle). Prior to Session Locking being added, liquibase is locked by writing a row to the DATABASECHANGELOCK table. However this cause problems if the process crashes and leaves the record there.

The Session Lock uses a database lock to lock, and block other processes from processing data at the same time.

The Oracle lock does require permissions.. However Yellowfin will test for permissions, and if it doesn't have access it will revert to the table row locking mechanism."

I suppose if you wanted the the warning investigated we could look into it, but it doesn't sound like this has any impact on functionality. Hope this helps, let me know if you need anything additionally.

Thanks,

Eric

Reply URL

1

Stefan Hall ●

Hi Eric,

I know the information and unfortunately it does not help.

I had our DBAs set up the required permissions for the user specifically and they worked.

Now the existing permissions don't seem to work anymore and I would like to know why?
Maybe your check is not correct anymore or the error text does not show all required permissions. Or something has changed with Oracle version 19.18.

Please clarify

;) Stefan

URL

1

Eric Hagfors ●

Hi Stefan,

Thanks for the reply, what was the working version of YF and Oracle for reference?

Thanks,
Eric

Reply URL

1

Stefan Hall ●

Hi Eric,

unfortunately, I can't tell you exactly. I don't regularly look through all our log files. We are still not live. In the live environment, we monitor that more closely.

I assume Oracle 19.17 and several YF 8.8.x.x versions.

;) Stefan

URL

1

Eric Hagfors ●

Hi Stefan,

Thanks for the reply. I dug deeper in our dev platform and found a potential root cause on this.

So I can see these this dev tasks in our platform for 9.73 / 9.8 -

Liquibase Session Locking for SQL Server
Implemented native session locks for SQL Server in migration jobs
Implement Database Session Locking for migration jobs when using MySQL, PostgreSQL or Oracle as a repository

There's mention in the comments of a "fork" referring to the exact functionality we're seeing -

The Oracle code will now check whether the database has the correct permissions (by attempting to run some of the locking queries).

Perhaps those queries are no longer working for some reason. Maybe the logs on the Oracle side show something?

More notably though, subsequently I see this implementation from 9.9 -

Upgraded Liquibase library to Version 4.21.

This liquibase update was a pretty significant jump, and addressed some security issues I believe, but I'm wondering if something else may have changed that would make the previously working locking "check" to fail. I'll work on replicating - if you could check old or install / upgrade logs to identify specific build it would be helpful.

1. Install version including original session locking functionality to oracle, see it working

2. Upgrade to 9.9

3. Startup, see oracle warning

If this works, I should be able to get this to devs.

That said, the only thing I could see this functionality impacting would be the startup process, maybe a slight performance increase with that, so fortunately there aren't really be any widespread adverse effects here.

Thanks,

Eric

Hi Stefan,

Thanks for the reply. I dug deeper in our dev platform and found a potential root cause on this.

So I can see these this dev tasks in our platform for 9.73 / 9.8 -

Liquibase Session Locking for SQL Server
Implemented native session locks for SQL Server in migration jobs
Implement Database Session Locking for migration jobs when using MySQL, PostgreSQL or Oracle as a repository

There's mention in the comments of a "fork" referring to the exact functionality we're seeing -

The Oracle code will now check whether the database has the correct permissions (by attempting to run some of the locking queries).

Perhaps those queries are no longer working for some reason. Maybe the logs on the Oracle side show something?

More notably though, subsequently I see this implementation from 9.9 -

Upgraded Liquibase library to Version 4.21.

This liquibase update was a pretty significant jump, and addressed some security issues I believe, but I'm wondering if something else may have changed that would make the previously working locking "check" to fail. I'll work on replicating - if you could check old or install / upgrade logs to identify specific build it would be helpful.

1. Install version including original session locking functionality to oracle, see it working

2. Upgrade to 9.9

3. Startup, see oracle warning

If this works, I should be able to get this to devs.

That said, the only thing I could see this functionality impacting would be the startup process, maybe a slight performance increase with that, so fortunately there aren't really be any widespread adverse effects here.

Thanks,

Eric

URL

1

Eric Hagfors ●

Hi Stefan,

Just wanted to check in to see if you had a chance to review my reply at this time.

Thanks,

Eric

Reply URL

1

Stefan Hall ●

Hi Eric,

I had read your last message on my phone and didn't understand that I was supposed to do something. Sorry

On the matter, I can't help you here and do more test installations for YF. Possible causes and the solution must be found out by your developers themselves. I think I have already invested (too) much time here and provided enough material for a real analysis.

Actually, I thought that we are already further here and the problem will be solved in the next few days.

;) Stefan

URL

1

Eric Hagfors ●

Hi Stefan,

In order to test

1. Install version including original session locking functionality to oracle, see it working

2. Upgrade to 9.9

3. Startup, see oracle warning

I would need specific working Oracle and YF versions / build dates. Let me know if this is a possibility.

Thanks,
Eric

Reply URL

1

Eric Hagfors ●

Hi Stefan,

I'm going to go ahead and mark this ticket as Completed due to inactivity at this time. Feel welcome to reach out in the future.

Thanks,
Eric

Reply URL

1

Stefan Hall ●

Hi Eric,
you're kidding, right? The problem doesn't solve itself and I'm happy to help as part of a customer.

But your last request goes far beyond the customer relationship and I can't take over the tasks from support. I can point out problems/errors, I can provide log files and add a video if necessary, but at some point support or a developer with their code knowledge has to take action.

So no, I'm not going to install various versions on my end to provide you with the faulty line of code. I expect that from YF. I want to use your product and I need to be able to run it cleanly.

;) Stefan

Reply URL

1

Sainath Yadav ●

Hello Stefan,

Happy Thursday! I hope you are doing well.

With regards to this case, we had a offline discussion with the Product team to understand a few things better in different directions altogether. Please see the detailed updates, I hope that helps.

We added session lock support for MySQL, PostgreSQL, Oracle, and (later) SQL Server. MySQL, PostgreSQL and SQL Server, do not require any database configuration to work, however, Oracle requires changes to it's configuration to support session locks.

The message: Oracle user needs execute access on DBMS_LOCK, and select access on SYS.V_$LOCK and SYS.V_$SESSION to use session locking was added to show that Oracle doesn't have the configuration to use Session locking. However, it will just fall-back to the standard Liquibase locking if it is unsupported.

So this message can be ignored, or the client can make the configuration changes to Oracle and enable Session locks, which it sounds like they did. Yellowfin should still work fine with the fall-back locking method. The locking mechanism is only required when Liquibase runs, which is in the Updater, and during Yellowfin startup (to check if there are any migration jobs to run, and run those migration jobs). Doesn't have any impact on how Yellowfin actually runs after Startup. So this reiterates that we can ignore the warnings. I hope this information helps.

Regards

Sainath Yadav

Manager, Technical Support

E: sainath.yadav@yellowfin.bi

50229a02e34b348a2b543170cda867c3

Reply URL

1

Stefan Hall ●

Hi Sainath,

i understand your approach, but we work in a high security area and pay close attention to log entries, even if they seem unnecessary.

That is exactly why I have adjusted the configuration in the Oracle DB, which I have also proven with appropriate tests and the information provided. I logged into the DB as a user (the same one that YF uses) and retrieved exactly the required information and it worked

It just doesn't work anymore when your program does it. I didn't have this behavior in a version prior to 9.9, so the permissions in Oracle have worked before and the warning was gone. From my point of view this is a clear sign that either something has changed - maybe you are asking for more than the warning says or you are asking for it differently or something has changed with one of the last Oracle patches, then your warning text would be incomplete.

Sorry for the frank words, I work in the IT support business & developer myself and I don't understand why I as a customer should pick out the faulty line of code for you. That's unfortunately how the discussion feels to me right now. Otherwise, I hope I have proven often enough that I have enough IT knowledge and experience to simply believe me. I am right with 90+ percent of my error messages and in the end it is a defect, even if you need months to reproduce it.

;) Stefan

URL

1

Sainath Yadav ●

Hello Stefan,

Thank you for your response. I certainly get that and agree with the point that there could be something unusual with these warning messages, Despite configuring based on the requirements or adding the correct Oracle permissions, the warnings are still showing up. The development team is already aware of this situation, and definitely there will be plans for testing a few things in the future. However, Yellowfin will revert to the default locking mechanism and still work.

Regards

Sainath Yadav

Manager, Technical Support

URL

1

Stefan Hall ●

Hi Sainath,

that's OK with me, just please don't mark the ticket as solved. It is a defect and that should be recognizable for me as well. Otherwise I lose the overview with the large amount of reported problems / defects. I will ask again in 2-3 months.

Thanks a lot

;) Stefan

URL

1

Sainath Yadav ●

Hello Stefan,

We have updated the ticket status to Defect Logged, and the same has been confirmed by the Product team as well. We will keep you informed of further developments in this case. Feel free to reach out to us if you have any questions; we would be happy to assist.

Regards

Sainath Yadav

Manager, Technical Support

E: sainath.yadav@yellowfin.bi

50229a02e34b348a2b543170cda867c3

URL

"Oracle user needs execute access on DBMS_LOCK" error messages are back

Replies (9)