Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

Code Base Access Control & Invoker’s Right

Mustafa, 2020-06-212020-07-14

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.

Oracle PL/SQL
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:

Oracle PL/SQL
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:

Oracle PL/SQL
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.

Oracle PL/SQL
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.

Oracle PL/SQL
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.

 

12c 18c 19c Development Security authid current_userCBACcode base access controlexampleinvoker rightprivilegerole to function

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes