Skip to Main Content
  • Questions
  • How to restrict a user with DBA role from directly executing a package?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, A.

Asked: June 03, 2021 - 5:24 am UTC

Last updated: June 04, 2021 - 11:05 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello Asktom team,

Thanks for all the good work you are doing.

Can you please help us with the following questions?

A. Can we restrict or impede a user having DBA role from directly executing a particular package? This package is defined in another user's schema and can invoke third party libraries. We need to do restrict it's execution because of a security requirement imposed by the third party.

One option that we are aware about is DB vault. However, looks like this option will impact the full DB.

B. Is it possible to define something like an access control list on the package to ensure that it can only be used by specified users?

Let's say, User1 has defined lots of packages and among them there is a special package named SecurePackage1. User2, User3 and User4 execute all of the User1's packages using "Execute Any Procedure" privilege. Do we have any mechanism using which we can specifically define the users that can execute the SecurePackage1 package?

Thanks,
AB

and Chris said...

The first thing to remember is a user with DBA privileges can do basically anything in the database. Given enough time it's always possible to bypass whatever measures you put in place.

So whatever steps you put in place to limit access to the secure code, you should also:

Audit executions of the procedure in question. While this won't stop people executing the procedure in question, you will be able to track down who did.

Avoid granting DBA to users! Create custom roles which only have the privileges people need to do their job.

That said, there are steps you can take to make it harder for privileged users to execute secure code.

12.1 introduced accessor lists. These define a comma-separated list of program units that can execute the target procedure. It's then only possible to execute the procedure via one of those in the list.

So you can create a wrapper around the secure package/procedures. These can check which roles are enabled in the current session. If this includes DBA or any others you want to restrict access, raise an error to stop execution.

Here's a quick demo. My current user (Chris) has the DBA role. Despite owning the procedure and having this role, the accessible by clause prevents me calling protect_me directly.

The only way to call it is via privileged_proc. This checks session_roles to see if DBA is enabled. If it is, it raises an exception. So CHRIS & DBAU can't run protect_me. Just U - which has only create session and execute on privileged_proc - can call it:

grant dba 
  to dbau identified by dbau;
grant create session, create procedure 
  to u identified by u;

create or replace procedure protect_me 
  accessible by ( privileged_proc )
  authid current_user
as
begin
  dbms_output.put_line ( 'Executed' );
end protect_me;
/

create or replace procedure privileged_proc 
  authid current_user
as
begin
  for r in ( 
    select * from session_roles
    where  role = 'DBA'
  ) loop
    raise_application_error ( -20001, 'Stop doing this DBA!' );
  end loop;
  chris.protect_me ();
end privileged_proc;
/

exec protect_me();

ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object PROTECT_ME

exec privileged_proc();

ORA-20001: Stop doing this DBA!

grant execute on privileged_proc to u;
  
conn u/u

exec chris.protect_me ();

ORA-06550: line 1, column 7:
PLS-00201: identifier 'CHRIS.PROTECT_ME' must be declared

exec chris.privileged_proc();

Executed

conn dbau/dbau

exec chris.protect_me ();

ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object PROTECT_ME

exec chris.privileged_proc();

ORA-20001: Stop doing this DBA!

Rating

  (1 rating)

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

Comments

AB, June 04, 2021 - 8:26 am UTC

Thanks a lot for the prompt reply, Chris.

The package we are trying to protect will be very heavily used. Therefore, we kept adding additional code checks as our last option. However, looks like this is the only option we have.

The package executes with definer's rights. Therefore, I think SESSION_ROLES will not be available and we will have to recursively check DBA_ROLE_PRIVS to get all the roles.

Also, in order to support flexibility of blocking any new roles in future, we will probably have to store the blocked roles in a table. However, we will then have to protect the table as well.

I think we can check the roles once per session and store the results in a package variable.

We have planned to use database audit trail to capture executes of the package. Accessor lists will not work in our case because the package will be called from lots of places, and the number will increase with time.

Also, it would be great if you can share your thoughts on my second query as well.
Chris Saxon
June 04, 2021 - 11:05 am UTC

Yes, you need invoker's privs to see the roles granted to the caller in session_roles.

Perhaps I wasn't clear:

I was suggesting creating a second package. This calls the current package with the additional privilege checks.

The current package has an accessor list which only includes this new package. Change all code to use the new package. So you never need to change the accessor list - only the checks in the new package when necessary.

I understand there may be a fair bit of work to change the code to the new package. But if you're adding extract permission checks you've got lots to do anyway!

Accessor lists are the only way I know to restrict package execution (other than standard exec privs) - these only allow you to specify program units, not database users.

I think we can check the roles once per session and store the results in a package variable.

It's possible for users to enable/disable roles within a session. So it's possible to bypass a one-off check.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library