Hello Connor,
Appreciate your input on the impact on security.
My question was mainly focussed on how the privilege model works in the database. The documentation appears to suggest that user needs to have SYS level privileges in order to be able to use DBMS_QOPATCH APIs. However, I am able to grant only EXECUTE privilege on DBMS_QOPATCH to a local user and that user is able to access patch details.
First, without EXECUTE privilege on DBMS_QOPATCH, I get error as expected:
SQL> show user
USER is "NP"
SQL> select * from session_privs ;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
SELECT ANY TABLE
CREATE SEQUENCE
CREATE PROCEDURE
MANAGE ANY QUEUE
ADMINISTER RESOURCE MANAGER
SELECT ANY DICTIONARY
GRANT ANY OBJECT PRIVILEGE
ANALYZE ANY DICTIONARY
ADVISOR
CREATE JOB
ADMINISTER SQL MANAGEMENT OBJECT
14 rows selected.
Elapsed: 00:00:00.16
SQL> select * from session_roles ;
ROLE
--------------------------------------------------------------------------------------------------------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
AQ_USER_ROLE
OEM_MONITOR
Elapsed: 00:00:00.08
SQL> set pagesize 0
SQL> set long 1000000
SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;
select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual
*
ERROR at line 1:
ORA-00904: "DBMS_QOPATCH"."GET_OPATCH_XSLT": invalid identifier
Elapsed: 00:00:00.02
SQL> select xmltransform(sys.dbms_qopatch.get_opatch_install_info, sys.dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;
select xmltransform(sys.dbms_qopatch.get_opatch_install_info, sys.dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual
*
ERROR at line 1:
ORA-00904: "SYS"."DBMS_QOPATCH"."GET_OPATCH_XSLT": invalid identifier
Elapsed: 00:00:00.01
After granting EXECUTE on DBMS_QOPATCH, it works
SQL> select * from user_tab_privs where table_name = 'DBMS_QOPATCH' ;
NP SYS
DBMS_QOPATCH SYS EXECUTE
NO NO NO PACKAGE NO
Elapsed: 00:00:01.71
SQL> select xmltransform(sys.dbms_qopatch.get_opatch_install_info, sys.dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;
Oracle Home : /opt/oracle/product/19c/dbhome_1
Inventory : /opt/oracle/oraInventory
Elapsed: 00:00:27.86
I still believe that the above is correct behaviour and the responsibility should be with administrators to decide which users, if any, should be able to query patch inventory.
However, the documentation appears to be misleading and not in sync with the actual implementation.
What am I missing?
Thanks in advance
February 17, 2025 - 7:53 am UTC
We're probably quibbling semantics here. From the docs
"The DBMS_QOPATCH package is created as part of SYS schema and SYS is the only user who can execute these subprograms."
This is indeed true (out of the box).
However, once you grant EXECUTE you have indeed given the grantee SYS-level access to that package, ie, it runs with the same rights as SYS.