liquibase: Don't use v$session in Oracle as the SELECT grant on this view is not usually available to any user
Description
In Oracle multiuser and multitenant environments the SELECT grant on v$session is usually not available for any user. To do a SELECT from this view is usually enabled by granting SELECT_CATALOG_ROLE to the user. However, in more restricted environments this is not done due to security and privacy concerns.
There is a solution, to call DBMS_UTILITY.DB_VERSION stored procedure. The DBMS_UTILITY package, by default, can be executed by PUBLIC.
To Reproduce
- Create an Oracle user without
SELECT_CATALOG_ROLE - Execute a Liquibase migration for this user and its schema
- Observe in the logs that
2020-04-05 21:26:00 [main] [INFO ] [l.database.core.OracleDatabase] Could not set check compatibility mode on OracleDatabase, assuming not running in any sort of compatibility mode: Cannot read from v$parameter: ORA-00942: table or view does not exist
About this issue
- Original URL
- State: closed
- Created 4 years ago
- Comments: 16 (4 by maintainers)
Commits related to this issue
- #1081 using DBMS_UTILITY.DB_VERSION Getting Oracle version using DBMS_UTILITY.DB_VERSION instead of querying v$parameter — committed to RockyMM/liquibase by deleted user 4 years ago
- #1081 resultSet is unused resultSet variable is not used therefore removing it — committed to RockyMM/liquibase by deleted user 4 years ago
- Updates for #1081 in 3.10.x — committed to liquibase/liquibase by molivasdat 4 years ago
➤ Erzsebet Carmean commented:
@RockyMM, I echo @ro-rah’s comment - thank you for working on this issue. I’ve added time for Datical internal QA to review the fix.
-erz
Thanks @RockyMM. We are looking to see which version we will be able to test this change in. Stay tuned.
Hey @ro-rah nice to hear that! Looking forward to 4.0 beta 😃
➤ Mario Champion commented:
hey internal reviewers, the linked PR is against master, so i moved this ticket into 4.0 beta 2 release as well.
@RockyMM perfect! I’ll continue to comment on the PR moving forward.
@RockyMM that’s great! Thanks for being vocal, bringing a solution, and of course being a part of our Liquibase community.