//
you're reading...
Administration

Object Privileges for Function Based Indexes

This is in 11.2.0.3 database.

A function based index stores values of a function or expression applied to one or more columns of a table. When you create a  function based index using a user-defined function that is owned by another schema, besides meeting all prerequisites of creating conventional indexes you will also need to have EXECUTE object privilege on the function. Oracle document does not state if this privilege should be granted directly or through a role. If the table with a function based index is only read by its owner, EXECUTE privilege on the function can be granted to the table/index owner through a role. However, if there is another database user that needs to read data from the table, the EXECUTE privilege needs to be granted to the table/index owner directly. Otherwise this third user will get “ORA-01031: insufficient privileges” error (or, as in my test case below, ORA-00904 : invalid identifier) when running SELECT statement against the table. This is a situation in Oracle OTM database, where most application tables are owned by schema owner GLOGOWNER, while most reporting functions are owned by schema owner REPORTOWNER. When OTM application runs it connects to the database through a third user. Some application queries use functions owned by REPORTOWNER, but these functions are applied to GLOGOWNER tables. Creating function based index can expedite query running time. But special privileges need to be made.

Below I will use a test case simulating OTM database to demonstrate this issue.

1)  Create three database users: indowner, funowner, appuser. Here INDOWNER is an equivalent of GLOGOWNER; FUNOWNERis  an equivalent of REPORTOWNER; and APPUSER is the database user from the application in OTM. Create a database role. Grant necessary privileges to three users and the role.

create user indowner identified by indowner;
create user funowner identified by funowner;
create user appuser identified by appuser;
create role app_role ;

grant create session, create table, create public synonym, resource to indowner;
grant create session, create table, create public synonym, resource to funowner;
grant create session to appuser;
grant app_role to indowner;
grant app_role to funowner;
grant app_role to appuser;

2) Connect as indowner, create a table, and insert one row of data into the table. Grant SELECT privilege on the table to role app_role.

conn indowner/indowner

create table t1(email_address varchar2(200));
insert into t1 values('john.smith@company.com');
commit;

create public synonym t1 for t1;
grant select on t1 to app_role;

3) Connect as funowner and create a function. Grant EXECUTE privilege on the function to the role.

conn funowner/funowner

CREATE FUNCTION get_domain (p_string VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
STRING_OUT VARCHAR2(4000);

BEGIN
STRING_OUT := SUBSTR(p_string,INSTR(p_string,'@',-1) + 1);
RETURN STRING_OUT;
END get_domain;
/
create public synonym get_domain for get_domain;
grant EXECUTE on get_domain to app_role;

4) Verify all three users can select from table t1 and function get_domain.

conn indowner/indowner
Connected.
INDOWNER> select * from t1;

EMAIL_ADDRESS
---------------------
john.smith@company.com

INDOWNER> select get_domain(email_address) from t1;

GET_DOMAIN(EMAIL_ADDRESS)
---------------------
company.com

conn funowner/funowner
Connected.
FUNOWNER> select * from t1;

EMAIL_ADDRESS
---------------------
john.smith@company.com

FUNOWNER> select get_domain(email_address) from t1;

GET_DOMAIN(EMAIL_ADDRESS)
---------------------
company.com

FUNOWNER> conn appuser/appuser
Connected.
APPUSER>  select * from t1;

EMAIL_ADDRESS
---------------------
john.smith@company.com

APPUSER> select get_domain(email_address) from t1;

GET_DOMAIN(EMAIL_ADDRESS)
---------------------
company.com

5) As indowner, create a function based index on t1.emal_address using get_domain function.

conn indowner/indowner

INDOWNER> create index ind_f_t1_email on t1(get_domain(email_address));

Index created.

6) Now both funowner and appuser cannot select from table t1, nor can they select from the function.

FUNOWNER> select * from t1
*
ERROR at line 1:
ORA-00904: : invalid identifier

FUNUSER> select get_domain(email_address) from t1;
select get_domain(email_address) from t1
*
ERROR at line 1:
ORA-00904: : invalid identifier

conn appuser/appuser
Connected.
APPUSER>  select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00904: : invalid identifier

APPUSER> select get_domain(email_address) from t1;
select get_domain(email_address) from t1
*
ERROR at line 1:
ORA-00904: : invalid identifier

The solution for this problem is granting EXECUTE privilege on the function directly to the schema owner of the function based index, indowner in this case. After the direct grant, both the schema owner of the function and the third user (application user) will be able to do SELECT on the table as well as the function without any error.

One interesting thing was observed from this test case. If the index owner indowner does a SELECT from table t1 right after creating the function based index, both FUNOWNER and APPUSER can do SELECT from table T1 without the error. But they still cannot SELECT from the function. Also INDOWNER needs to do SELECT before FUNOWNER and/or APPUSER encounters this error. Once the error happens, SELECT by INDOWNER does not help. Not sure what Oracle does internally triggering/suppressing the error. Bottom line is in OTM database if you need to create a custom index (function based) under GLOGOWNER using one of the function under REPORTOWNER, granting EXECUTE privilege on the function directly to GLOGOWNER to endure the application user does not get this error when SELECTing from the table.

Advertisements

About Hong Wang

I am an Oracle DBA, working in Oracle database since version 7.3. Worked in both application development and production support. Many experiences in real world complicate problems and database projects. This blog serves as a collections of notes I write on my database studies as well as issues I encountered/solved. Your comments are well welcome.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: