Skip to Main Content
  • Questions

  • Giving grant role to invoker from stored procedure

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dusan.

Asked: September 12, 2018 - 12:45 pm UTC

Last updated: September 13, 2018 - 2:25 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello there,

As schema owner, I give grant execute on package to another user, who is considered as executor of the package. The package contains security part (procedure with invoker rights (authid current_user)), which checks whether the caller has proper rights. It checks whether the caller has grant to role R_OPERATOR.
Caller has grant execute on package only, role R_OPERATOR is not granted, thus caller cannot perform, is stopped by security part.
The package is delivery from third party and I do no want to change the logic there.
Solution is to grant the role R_OPERATOR to caller, but I do not want to grant this role permanently, just to keep grant execute only.
Is it possible to dynamically grant the role to caller only when running the package, otherwise keep grant execute on desired package for the caller only?

Thanks,

Dusan

and Chris said...

You can enable and disable the roles active in a session with the set role command.

create role r;
grant create session, create table, create procedure, unlimited tablespace
  to data_owner identified by data_owner;
  
grant create session, r 
  to app_user identified by app_user;
  
conn data_owner/data_owner

create table t (
  c1 int
);

insert into t values ( 1 );

create or replace function f
  return int authid current_user as
  retval int;
begin

  select count(*) 
  into   retval
  from   data_owner.t;

  return retval;
end f;
/

grant execute on f to app_user;
grant select on t to r;

conn app_user/app_user

select data_owner.f from dual;

F   
  1 

set role none;

select data_owner.f from dual;

ORA-00942: table or view does not exist

set role r;

select data_owner.f from dual;

F   
  1


So you could disable the roles in a logon trigger. Then re-enable as needed.

But this could get messy. And is easy to bypass by anyone who knows what they're doing. Luckily you're on 12c. So you can use code-based access control.

With this, you still use invoker's rights. And grant access to the function. So the target user can only run the function. Not access the underlying table like it can when you grant the role:

conn chris/chris

revoke r from app_user;
grant r to data_owner with admin option;

conn data_owner/data_owner

grant r to function f;

conn app_user/app_user

select data_owner.f from dual;

F   
  1 

select * from data_owner.t;

ORA-00942: table or view does not exist

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pl/sql store procedure

Revanth kumar kurva, September 12, 2018 - 3:03 pm UTC

Hi ,

I have same identical request . How do you perform the grant select on table which created by schema . I have a,b,c schemas . When ever these schemas creates a table . I have grant select on table to role D . Please suggest on this
Chris Saxon
September 12, 2018 - 3:11 pm UTC

That doesn't look like the same request to me?

Anyway, you have to grant each table individually. There's no "grant all on user" option.

This is one of the more popular suggestions on the database ideas forum. We've raised an enhancement request for it https://community.oracle.com/ideas/2333

Great 12c enhancement!

Dusan Valasek, September 13, 2018 - 1:22 pm UTC

Hi Chris,

Thank you for your valuable answer!
I do no go for first solution, due easy bypass (security by obscurity)
Second solution is cool, I heard about it but never tried. Nevertheless, more enhancement would be nice. In sense "Do not give grant role on app stored procedure for everybody, just for app_user or somebody else"

Anyway, in the meantime I found some solution, little modification, not to the stored procedures code, but just to one view.

Thanks,

Dusan
Chris Saxon
September 13, 2018 - 2:25 pm UTC

You still need execute privileges on the function! So only those you've granted this access can call it with code-based access control.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.