CODEWITHSUNDEEP
databaseoracle-databaseindexingprivilegesoracle12c
Sammy
Sammy

Reputation: 777

Oracle Indexes EXECUTE object privilege

Update:

I asked that since if we run this statement:

select * from v$object_privilege where object_type_name = 'INDEX';

and we obtain this:

Result of the statement.

So i assume that certain users may / may not be able to execute these indexes, as a form of security.

Or am i wrong and none of this makes sense?

Kind regards for your answers!

--

Good afternoon,

I would like to know why does oracle indexes have an EXECUTE privilege, but every time we try to grant it to a Role / User, we obtain the same error:

SQL Error: ORA-04042: procedure, function, package, or package body does not exist.

The object is perfectly fine, and this error even appears when we try to set it to oracle default-created indexes.

Kind regards,

Sam

Upvotes: 3

Views: 2436

Answers (3)

Osy
Osy

Reputation: 1633

There is no PRIVILEGES (neither EXECUTE privilege) on INDEX(NOT INDEXTYPE) objects, as own DICTIONARY is saying:

select * from sys.V_$OBJECT_PRIVILEGE where object_type_name = 'INDEX';

OBJECT_TYPE_NAME      OBJECT_TYPE_ID PRIVILEGE_ID PRIVILEGE_NAME                                                 
--------------------- -------------- ------------ ---------------
INDEX                 32                       12 EXECUTE

Check OBJECT_TYPE_ID = 32 corresponding to :

select * from REPCAT$_OBJECT_TYPES where OBJECT_TYPE_ID = 32;

OBJECT_TYPE_ID OBJECT_TYPE_NAME 
-------------- -----------------
                32 INDEX TYPE

Now, INDEX TYPE is used to create DOMAINS, it means, application specific indexes, EXECUTE privilege is required because are associated to user-defined index functions and procedures than implements the index type.

This is taken from Oracle documentation, referring also to "Using Extensible Indexing"

Creating an Indextype: Example The following statement creates an indextype named position_indextype and specifies the position_between operator that is supported by the indextype and the position_im type that implements the index interface. Refer to "Using Extensible Indexing" for an extensible indexing scenario that uses this indextype:

CREATE INDEXTYPE position_indextype
   FOR position_between(NUMBER, NUMBER, NUMBER)
   USING position_im;

At the end of complete implementation, you can execute this:

SELECT last_name, salary FROM employees    WHERE
 position_between(salary, 10, 20)=1    ORDER BY salary DESC, last_name;

Complete implementation here.

Upvotes: 2

Rusty
Rusty

Reputation: 2138

EXECUTE privilege could be granted on INDEXTYPE:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#r9c1-t49

"Normal" indexes created by CREATED INDEX ... statement do not require this privilege and could not have one.

UPDATE:

1) execute privilege is required because INDEXTYPE uses OPERATOR which uses PL/SQL function. So when you create domain index you need to be able to execute that function which is used for operator.

2) Why it is displayed as INDEX but not INDEXTYPE in the system dictionary - here we can only guess. I think they decide to do that because domain INDEX is instance of an application-specific index of type (http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5012.htm#i2062403).

So standalone INDEXTYPE does not make much sense. But again, this is only my assumption.

Upvotes: 2

woo2333
woo2333

Reputation: 39

some declarations from oracle document,

To create an index in your own schema, one of the following conditions must be true:

  • The table or cluster to be indexed must be in your own schema.
  • You must have the INDEX object privilege on the table to be indexed.
  • You must have the CREATE ANY INDEX system privilege.

"To create an index in another schema, you must have the CREATE ANY INDEX system privilege. Also, the owner of the schema to contain the index must have either the UNLIMITED TABLESPACE system privilege or space quota on the tablespaces to contain the index or index partitions."

"To create a domain index in your own schema, in addition to the prerequisites for creating a conventional index, you must also have the EXECUTE object privilege on the indextype. If you are creating a domain index in another user's schema, then the index owner also must have the EXECUTE object privilege on the indextype and its underlying implementation type. Before creating a domain index, you should first define the indextype."

if you run a multi-tier-application, you can create a oracle schema for your app's repository with the db-role "create resource". With this role you can create table for persisting your data and create index on your Tables, you dont need think about "execute" privilege.

The way "create an index in another schema" can normally not be allowed by dba.

Upvotes: 0

Related Questions