Hi,
So you might have already used some “invoker’s right” functions. As you know when you create a procedure/package/function and grant execute on them to other users, whenever other user run those codes, they will run with your privilege. Sometimes you might need those objects to run with “invoker’s right”. that way you make sure that the code will run only if invoker user has necessary privileges. a common example is a procedure which changes a user password. if this code won’t run with invoker’s privileges, then any user might change any other user’s password so that kind of procedure is created as “authid current_user” which means invoker’s right. I will talk about this later in another post.
Now, current problem is, you want to create a function and you want this function should be run under the invoker’s privileges but some objects in that function shouldn’t be accessible by the invoker.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
drop table sales; create table sales(product_No number, amount number, quantity number, customer_no number, sales_date date); insert into sales values (1, 1, 100, 789, sysdate); insert into sales values (1, 10, 20, 456, sysdate-1); insert into sales values (2, 2, 1500, 123, sysdate-2); insert into sales values (3, 100, 2, 789, sysdate-2); insert into sales values (4, 25, 20, 123, sysdate-2); commit; create or replace function sf_get_daily_earning(p_sales_date date) return number authid current_user as x_earning number; begin Select Sum(amount*quantity) into X_Earning from Mustafa.Sales where Sales_Date >= trunc(p_sales_date) and Sales_Date < trunc(p_sales_date)+1; return X_Earning; end; / |
I have a basic SALES table with some dummy date and a function which returns a given date total earnings.
let’s create a user and give the execute privilege:
|
1 2 3 4 5 |
create user test1 identified by test1; grant create session to test1; grant execute on mustafa.sf_get_daily_earning to test1; |
Test1 user should be able to call the function:
|
1 2 3 4 5 6 |
sqlplus test1/test1 select mustafa.sf_get_daily_earning(sysdate-2) from dual; * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "MUSTAFA.SF_GET_DAILY_EARNING", line 4 |
so test1 user is able to call the function but since test1 user has no privilege on SALES table, it got an “table or view does not exists” error. To overcome the problem you can give a SELECT privilege on SALES to test1 user but this will make all the sales data accessible to test1 user and that could be security breach. I don’t want my users see my actual data, only the one that I provided using function and only if they have privilege to call it.
After 12c we got a solution for that. You can assign a ROLE to a FUNCTION which called Code Base Access Control. So a function itself could have a privilege, that way you don’t need to assign privileges to users, just to the function.
|
1 2 3 4 5 |
sqlplus mustafa/mustafa create role MyRole ; grant select on sales to MyRole; grant MyRole to Function sf_get_daily_earning; |
as you can see, I created a ROLE and assign it to a function. Please consider that you can not give direct privileges to a function, only ROLEs.
So my function has a role called MyRole and this role contains a select privilege on sales table. so anyone calling this function does not need a select on sales privileges anymore.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
sqlplus test1/test1 select mustafa.sf_get_daily_earning(sysdate-2) from dual; MUSTAFA.SF_GET_DAILY_EARNING(SYSDATE-2) --------------------------------------- 3700 select * from mustafa.sales; select * from mustafa.sales * ERROR at line 1: ORA-00942: table or view does not exist |
as you see test1 user is able to call function with it’s own privileges but can not select the table. keep your database safe.