Archive for December, 2015

ACCESSIBLE BY clause versus Function Based Indexes

December 14, 2015

Oracle introduced the ACCESSIBLE BY clause in 12c to improve the security of subrograms.
Basically the scenario is following:
Let’s create a tax stored function (as hr user) which will be used by the same user in his depts subprogram.
The creator wanted to allow the execution of this function for oe user in it’s total_orders procedure.

 
CREATE OR REPLACE  FUNCTION TAX(P_AMOUNT IN NUMBER) 
RETURN NUMBER  
DETERMINISTIC
ACCESSIBLE BY (depts,oe.total_orders)
IS M  NUMBER;
BEGIN
CASE WHEN P_AMOUNT <8000 THEN  M:=0.08; ELSE  M:=0.31;  
END CASE;
RETURN P_AMOUNT*M;
END;
/
Function TAX compiled

HR user issued the necessary grant statement:

 
GRANT EXECUTE ON tax TO oe;
/

Note again, the creator can use this function in his depts procedure, but – for example – in depts2 not!

 
CREATE OR REPLACE PROCEDURE depts(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
V_max_sal number;
BEGIN
SELECT MAX(salary) INTO v_max_sal
FROM employees
where department_id=p_deptno;
dbms_output.put_line
('The tax value in department('||p_deptno||') is: '||tax(v_max_sal));
END;
/
Procedure DEPTS compiled

However, if hr user wants to create a similar procedure with different name (such as depts2), it will receive an error:

 
CREATE OR REPLACE PROCEDURE depts2(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
V_max_sal number;
BEGIN
SELECT MAX(salary) INTO v_max_sal
FROM employees
where department_id=p_deptno;
dbms_output.put_line
('The tax value in department('||p_deptno||') is: '||tax(v_max_sal));
END;
/
Error(8,1): PL/SQL: Statement ignored
Error(9,54): PLS-00904: insufficient privilege to access object TAX

This is same for oe user, who wants to use hr’s tax function in total_orders procedure:

 
CREATE OR REPLACE PROCEDURE total_orders
IS
v_total number;
BEGIN
SELECT sum(order_total) INTO v_total
FROM orders;
dbms_output.put_line('total:'||v_total||' tax of it:'||hr.tax(v_total));
END;
/
Procedure TOTAL_ORDERS compiled

When oe user wants to use hr’s tax function in total_orders2 procedure she or he will receive an Oracle error:

 
CREATE OR REPLACE PROCEDURE total_orders2
IS
v_total number;
BEGIN
SELECT sum(order_total) INTO v_total
FROM orders;
dbms_output.put_line('total:'||v_total||' tax of it:'||hr.tax(v_total));
END;
/
 PL/SQL: Statement ignored
PLS-00904: insufficient privilege to access object TAX

This information well known, no suprise.
Now hr wants to create a tax_ix function based index for tax function, because he or she wants to improve the performance
for that queries which refer to this function:

 
CREATE INDEX TAX_IX ON EMPLOYEES(TAX(SALARY));
SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object TAX

This behavior is also know from ORACLE-BASE.
(https://oracle-base.com/articles/12c/plsql-white-lists-using-the-accessible-by-clause-12cr1)
What to do?
Let’s modify the definition of the tax function removing the ACCESSIBLE BY clause

 
CREATE OR REPLACE  FUNCTION TAX(P_AMOUNT IN NUMBER) 
RETURN NUMBER  
DETERMINISTIC
--ACCESSIBLE BY (depts,oe.total_orders)
IS M  NUMBER;
BEGIN
CASE WHEN P_AMOUNT <8000 THEN  M:=0.08; ELSE  M:=0.31;  
END CASE;
RETURN P_AMOUNT*M;
END;
/
Function TAX compiled

Let’s try to create and use the index again:

 
CREATE INDEX TAX_IX ON EMPLOYEES(TAX(SALARY));
Index TAX_IX created.

The execution plan
We can see that the optimizer used our index in an INDEX RANGE SCAN operation.

 

 

 

Let’s modify our tax function allowing the ACCESSIBLE BY clause:

 
CREATE OR REPLACE  FUNCTION TAX(P_AMOUNT IN NUMBER) 
RETURN NUMBER  
DETERMINISTIC
ACCESSIBLE BY (depts,oe.total_orders)
IS M  NUMBER;
BEGIN
CASE WHEN P_AMOUNT <8000 THEN  M:=0.08; ELSE  M:=0.31;  
END CASE;
RETURN P_AMOUNT*M;
END;
/
Function TAX compiled

Let’s recompile our depts procedure:

 
CREATE OR REPLACE PROCEDURE depts(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
V_max_sal number;
BEGIN
SELECT MAX(salary) INTO v_max_sal
FROM employees
where department_id=p_deptno;
dbms_output.put_line
('The tax value in department('||p_deptno||') is: '||tax(v_max_sal));
END;
/
Procedure DEPTS compiled

Now we want to execute the depts procedure as hr:

 
EXEC depts(90)

Error report -
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object TAX
ORA-06512: at "HR.DEPTS", line 5

It seems to be that hr is underprivileged for executing this function, but
1. The owner of both subrogram is hr.
2. The tax function was allowed for depts procedure to use it.
What is the solution?
According my knowledge I can say
1. Do not create function based index for that function which has
ACCESSIBLE BY clause
2. If you need the function based index, please do not specify ACCESSIBLE BY clause.
Any solution or workaround are appreciated.
Many thanks.
Laszlo
laszlo.czinkoczki@webvalto.hu

Advertisements