Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-47431

VerifyOracle fails if Liferay is connected to the DB through a proxy schema

    Details

      Description

      LPS-43794 introduces method convertColumnToClob() which uses count to determine whether certain columns have CLOB data type. The problem with this code is that the query returns zero if a proxy schema is used to connect to Liferay's repository.

      Steps to reproduce

      1) Create two empty schema in an Oracle database (LPORTAL_REPOS and LPORTAL_PROXY)

      2) Startup a Liferay 6.2 EE instance, connect it to LPORTAL_REPOS and have tables created

      3) User LPORTAL_PROXY will have synonyms for the tables of
      LPORTAL_REPOS and the necessary privileges (SELECT/INSERT/UPDATE/DELETE) will be
      granted.

      To make this happen execute the following SQL script.

      set serveroutput on
      
      declare
      
        v_owner varchar2(50) := 'LPORTAL_REPOS';
        v_proxy varchar2(50) := 'LPORTAL_PROXY';
      
        procedure exec(p_sql in varchar2) is
        begin
          dbms_output.put(p_sql || ' ... ');
          execute immediate p_sql;
          dbms_output.put_line('OK');
        exception
          when others then
            dbms_output.put_line(sqlerrm);
        end;
      
      begin
      
        dbms_output.enable(1024*1024);
      
        for tab in
          (select table_name from dba_tables where owner = v_owner)
        loop
          -- Create synonym
          exec('create or replace synonym ' ||
            v_proxy || '.' || tab.table_name || ' for ' ||
            v_owner || '.' || tab.table_name);
          -- Grant
          exec('grant select, insert, update, delete on ' ||
            v_owner || '.' || tab.table_name || ' to ' || v_proxy);
        end loop;
      end;
      /
      

      4) Stop Liferay and set up the following property

      verify.frequency=-1
      

      5) Upon restart when VerifyProcessSuite run, observe the following error

      com.liferay.portal.verify.VerifyException: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges.
      

      This happens, because in class VerifyOracle columns from VARCHAR to CLOB are updated and the following SQL may return zero not because there aren't any CLOB columns, but because for user LPORTAL_PROXY view user_tab_columns doesn't contain any data about the objects of LPORTAL_REPOS.

      select count(*) from user_tab_columns where table_name = 'ASSETENTRY' and column_name = 'DESCRIPTION' and data_type = 'CLOB';
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                haoliang.wu Haoliang Wu (Inactive)
                Reporter:
                laszlo.csontos Laszlo Csontos (Inactive)
                Participants of an Issue:
                Recent user:
                Esther Sanz
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  5 years, 20 weeks, 6 days ago

                  Packages

                  Version Package
                  6.2.2 CE GA3
                  6.2.X EE
                  7.0.0 M1