 | |
alter index (Oracle Text index) rebuild on different schema => norights
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
alter index (Oracle Text index) rebuild on different schema => norights -
01-18-2008
, 04:58 AM
#2
| |||||
| |||||
|
|
Hi, Core question: I'd like to execute this statement as "appuser": ALTER INDEX adminuser.my_oracle_text_index REBUILD; |
Quote:
|
Error message: ORA-01418: specified index does not exist How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? |
http://tahiti.oracle.com :
alter index whatever rebuild parameters ('sync');
Quote:
| More specialized question: That is how I created the index on "schema_owner_user". It is an Oracle Text index with index type CONTEXT to make it possible to search the column my_table.my_column with Oracle Text keywords "contains": CREATE INDEX schema_owner_user.my_oracle_text_index ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT; Now the "app_user" is able to search with keyword "contains" because it has the grants for schema_owner_user.my_table: GRANT SELECT ON schema_owner_user.my_table TO app_user; But "app_user" is not allowed to alter the index. |
Quote:
|
The problem could be solved if giving "app_user" the grant for altering any index as "system" user: GRANT ALTER ANY INDEX TO app_user; But I am not allowed to do that (I don't have the system password on the production machine of my customer). |
Quote:
|
I need to give the right grant as schema_owner_user to app_user. But I have no idea how the syntax could be. |
You should also always post your version of Oracle, up to (at least!)
3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is.
Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant
execute rights to the data owner. Create index set(s) as ctxsys,
lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys.
Then, create the index(es) as data owner.
Depending on your version of oracle, there are several automated methods
of rebuilding your indexes, none of which requires your application user
to have outrageous rights.
--
Regards,
Frank van Bortel
Top-posting in UseNet newsgroups is one way to shut me up
#3
| |||||
| |||||
|
|
Hi, Core question: I'd like to execute this statement as "appuser": ALTER INDEX adminuser.my_oracle_text_index REBUILD; |
Quote:
|
Error message: ORA-01418: specified index does not exist How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? |
http://tahiti.oracle.com :
alter index whatever rebuild parameters ('sync');
Quote:
| More specialized question: That is how I created the index on "schema_owner_user". It is an Oracle Text index with index type CONTEXT to make it possible to search the column my_table.my_column with Oracle Text keywords "contains": CREATE INDEX schema_owner_user.my_oracle_text_index ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT; Now the "app_user" is able to search with keyword "contains" because it has the grants for schema_owner_user.my_table: GRANT SELECT ON schema_owner_user.my_table TO app_user; But "app_user" is not allowed to alter the index. |
Quote:
|
The problem could be solved if giving "app_user" the grant for altering any index as "system" user: GRANT ALTER ANY INDEX TO app_user; But I am not allowed to do that (I don't have the system password on the production machine of my customer). |
Quote:
|
I need to give the right grant as schema_owner_user to app_user. But I have no idea how the syntax could be. |
You should also always post your version of Oracle, up to (at least!)
3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is.
Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant
execute rights to the data owner. Create index set(s) as ctxsys,
lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys.
Then, create the index(es) as data owner.
Depending on your version of oracle, there are several automated methods
of rebuilding your indexes, none of which requires your application user
to have outrageous rights.
--
Regards,
Frank van Bortel
Top-posting in UseNet newsgroups is one way to shut me up
#4
| |||||
| |||||
|
|
Hi, Core question: I'd like to execute this statement as "appuser": ALTER INDEX adminuser.my_oracle_text_index REBUILD; |
Quote:
|
Error message: ORA-01418: specified index does not exist How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? |
http://tahiti.oracle.com :
alter index whatever rebuild parameters ('sync');
Quote:
| More specialized question: That is how I created the index on "schema_owner_user". It is an Oracle Text index with index type CONTEXT to make it possible to search the column my_table.my_column with Oracle Text keywords "contains": CREATE INDEX schema_owner_user.my_oracle_text_index ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT; Now the "app_user" is able to search with keyword "contains" because it has the grants for schema_owner_user.my_table: GRANT SELECT ON schema_owner_user.my_table TO app_user; But "app_user" is not allowed to alter the index. |
Quote:
|
The problem could be solved if giving "app_user" the grant for altering any index as "system" user: GRANT ALTER ANY INDEX TO app_user; But I am not allowed to do that (I don't have the system password on the production machine of my customer). |
Quote:
|
I need to give the right grant as schema_owner_user to app_user. But I have no idea how the syntax could be. |
You should also always post your version of Oracle, up to (at least!)
3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is.
Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant
execute rights to the data owner. Create index set(s) as ctxsys,
lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys.
Then, create the index(es) as data owner.
Depending on your version of oracle, there are several automated methods
of rebuilding your indexes, none of which requires your application user
to have outrageous rights.
--
Regards,
Frank van Bortel
Top-posting in UseNet newsgroups is one way to shut me up
#5
| |||||
| |||||
|
|
Hi, Core question: I'd like to execute this statement as "appuser": ALTER INDEX adminuser.my_oracle_text_index REBUILD; |
Quote:
|
Error message: ORA-01418: specified index does not exist How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? |
http://tahiti.oracle.com :
alter index whatever rebuild parameters ('sync');
Quote:
| More specialized question: That is how I created the index on "schema_owner_user". It is an Oracle Text index with index type CONTEXT to make it possible to search the column my_table.my_column with Oracle Text keywords "contains": CREATE INDEX schema_owner_user.my_oracle_text_index ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT; Now the "app_user" is able to search with keyword "contains" because it has the grants for schema_owner_user.my_table: GRANT SELECT ON schema_owner_user.my_table TO app_user; But "app_user" is not allowed to alter the index. |
Quote:
|
The problem could be solved if giving "app_user" the grant for altering any index as "system" user: GRANT ALTER ANY INDEX TO app_user; But I am not allowed to do that (I don't have the system password on the production machine of my customer). |
Quote:
|
I need to give the right grant as schema_owner_user to app_user. But I have no idea how the syntax could be. |
You should also always post your version of Oracle, up to (at least!)
3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is.
Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant
execute rights to the data owner. Create index set(s) as ctxsys,
lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys.
Then, create the index(es) as data owner.
Depending on your version of oracle, there are several automated methods
of rebuilding your indexes, none of which requires your application user
to have outrageous rights.
--
Regards,
Frank van Bortel
Top-posting in UseNet newsgroups is one way to shut me up
#6
| ||||
| ||||
|
|
How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); |
which was obviously right as you repeated my statement. I looked up
how "grant" works but couldn't find something like:
grant alter schema_owner_user.my_oracle_text_index to app_user;
(^^^ just as an example; this is NOT correct)
Is it possible to give a user such a special right?
Quote:
|
Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. |
Thanks,
Jan.
Quote:
|
Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. |
On 18 Jan., 13:46, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
Quote:
|
Krabatz wrote: Hi, Core question: I'd like to execute this statement as "appuser": ALTER INDEX adminuser.my_oracle_text_index REBUILD; Why?!? Why not rebuild the index *as owner*?!? Error message: ORA-01418: specified index does not exist How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); More specialized question: That is how I created the index on "schema_owner_user". It is an Oracle Text index with index type CONTEXT to make it possible to search the column my_table.my_column with Oracle Text keywords "contains": CREATE INDEX schema_owner_user.my_oracle_text_index ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT; Now the "app_user" is able to search with keyword "contains" because it has the grants for schema_owner_user.my_table: GRANT SELECT ON schema_owner_user.my_table TO app_user; But "app_user" is not allowed to alter the index. No - and it shouldn't. The problem could be solved if giving "app_user" the grant for altering any index as "system" user: GRANT ALTER ANY INDEX TO app_user; But I am not allowed to do that (I don't have the system password on the production machine of my customer). Pfew! Lucky customer! I need to give the right grant as schema_owner_user to app_user. But I have no idea how the syntax could be. No, you don't. You should read up on Oracle, and forget SS2K. You should also always post your version of Oracle, up to (at least!) 3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is. Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant execute rights to the data owner. Create index set(s) as ctxsys, lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys. Then, create the index(es) as data owner. Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up- Zitierten Text ausblenden - - Zitierten Text anzeigen - |
#7
| ||||
| ||||
|
|
How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); |
which was obviously right as you repeated my statement. I looked up
how "grant" works but couldn't find something like:
grant alter schema_owner_user.my_oracle_text_index to app_user;
(^^^ just as an example; this is NOT correct)
Is it possible to give a user such a special right?
Quote:
|
Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. |
Thanks,
Jan.
Quote:
|
Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. |
On 18 Jan., 13:46, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
Quote:
|
Krabatz wrote: Hi, Core question: I'd like to execute this statement as "appuser": ALTER INDEX adminuser.my_oracle_text_index REBUILD; Why?!? Why not rebuild the index *as owner*?!? Error message: ORA-01418: specified index does not exist How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); More specialized question: That is how I created the index on "schema_owner_user". It is an Oracle Text index with index type CONTEXT to make it possible to search the column my_table.my_column with Oracle Text keywords "contains": CREATE INDEX schema_owner_user.my_oracle_text_index ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT; Now the "app_user" is able to search with keyword "contains" because it has the grants for schema_owner_user.my_table: GRANT SELECT ON schema_owner_user.my_table TO app_user; But "app_user" is not allowed to alter the index. No - and it shouldn't. The problem could be solved if giving "app_user" the grant for altering any index as "system" user: GRANT ALTER ANY INDEX TO app_user; But I am not allowed to do that (I don't have the system password on the production machine of my customer). Pfew! Lucky customer! I need to give the right grant as schema_owner_user to app_user. But I have no idea how the syntax could be. No, you don't. You should read up on Oracle, and forget SS2K. You should also always post your version of Oracle, up to (at least!) 3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is. Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant execute rights to the data owner. Create index set(s) as ctxsys, lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys. Then, create the index(es) as data owner. Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up- Zitierten Text ausblenden - - Zitierten Text anzeigen - |
#8
| ||||
| ||||
|
|
How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); |
which was obviously right as you repeated my statement. I looked up
how "grant" works but couldn't find something like:
grant alter schema_owner_user.my_oracle_text_index to app_user;
(^^^ just as an example; this is NOT correct)
Is it possible to give a user such a special right?
Quote:
|
Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. |
Thanks,
Jan.
Quote:
|
Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. |
On 18 Jan., 13:46, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
Quote:
|
Krabatz wrote: Hi, Core question: I'd like to execute this statement as "appuser": ALTER INDEX adminuser.my_oracle_text_index REBUILD; Why?!? Why not rebuild the index *as owner*?!? Error message: ORA-01418: specified index does not exist How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); More specialized question: That is how I created the index on "schema_owner_user". It is an Oracle Text index with index type CONTEXT to make it possible to search the column my_table.my_column with Oracle Text keywords "contains": CREATE INDEX schema_owner_user.my_oracle_text_index ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT; Now the "app_user" is able to search with keyword "contains" because it has the grants for schema_owner_user.my_table: GRANT SELECT ON schema_owner_user.my_table TO app_user; But "app_user" is not allowed to alter the index. No - and it shouldn't. The problem could be solved if giving "app_user" the grant for altering any index as "system" user: GRANT ALTER ANY INDEX TO app_user; But I am not allowed to do that (I don't have the system password on the production machine of my customer). Pfew! Lucky customer! I need to give the right grant as schema_owner_user to app_user. But I have no idea how the syntax could be. No, you don't. You should read up on Oracle, and forget SS2K. You should also always post your version of Oracle, up to (at least!) 3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is. Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant execute rights to the data owner. Create index set(s) as ctxsys, lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys. Then, create the index(es) as data owner. Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up- Zitierten Text ausblenden - - Zitierten Text anzeigen - |
#9
| ||||
| ||||
|
|
How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); |
which was obviously right as you repeated my statement. I looked up
how "grant" works but couldn't find something like:
grant alter schema_owner_user.my_oracle_text_index to app_user;
(^^^ just as an example; this is NOT correct)
Is it possible to give a user such a special right?
Quote:
|
Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. |
Thanks,
Jan.
Quote:
|
Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. |
On 18 Jan., 13:46, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
Quote:
|
Krabatz wrote: Hi, Core question: I'd like to execute this statement as "appuser": ALTER INDEX adminuser.my_oracle_text_index REBUILD; Why?!? Why not rebuild the index *as owner*?!? Error message: ORA-01418: specified index does not exist How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); More specialized question: That is how I created the index on "schema_owner_user". It is an Oracle Text index with index type CONTEXT to make it possible to search the column my_table.my_column with Oracle Text keywords "contains": CREATE INDEX schema_owner_user.my_oracle_text_index ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT; Now the "app_user" is able to search with keyword "contains" because it has the grants for schema_owner_user.my_table: GRANT SELECT ON schema_owner_user.my_table TO app_user; But "app_user" is not allowed to alter the index. No - and it shouldn't. The problem could be solved if giving "app_user" the grant for altering any index as "system" user: GRANT ALTER ANY INDEX TO app_user; But I am not allowed to do that (I don't have the system password on the production machine of my customer). Pfew! Lucky customer! I need to give the right grant as schema_owner_user to app_user. But I have no idea how the syntax could be. No, you don't. You should read up on Oracle, and forget SS2K. You should also always post your version of Oracle, up to (at least!) 3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is. Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant execute rights to the data owner. Create index set(s) as ctxsys, lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys. Then, create the index(es) as data owner. Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up- Zitierten Text ausblenden - - Zitierten Text anzeigen - |
#10
| ||||||
| ||||||
|
|
Hi, thanks for your quick answer! I didn't get some of your statements. So please don't bother if I have to ask again... How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); I asked here for the correct syntax for "grant", not for "alter index" which was obviously right as you repeated my statement. I looked up how "grant" works but couldn't find something like: grant alter schema_owner_user.my_oracle_text_index to app_user; (^^^ just as an example; this is NOT correct) Is it possible to give a user such a special right? |
this doesn't appear to be a special case.
Quote:
|
Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. OK, thanks. What would you recommend for Oracle 10.2.0.1.0? |
First I'd recommend patching to 10.2.0.3. Next I'd recommend perusing
the documentation for 10gR2 at http://tahiti.oracle.com.
Quote:
|
Thanks, Jan. Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. On 18 Jan., 13:46, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com wrote: Krabatz wrote: Hi, Core question: I'd like to execute this statement as "appuser": ALTER INDEX adminuser.my_oracle_text_index REBUILD; Why?!? Why not rebuild the index *as owner*?!? |
Quote:
|
Error message: ORA-01418: specified index does not exist How is the syntax to give app_user the grants for alter index of schema_owner_user.my_oracle_text_index? By using the correct syntax, which is of course online athttp://tahiti.oracle.com: alter index whatever rebuild parameters ('sync'); More specialized question: That is how I created the index on "schema_owner_user". It is an Oracle Text index with index type CONTEXT to make it possible to search the column my_table.my_column with Oracle Text keywords "contains": CREATE INDEX schema_owner_user.my_oracle_text_index ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT; Now the "app_user" is able to search with keyword "contains" because it has the grants for schema_owner_user.my_table: GRANT SELECT ON schema_owner_user.my_table TO app_user; But "app_user" is not allowed to alter the index. No - and it shouldn't. |
Quote:
|
The problem could be solved if giving "app_user" the grant for altering any index as "system" user: GRANT ALTER ANY INDEX TO app_user; |
as I see it, is your wanting to alter an index by a user account which
doesn't own it. Only the object owner should be altering its objects.
Quote:
|
But I am not allowed to do that (I don't have the system password on the production machine of my customer). Pfew! Lucky customer! I need to give the right grant as schema_owner_user to app_user. But I have no idea how the syntax could be. No, you don't. You should read up on Oracle, and forget SS2K. You should also always post your version of Oracle, up to (at least!) 3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is. Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant execute rights to the data owner. Create index set(s) as ctxsys, lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys. Then, create the index(es) as data owner. Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up- Zitierten Text ausblenden - - Zitierten Text anzeigen -- Hide quoted text - - Show quoted text - |
David Fitzjarrell
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.