Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm new to Oracle Text and I am using it to support a search feature in an application I have written. The everything seems to be working correctly except I'm not happy with the fact that I had to store a bunch of things in the ctxsys schema in order to get around insufficient privilege issues. (This is with Oracle 10gR2).

Here is the SQL script I have to set up Oracle Text for my application

-- Since the CTXSYS schema was created when Oracle Text was installed
-- it does not yet have permission to select on certain tables it needs to
-- for the below procedure
grant select on dmg.table1 to CTXSYS;
grant select on dmg.table2 to CTXSYS;
grant select on dmg.table3 to CTXSYS;
grant select on dmg.table4 to CTXSYS;
grant select on dmg.table5 to CTXSYS;

create or replace procedure ctxsys.attr_indexing_procedure (
    rid  in              rowid,
    tlob in out NOCOPY   clob )
is


begin
    -- This procedure queries the above five tables to extract text and combine it into a single document placed into tlob
end;
/

begin
    ctx_ddl.create_preference('dmg.my_datastore', 'user_datastore' );

    ctx_ddl.set_attribute( 'dmg.my_datastore', 'procedure', 'CTXSYS.attr_indexing_procedure' );
end;
/

begin
    ctx_ddl.create_preference( 'dmg.my_index_lexer', 'BASIC_LEXER' );
    ctx_ddl.set_attribute( 'dmg.my_index_lexer', 'base_letter', 'YES');
end;
/

begin 
  ctx_ddl.create_preference('dmg.MY_STEM_FUZZY_PREF', 'BASIC_WORDLIST'); 
  ctx_ddl.set_attribute('dmg.MY_STEM_FUZZY_PREF','STEMMER','ENGLISH');
  ctx_ddl.set_attribute('dmg.MY_STEM_FUZZY_PREF','FUZZY_MATCH','ENGLISH');
  ctx_ddl.set_attribute('dmg.MY_STEM_FUZZY_PREF','FUZZY_SCORE','0');
  ctx_ddl.set_attribute('dmg.MY_STEM_FUZZY_PREF','FUZZY_NUMRESULTS','5000');
  ctx_ddl.set_attribute('dmg.MY_STEM_FUZZY_PREF','SUBSTRING_INDEX','TRUE');
  ctx_ddl.set_attribute('dmg.MY_STEM_FUZZY_PREF','PREFIX_INDEX','TRUE');
  ctx_ddl.set_attribute('dmg.MY_STEM_FUZZY_PREF','WILDCARD_MAXTERMS','5000');
end;
/

-- I would prefer that this index be owned by dmg but it looks like that would require the create any table privilege on dmg (because oracle text
-- will create tables in the ctxsys schema when creating the index). Therefore, ctxsys will own the text index.
create index ctxsys.ix_doi_attr on table1(column1) indextype is ctxsys.context parameters( 'datastore dmg.my_datastore lexer dmg.my_index_lexer wordlist dmg.DOI_STEM_FUZZY_PREF');

-- Schedule the index to sync every 4 hours
declare
  job_num number;
  nlsvar varchar2(4000);
  envvar raw(32);
begin
  select nls_env,misc_env into nlsvar,envvar from dba_jobs where rownum<2 and nls_env is not null and misc_env is not null;
  select max(job)+1 into job_num from dba_jobs;
  sys.dbms_ijob.submit(job=>job_num, luser=>'CTXSYS', puser=>'CTXSYS', cuser=>'CTXSYS', what=>'ctx_ddl.sync_index(''ctxsys.ix_doi_attr'');', next_date=>sysdate+1/1440,
                  interval=>'SYSDATE+240/1440', broken=>false, nlsenv=>nlsvar, env=>envvar);
  commit;
  dbms_output.put_line('job '||job_num||' has been submitted.');
end;
/

This script is intended to be run by my personal user, which has the DBA role. All the relevant tables are owned by the "DMG" schema, which has very limited privileges.

As you can see I am using a user_datastore to aggregate text from multiple tables into a single document. Also, I could only makes this work if the indexing procedure, the index itself and the dmbs_job to sync the index periodically were all owned by ctxsys, which seems like a bad idea. Preferably, I would like everything to be owned by the DMG schema.

The biggest issue I ran into was in the create index statement. It would always fail with

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 364
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.

After some digging I figured out that the create index statement was attempting to create some tables under the ctxsys schema called DR$IX_DOI_ATTR$I, DR$IX_DOI_ATTR$K,DR$IX_DOI_ATTR$N, DR$IX_DOI_ATTR$P, and DR$IX_DOI_ATTR$R. I found that the DMG schema could create the index if I gave it the CREATE ANY TABLE privilege, but that is not something I want to do in production.

How can I make everything owned by the DMG schema (i.e. minimum privileges I need to grant the DMG schema)? Is there a way for me to remove the grants on the dmg tables to ctxsys?

EDIT:

I was originally incorrect in saying that giving the DMG schema the CREATE ANY TABLE privilege works. It instead changes the error message to this:

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-01536: space quota exceeded for tablespace 'DMG'
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
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.

This is a very strange message because there are NO quotas setup anywhere on this database. I have no idea how it is hitting up against a quota limit.

share|improve this question
    
could you give an example of code how you did that? –  user26188 Jul 20 '13 at 20:35
    
@hospel It has been some time since I have thought about this problem. Could you be more specific about what code example you are looking for? If you are talking about granting dba and then revoking it I simply ran grant dba to dmg; create index ....; revoke dba from dmg; –  Eddie Jul 22 '13 at 14:48
add comment

1 Answer 1

up vote 0 down vote accepted

What I ended up doing was giving the DMG schema DBA access right before creating the index then revoking it immediately after. I was afraid that would cause the index to fail to sync but it seems to work just fine. I was able move everything into the DMG schema= and remove the initial grants to the ctxsys schema.

I'm still not clear on what specific permissions I needed to give the DMG schema, but I got around my problem.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.