3 Replies Latest reply: Jul 13, 2011 12:07 AM by 874821 RSS

    Rights issue when creating context index.

    835615
      Hi,

      We have two users, "app" and "appsys" . with most permissions granted through roles.
      "app" has just enough permissions for the application to run. No update/delete/etc on most tables, just select and (on a select few) insert rights.
      "appsys" is the "crew" user, with enough rights for basic day-to-day administration and most upgrade scripts that come with new code releases.

      Other than that, it's a pretty much default EE 11.2.0.1.0.

      Therefore, my first preference was to have 'appsys' create the desired text index for use by 'app':

      CREATE INDEX app.table_text_ix ON app.table (xml)
           INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (EVERY "TRUNC(SYSDATE + 1) + 03/24") TRANSACTIONAL');

      Have also tried the bare:

      CREATE INDEX app.table_text_ix ON app.table (xml)
           INDEXTYPE IS CTXSYS.CONTEXT;

      Both give the same result:

      Error at Command Line:1 Column:19
      Error report:
      SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
      ORA-20000: Oracle Text error:
      DRG-50857: oracle error in drvxtab.create_index_tables
      ORA-01031: insufficient privileges
      ORA-06512: at "CTXSYS.DRUE", line 160
      ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
      29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
      *Cause:    Failed to successfully execute the ODCIIndexCreate routine.
      *Action:   Check to see if the routine has been coded correctly.

      When using user app (temporarily granting relevant privileges):

      ERROR at line 1:
      ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
      ORA-20000: Oracle Text error:
      DRG-50857: oracle error in drvddl.IndexCreate
      ORA-27486: insufficient privileges
      ORA-06512: at "CTXSYS.DRUE", line 160
      ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

      I've tried granting just about anything but the kitchen sink to either user, both direct and indirect, but it still won't let me. Even SYS seems to be missing something.
      Or rather, I seem to be missing something. But what? I've read a few prior threads about this issue and tried most suggestions: still no go.

      Things I've GRANTed, as per documentation:
      - CTXAPP role
      - execute on ctxsys.ctx_ddl
      - RESOURCE
      - CONNECT

      Other privileges it already had OR that I've tried granting:
      EXECUTE ANY PROCEDURE
      CREATE SESSION
      UNLIMITED TABLESPACE
      CREATE TABLE
      CREATE ANY TABLE
      ALTER ANY TABLE
      DROP ANY TABLE
      CREATE CLUSTER
      CREATE ANY INDEX
      ALTER ANY INDEX
      DROP ANY INDEX
      CREATE SEQUENCE
      CREATE PROCEDURE
      CREATE ANY PROCEDURE
      CREATE ANY JOBB
      CREATE TRIGGER
      CREATE ANY TRIGGER
      CREATE TYPE
      CREATE OPERATOR
      CREATE INDEXTYPE

      Have also tried the grant select on sys.all_users to public; solution from a previous thread.

      So... I'm lost... what am I missing?
        • 1. Re: Rights issue when creating context index.
          Barbara Boehmer
          I don't know if what you have posted is pseudo-code or what you actually ran. Table is not a valid name for a table. It is best to post a copy and paste of an actual run from SQL*Plus, including line numbers and errors. I suggest that you start with a very simple example, avoiding reserve names like table and xml, just creating a simple text index on a table with a varchar2 column, as the same user that owns the table, checking everything along the way. Once you get that working, then you can add the other things one at a time. I have provided a script for you to run and post the results of below, followed by a run of the script on my system to show what you should get.

          -- script for you to run from SQL*Plus and post a copy and paste of the results of:
          -- version:
          SELECT * FROM v$version
          /
          -- check that ctxsys user exists:
          SELECT username 
          FROM   all_users
          WHERE  username = 'CTXSYS'
          /
          -- check that Oracle Text is installed properly:
          SELECT *
          FROM   dba_registry
          WHERE  comp_id = 'CONTEXT'
          /
          -- create test user:
          CREATE USER test IDENTIFIED BY test
          /
          -- grant privileges:
          GRANT CONNECT, RESOURCE, CTXAPP TO test
          /
          -- connect:
          CONNECT test/test
          -- create table:
          CREATE TABLE test.test_tab
            (test_col  VARCHAR2 (9))
          /
          -- insert test data:
          INSERT INTO test.test_tab (test_col)
          VALUES ('test data')
          /
          -- check that test data was inserted:
          SELECT * FROM test.test_tab
          /
          -- create index:
          CREATE INDEX test.test_idx
          ON test.test_tab (test_col)
          INDEXTYPE IS CTXSYS.CONTEXT 
          /
          -- check for errors:
          SELECT * FROM ctx_user_index_errors
          /
          -- check that index and domain index tables were created:
          COLUMN object_name FORMAT A30
          SELECT object_name, object_type
          FROM   user_objects
          WHERE  object_name LIKE '%TEST%'
          /
          -- check that tokens were created:
          SELECT token_text FROM test.dr$test_idx$i
          /
          -- test query:
          SELECT * FROM test.test_tab
          WHERE  CONTAINS (test_col, 'test data') > 0
          /
          -- example of the results you should get:
          SCOTT@orcl_11gR2> -- version:
          SCOTT@orcl_11gR2> SELECT * FROM v$version
            2  /
          
          BANNER
          --------------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
          PL/SQL Release 11.2.0.1.0 - Production
          CORE     11.2.0.1.0     Production
          TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
          NLSRTL Version 11.2.0.1.0 - Production
          
          5 rows selected.
          
          SCOTT@orcl_11gR2> -- check that ctxsys user exists:
          SCOTT@orcl_11gR2> SELECT username
            2  FROM   all_users
            3  WHERE  username = 'CTXSYS'
            4  /
          
          USERNAME
          ------------------------------
          CTXSYS
          
          1 row selected.
          
          SCOTT@orcl_11gR2> -- check that Oracle Text is installed properly:
          SCOTT@orcl_11gR2> SELECT *
            2  FROM   dba_registry
            3  WHERE  comp_id = 'CONTEXT'
            4  /
          
          COMP_ID
          ------------------------------
          COMP_NAME
          --------------------------------------------------------------------------------
          VERSION                        STATUS
          ------------------------------ --------------------------------------------
          MODIFIED                      NAMESPACE
          ----------------------------- ------------------------------
          CONTROL                        SCHEMA
          ------------------------------ ------------------------------
          PROCEDURE                                                     STARTUP
          ------------------------------------------------------------- --------
          PARENT_ID
          ------------------------------
          OTHER_SCHEMAS
          --------------------------------------------------------------------------------
          CONTEXT
          Oracle Text
          11.2.0.1.0                     VALID
          30-MAR-2010 11:06:15          SERVER
          SYS                            CTXSYS
          VALIDATE_CONTEXT
          
          
          
          
          1 row selected.
          
          SCOTT@orcl_11gR2> -- create test user:
          SCOTT@orcl_11gR2> CREATE USER test IDENTIFIED BY test
            2  /
          
          User created.
          
          SCOTT@orcl_11gR2> -- grant privileges:
          SCOTT@orcl_11gR2> GRANT CONNECT, RESOURCE, CTXAPP TO test
            2  /
          
          Grant succeeded.
          
          SCOTT@orcl_11gR2> -- connect:
          SCOTT@orcl_11gR2> CONNECT test/test
          Connected.
          TEST@orcl_11gR2> -- create table:
          TEST@orcl_11gR2> CREATE TABLE test.test_tab
            2    (test_col  VARCHAR2 (9))
            3  /
          
          Table created.
          
          TEST@orcl_11gR2> -- insert test data:
          TEST@orcl_11gR2> INSERT INTO test.test_tab (test_col)
            2  VALUES ('test data')
            3  /
          
          1 row created.
          
          TEST@orcl_11gR2> -- check that test data was inserted:
          TEST@orcl_11gR2> SELECT * FROM test.test_tab
            2  /
          
          TEST_COL
          ---------
          test data
          
          1 row selected.
          
          TEST@orcl_11gR2> -- create index:
          TEST@orcl_11gR2> CREATE INDEX test.test_idx
            2  ON test.test_tab (test_col)
            3  INDEXTYPE IS CTXSYS.CONTEXT
            4  /
          
          Index created.
          
          TEST@orcl_11gR2> -- check for errors:
          TEST@orcl_11gR2> SELECT * FROM ctx_user_index_errors
            2  /
          
          no rows selected
          
          TEST@orcl_11gR2> -- check that index and domain index tables were created:
          TEST@orcl_11gR2> COLUMN object_name FORMAT A30
          TEST@orcl_11gR2> SELECT object_name, object_type
            2  FROM   user_objects
            3  WHERE  object_name LIKE '%TEST%'
            4  /
          
          OBJECT_NAME                    OBJECT_TYPE
          ------------------------------ -------------------
          DR$TEST_IDX$I                  TABLE
          DR$TEST_IDX$K                  TABLE
          DR$TEST_IDX$N                  TABLE
          DR$TEST_IDX$R                  TABLE
          DR$TEST_IDX$X                  INDEX
          TEST_IDX                       INDEX
          TEST_TAB                       TABLE
          
          7 rows selected.
          
          TEST@orcl_11gR2> -- check that tokens were created:
          TEST@orcl_11gR2> SELECT token_text FROM test.dr$test_idx$i
            2  /
          
          TOKEN_TEXT
          ----------------------------------------------------------------
          DATA
          TEST
          
          2 rows selected.
          
          TEST@orcl_11gR2> -- test query:
          TEST@orcl_11gR2> SELECT * FROM test.test_tab
            2  WHERE  CONTAINS (test_col, 'test data') > 0
            3  /
          
          TEST_COL
          ---------
          test data
          
          1 row selected.
          
          TEST@orcl_11gR2>
          • 2. Re: Rights issue when creating context index.
            Barbara Boehmer
            Once again, I have no way of telling if what you posted is what you actually ran. You said that you tried to create a simple index without parameters. You also posted that you granted created any jobb with job misspelled with two b's. If you tried to run the full index creation with sync ... then you need the create job privilege (spelled correctly). Without it, you would get the exact error stack that you posted. Please see the simplified reproduction and solution below. Note that it is necessary to drop the partially created index before attempting creation again.
            SCOTT@orcl_11gR2> -- reproduction of problem:
            SCOTT@orcl_11gR2> CREATE USER test IDENTIFIED BY test
              2  /
            
            User created.
            
            SCOTT@orcl_11gR2> GRANT CONNECT, RESOURCE, CTXAPP TO test
              2  /
            
            Grant succeeded.
            
            SCOTT@orcl_11gR2> CONNECT test/test
            Connected.
            TEST@orcl_11gR2> CREATE TABLE test.test_tab
              2    (test_col  VARCHAR2 (9))
              3  /
            
            Table created.
            
            TEST@orcl_11gR2> CREATE INDEX test.test_idx
              2  ON test.test_tab (test_col)
              3  INDEXTYPE IS CTXSYS.CONTEXT
              4  PARAMETERS
              5    ('SYNC (EVERY "TRUNC(SYSDATE + 1) + 03/24") TRANSACTIONAL')
              6  /
            CREATE INDEX test.test_idx
            *
            ERROR at line 1:
            ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
            ORA-20000: Oracle Text error:
            DRG-50857: oracle error in drvddl.IndexCreate
            ORA-27486: insufficient privileges
            ORA-06512: at "CTXSYS.DRUE", line 160
            ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
            
            
            TEST@orcl_11gR2> -- solution:
            TEST@orcl_11gR2> CONNECT system
            Connected.
            SYSTEM@orcl_11gR2> GRANT CREATE JOB TO test
              2  /
            
            Grant succeeded.
            
            SYSTEM@orcl_11gR2> CONNECT test/test
            Connected.
            TEST@orcl_11gR2> DROP INDEX test.test_idx
              2  /
            
            Index dropped.
            
            TEST@orcl_11gR2> CREATE INDEX test.test_idx
              2  ON test.test_tab (test_col)
              3  INDEXTYPE IS CTXSYS.CONTEXT
              4  PARAMETERS
              5    ('SYNC (EVERY "TRUNC(SYSDATE + 1) + 03/24") TRANSACTIONAL')
              6  /
            
            Index created.
            
            TEST@orcl_11gR2>
            • 3. Re: Rights issue when creating context index.
              874821
              Step 1: Ensure the schema CTXSYS does not exist, if exists ignore the execution of “step 2”.

              Step 2: Add the below mentioned statement above the existing statement(also mentioned below) in the catctx.sql file which is in the $ORACLE_HOME/ctx/admin" location.

              Existing statement : Rem CTXDEF.sql - ctx default object creation

              Statement to be added: grant execute on CTX_DDL to ctxsys ;


                   In SYS user login, execute the script to create CTXSYS schema and its related objects after confirming below parameters to this script
                   ctxsys           - password of CTXSYS schema
                   SYSAUX     - default tablespace
                   TEMP           - default temp tablespace (Replace if your database default temporary tablespace is different)

              SQL>@$ORACLE_HOME/ctx/admin/catctx.sql ctxsys SYSAUX TEMP NOLOCK

              Step 3: In SYS user login execute the below script to recompile user objects
                   SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

              Step 4: In CTXSYS user login, execute the below script to create default preference for language ENGLISH
                   SQL>@$ORACLE_HOME/ctx/admin/defaults/drdefus.sql

              Step 5 : In SYS User login ,execute the below script to give grant permission

              SQL>grant ctxapp to DBusername identified by Password;
              SQL>grant select on ctxsys.dr$preference to DBusername;
              SQL>grant execute on CTX_DDL to DBusername;


              Step 5: Login as DB user and execute the below drop index script if any already exists in same name
                   SQL>DROP INDEX LEI_CATALOG_SEARCH_SFIELD

              Step 6: Login as DB user and create the below domain index
                   SQL>CREATE INDEX LEI_CATALOG_SEARCH_SFIELD ON LET_CATALOG_SEARCH (SEARCH_FIELD) INDEXTYPE IS CTXSYS.CONTEXT;

              Regards,
              Kamal.C

              Edited by: 871818 on Jul 12, 2011 10:07 PM