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

Stefan Hall shared this problem 10 months ago
Defect Logged

Hi,

our DBAs had assigned the authorization and the error messages were gone. With the update to v9.9 I took a closer look at the logs again and unfortunately the error message is back !?

Of course I first checked the DB user permissions, but this looks fine - see screenshot. I can display the data of the two views mentioned (SYS.V_$LOCK and SYS.V_$SESSION), as well as "begin DBMS_LOCK.SLEEP(1); End; " successfully. Both of these together indicate that the permissions are correct and working as well.

Only YF doesn't notice it anymore and reports again :

YF:2023-08-04 11:40:55.625: WARN (OracleLockService) - Error during capability test for oracle session locking: java.sql.SQLSyntaxErrorException: ORA-00942: Tabelle oder View nicht vorhanden

YF:2023-08-04 11:40:55.625: WARN (OracleLockService) - Oracle user needs execute access on DBMS_LOCK, and select access on SYS.V_$LOCK and SYS.V_$SESSION to use session locking

Please check this on your end.

;) Stefan

5db671793f776cdbe96d75b7efeffe21

Replies (9)

photo
1

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

photo
1

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.


  1. YF 9.9/8, Oracle DB 19.16-18, YFAdmin or any other name for the DB User
  2. 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

photo
photo
1

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

photo
1

Hi Eric,

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

  1. the error message in the log
  2. the set user permissions for YF DB User in the Oracle DB
  3. 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

photo
photo
1

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

photo
1

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

photo
photo
1

Hi Stefan,

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

Thanks,
Eric

photo
1

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

photo
1

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

photo
photo
1

Hi Stefan,

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

Thanks,

Eric

photo
1

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

photo
photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
Leave a Comment
 
Attach a file