Home » Server Options » Text & interMedia » Text Index Privileges (Oracle 10g, 10.2.0.4, Solaris)
Text Index Privileges [message #435545] Wed, 16 December 2009 11:50 Go to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hello Everybody,

I am having a very basic doubt with respect to text indexes. I am not sure whether I have missed something very obvious or is it a limitation. Unfortunately I cannot post the actual plan due to various reasons. So let me try explaining it as much in detail as I can.

We have a DB which is OLS and VPD enabled and a bunch of users who can only create data but they cannot read the data. There is another bunch of users who has the policy to read the data but cannot select the table directly. So we have packages created in the respectivev schema and execute privilege is been granted to the Schema which can read the data. Packages are created with the default rights (Authid Definer).

So let's assume Schema A and Schema B can create data but cannot see the data. Schema A has few direct select access for tables defined in Schema B. Schema C has the OLS policy to read the data from Schema A and Schema B.

Hope so far is clear.

Now comes the actual issue with text index.

When I do an explain plan a very simple query something like this directly from Schema B
select * from schema_b.emp
where contains(emp.xml_Data,'Hello World',1) > 0


I get a plan which reference the domain index (Text Index). The same table is been granted select privilege to Schema A. If I execute the same query in Schema A it is not accessing the table using the domain index(Text Index) instead it is used only in the filter condition.

I know that Schema A and Schema B cannot see the data but the bit which I struggle to understand is why Oracle is not using the domain index when it is getting used if I query the table who owns it directly.

I tried enabling the optimizer trace (10053) and I could not even find a trace of this domain index mentioned in the trace.

I tried googgling, metalink for any special privileges need to be granted for oracle to use the text index but in vain.

Hopefully somebody could throw some light on this issue.

If there is any ambiguity or if anything is not clear please do let me know. I will try to clarify it.

Once again I would like apologize for not providing the actual plan.

Regards

Raj
Re: Text Index Privileges [message #435566 is a reply to message #435545] Wed, 16 December 2009 18:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Your description is insufficient to reproduce the problem. You need to provide a reproducible test case, keeping it as simple as possible.

Re: Text Index Privileges [message #435828 is a reply to message #435566] Fri, 18 December 2009 04:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi Barbara,

Thanks for the reply. I know that I have not given enough information (i.e) scripts to reproduce the problem. I will try to do it in another few hours (painful job as the machine which has internet access don't have a db and the db machine don't have internet access). So you have to kindly bear with me with any small syntactical errors.

Kind regards

Raj
Re: Text Index Privileges [message #435888 is a reply to message #435566] Fri, 18 December 2009 10:01 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Barbara,

Please find attached the test script required to re-create the problem. Prerequisite is you need to have a DB with OLS enabled and the following policy created "TEST_POLICY" with one label and one level.

Oracle version is 10.2.0.4 with OLS enabled.

conn / as sysdba

create user test_user1 identified by password;

grant connect, resource to test_user1;

create user test_user2 identified by password;

grant connect, resource to test_user2;

conn test_user1/password

drop table test_tab;  

create table test_tab
( 
 test_sno number(4) not null,
 test_xml xmltype,
 constraint pk_test_tab primary key(test_sno)
);

create index test_xml_ti on test_tab
(
 test_xml
)
indextype is ctxsys.context;

exec dbms_stats.gather_table_stats(NULL,'TEST_TAB');

explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;

select * from table(dbms_xplan.display);

grant select on test_tab to test_user1;

conn test_user2/password

explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;

select * from table(dbms_xplan.display);

conn <sec_user_admin>/<sec_user_password>

exec sa_policy_admin.apply_table_policy('TEST_POLICY','TEST_USER1','TEST_TAB','READ_CONTROL,WRITE_CONTROL,LABEL_UPDATE',NULL,NULL);

conn test_user1/password

explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;

select * from table(dbms_xplan.display);

grant select on test_tab to test_user1;

conn test_user2/password

explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;

select * from table(dbms_xplan.display);

conn <sec_user_admin>/<sec_user_password>

exec sa_policy_admin.disable_table_policy('TEST_POLICY','TEST_USER1','TEST_TAB');

conn test_user2/password

explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;

select * from table(dbms_xplan.display);


So far my observation is if I disable the label security policy on the table oracle chooses to use the text index but when I enable the table policy it is not doing it.

Also could you please force oracle to hard parse the query everytime.

Hopefully this should reproduce the problem what I am describing. I will try to post the actual output by executing in my local pc.

If you need any further information please do let me know.

Regards

Raj

P.S : I know that I have not inserted any data at all. But even without any data I am able to get the correct plan (i.e) using the domain text index.

[Updated on: Fri, 18 December 2009 10:02]

Report message to a moderator

Re: Text Index Privileges [message #436070 is a reply to message #435888] Mon, 21 December 2009 05:09 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi Barbara,

Did you had a chance to look into this. For the policy creation if you want you could use the steps described in the followup from David Knox dated sep 26 2004.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:440419921146

Once again thanks very much for looking into this.

Regards

Raj
Re: Text Index Privileges [message #436124 is a reply to message #436070] Mon, 21 December 2009 11:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
There are some errors in your script. I have not had time to look at it further. I was hoping you might correct the errors and provide the results of a run on your system in the meanwhile.
Re: Text Index Privileges [message #436281 is a reply to message #436124] Tue, 22 December 2009 12:10 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi Barbara,

Apologies for the delay. Please find my observation. I have attached the files otherwise it will make the post very long. If you have trouble or concerns let me know I will post it in inline.

Hopefully you should be able to replicate the problem at your problem. If you need any further information please do let me know. To summarise the problem again I am not able to make oracle use the text index on a table from the schema which doesn't own the table directly.

Kind regards

Raj

[Edit: ] re-uploaded the file

[Updated on: Tue, 22 December 2009 12:18]

Report message to a moderator

Re: Text Index Privileges [message #436933 is a reply to message #436281] Tue, 29 December 2009 11:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi Barbara,

Did you had a chance to look into this. Apologies for being pushy. Also just to let you know I tried running the same test case in Oracle 11gr2 and I ended up with the same output.

Regards

Raj
Re: Text Index Privileges [message #436940 is a reply to message #436933] Tue, 29 December 2009 11:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I have been very busy and probably will not have time to get to it until at least Thursday or Friday. Even then, I may not be able to resolve your problem. It may have more to do with tuning or OLS than Text.

Re: Text Index Privileges [message #437016 is a reply to message #436940] Wed, 30 December 2009 05:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Thanks Barbara, I will try posting the same details in asktom. I guess it is not to do with the data distribution but something to do with OLS but I could not find any known issues or SR relating to this.

Once again thanks very much for your help.

Thanks

Raj
Re: Text Index Privileges [message #438291 is a reply to message #437016] Thu, 07 January 2010 20:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Raj,

I finally got around to looking at this. Unfortunately, I am not familiar with Oracle Label Security. I thought that it was something that I could enable on my existing database, but apprently not. So, I am unable to create the proper environment to reproduce the problem, in order to diagnose it. Hopefully Tom Kyte will be able to help you. Sorry.

Barbara
Re: Text Index Privileges [message #445666 is a reply to message #435545] Wed, 03 March 2010 04:53 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi,

We managed to find a workaround for the problem. The problem is around VPD (OLS is implemented internally as VPD) and the workaround for this problem is either to set the optimizer_secure_view_merge to false or grant merge any view to the schema that selects on objects which it doesn't own it directly and there is a VPD or OLS policy defined on that object

Refer metalink note : 834663.1 for further information.

Once again thanks very much for all your help.

Kind regards

Raj
Previous Topic: Wildcard Seraches Using CONTAINS
Next Topic: SUBSTRING_INDEX
Goto Forum:
  


Current Time: Thu Mar 28 18:18:27 CDT 2024