9 Replies Latest reply: Jan 4, 2009 12:54 PM by Aman.... RSS

    create index privilege

    OracleGuy777
      I am reading

      http://www.dba-oracle.com/concepts/grant_user_privileges.htm

      and it has

      grant create index

      but I thought there was no such privilege as create index. I tried it and got:

      ERROR at line 1:
      ORA-00990: missing or invalid privilege

      So is the website wrong?

      thanks
        • 1. Re: create index privilege
          576633
          Try reading the docs first...

          http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm
          • 2. Re: create index privilege
            Satyaki_De
            satyaki>
            satyaki>grant create any index to titan;
            
            Grant succeeded.
            
            Elapsed: 00:00:01.13
            satyaki>
            Regards.

            Satyaki De.
            • 3. Re: create index privilege
              400137
              It should be GRANT CREATE <u>ANY</u> INDEX TO <i>username</i>
              OracleGuy777 wrote:

              So is the website wrong?
              You should always reference Oracle documentation first. Go to http://tahiti.oracle.com and pick the docs specific to your database version.

              As for the website you were asking about, I would say write them and ask for clarification or to correct their article.

              Cheers
              • 4. Re: create index privilege
                Aman....
                Oracleguy,
                I am not sure that what's the context of the quoted site but in Oracle docs, there is a small reference which says this,
                http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#sthref2475
                >
                Creating Indexes

                This section describes how to create indexes. To create an index in your own schema, at least one of the following conditions must be true:

                The table or cluster to be indexed is in your own schema.

                You have INDEX privilege on the table to be indexed.

                You have CREATE ANY INDEX system privilege.>
                The irony is that there is no priviledge as such that called Index priv. Once you get a create table, you automatically get create index over it. As others mentioned, there is a create any index but that's a totally different thing.
                The answer to the question you asked, there is no such priv, AFAIK.
                HTH
                Aman....
                • 5. Re: create index privilege
                  OracleGuy777
                  Slow_moe wrote:
                  Try reading the docs first...

                  http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm
                  I did, and could find no reference to it, but did on that website, which is why I asked the question
                  • 6. Re: create index privilege
                    OracleGuy777
                    many thanks aman, and happy new year

                    so you dont just answer questions in the database general forum!!!

                    thanks for clarifying, I could only find reference to "create any index" and not "create index", so I guess the website was after all wrong

                    thanks
                    • 7. Re: create index privilege
                      Solomon Yakobson
                      That is why you need to stop reading Burleson. Also, anwser you gave a star to is completely wrong:
                      Aman.... wrote:
                      Oracleguy,
                      I am not sure that what's the context of the quoted site but in Oracle docs, there is a small reference which says this,
                      http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#sthref2475
                      >
                      Creating Indexes

                      This section describes how to create indexes. To create an index in your own schema, at least one of the following conditions must be true:

                      The table or cluster to be indexed is in your own schema.

                      You have INDEX privilege on the table to be indexed.

                      You have CREATE ANY INDEX system privilege.>
                      The irony is that there is no priviledge as such that called Index priv. Once you get a create table, you automatically get create index over it. As others mentioned, there is a create any index but that's a totally different thing.
                      The answer to the question you asked, there is no such priv, AFAIK.
                      HTH
                      Aman....
                      The "irony" is where do you see CREATE in:
                      You have INDEX privilege on the table to be indexed.
                      Oracle docs are telling you users granted object privilege INDEX (and not some system privilege CREATE INDEX that exists only in Burleson's mind) on a given table can create index on that table:
                      SQL> connect u1
                      Enter password: **
                      Connected.
                      SQL> select * from scott.emp;
                      select * from scott.emp
                                          *
                      ERROR at line 1:
                      ORA-00942: table or view does not exist
                      
                      
                      SQL> create index emp_idx_u1 on scott.emp(hiredate);
                      create index emp_idx_u1 on scott.emp(hiredate)
                                                       *
                      ERROR at line 1:
                      ORA-00942: table or view does not exist
                      
                      
                      SQL> connect scott
                      Enter password: *****
                      Connected.
                      SQL> grant index on emp to u1;
                      
                      Grant succeeded.
                      
                      SQL> connect u1
                      Enter password: **
                      Connected.
                      SQL> select * from scott.emp;
                      select * from scott.emp
                                          *
                      ERROR at line 1:
                      ORA-01031: insufficient privileges
                      
                      
                      SQL> create index emp_idx_u1 on scott.emp(hiredate);
                      
                      Index created.
                      
                      SQL> select index_name from user_indexes;
                      
                      INDEX_NAME
                      ------------------------------
                      EMP_IDX_U1
                      
                      SQL> connect scott
                      Enter password: *****
                      Connected.
                      SQL> select  owner,
                        2          index_name
                        3    from  dba_indexes
                        4    where table_owner = 'SCOTT'
                        5      and table_name = 'EMP'
                        6  /
                      
                      OWNER                          INDEX_NAME
                      ------------------------------ ------------------------------
                      SCOTT                          PK_EMP
                      SCOTT                          EMP_IDX
                      SCOTT                          EMP_DEPTNO
                      SCOTT                          IDX_EMP
                      U1                             EMP_IDX_U1
                      
                      SQL> 
                      SY.
                      • 8. Re: create index privilege
                        OracleGuy777
                        Great answer, Solomon.

                        I think I know what Aman was saying, English is not his first language so sometimes what he says can be slightly ambiguous, but he is a general all-round very good person to get responses from. Just like you are in this sql section of the forum, as you cleared up any confusion that there might have been.

                        thanks
                        • 9. Re: create index privilege
                          Aman....
                          Soloman,
                          Well, I don't normally say this but I can't be wrong about it as that was the question which was asked in my SQL exam of OCP and I did prepare it pretty well. I still remember that in my 9i sql book , some where it is indeed mentioned that there is no Create Index priv and one gets it automagically when he gets a create Priv. So saying that I was completely wrong, I guess its not really right.
                          Probably , what I mentioned that there is no priv like Index over the table that didn't fit well. The object priv Index, as you mentioned, probably that's another form of saying it but , I haven't read that wording anywhere,may be I missed it.But What I did want to say was that there is just create any index and there is no create index.
                          Well, as Oracleguy mentioned(and he is correct), English is not my first language and to make things more worse, I know "very less" about oracle, so there is always a room for improvement and learning. So in that way, it was a good discussion.Happy 2009 for you!
                          @Oracleguy,
                          Haha no I am not just on Database forum but mostly there only. You see, when you know less, you shouldn't speak too much ;-). Okay, enough off-topic talk,I wish you a great , prosperous and happy 2009 buddy!
                          Aman....