Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-20994

Zabbix limits to use Oracle DB by DB owner only. A fix proposed.

    XMLWordPrintable

Details

    • Team A
    • Roadmap

    Description

      Imagine that I want to use a separate DB user for frontend connecting to my Oracle DB. The user which was not used to create the DB originally.
      It does not work in zabbix!
      Zabbix complains that "dbversion" table was not found.

      Reason of that is that USER_TABLES in line #32 https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/ui/include/classes/db/OracleDbBackend.php?at=release/5.0#32
      will limit search tables owner, which is not something we actually need to limit on.
      If we would use ALL_TABLES instead - we could pass the check.
      Oracle doc about them:
      https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4473.htm#REFRN26286
      https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#REFRN20286

      There is this generic function for all type of databases:
      https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/ui/include/classes/db/DbBackend.php?at=refs%2Fheads%2Frelease%2F5.0#122

      	public function checkDbVersion() {
      		if (!$this->checkDbVersionTable()) {
      			return false;
      		}
      
      		$version = DBfetch(DBselect('SELECT dv.mandatory FROM dbversion dv'));
      
      		if ($version['mandatory'] != ZABBIX_DB_VERSION) {
      			$this->setError(_s('The Zabbix database version does not match current requirements. Your database version: %1$s. Required version: %2$s. Please contact your system administrator.',
      				$version['mandatory'], ZABBIX_DB_VERSION
      			));
      
      			return false;
      		}
      
      		return true;
      	}
       

      where for checkDbVersionTable() function we perform specific SQLs for different DB engines - to just check the table existence. That's where it fails for Oracle's SQL:

      		$table_exists = DBfetch(DBselect("SELECT table_name FROM user_tables WHERE table_name='DBVERSION'"));
      

      After check the table existence, in the generic function we continue and also check a value from the table, using already a generic SQL.

      So, even if we imagine that checking the table existence from "all_tables" will provide a false result (the uses has access to many zabbix databases on the Oracle server), we still will check it by reading from the table.

      So, I personally consider it as a safe change to "user_tables" to "all_tables" in the line #32
      https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/ui/include/classes/db/OracleDbBackend.php?at=release/5.0#32

      Thus, requesting this to be changed.

      It's also actual for server code, where in DBtable_exists() we do:
      https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/libs/zbxdbhigh/db.c?at=refs%2Fheads%2Frelease%2F5.0#2068

      #elif defined(HAVE_ORACLE)
      	result = DBselect(
      			"select 1"
      			" from tab"
      			" where tabtype='TABLE'"
      				" and lower(tname)='%s'",
      			table_name_esc);
      

      which was also failing for not an DB owner user.
      Here is a good discussion to read as for server's code: https://stackoverflow.com/questions/14572361/difference-between-tab-table-and-all-tables-in-oracle

      Attachments

        Activity

          People

            zabbix.dev Zabbix Development Team
            zalex_ua Oleksii Zagorskyi
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated: