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.
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:
Test1 user should be able to call the function:
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.
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.
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.