jump to navigation

Accessing Roles in stored PL/SQL October 22, 2009

Posted by mwidlake in development, internals.
Tags: , ,

Whilst looking for the minimum privileges I needed to execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO {it is ANALYZE ANY, by the way} I discovered something about PL/SQL and roles that I did not know. Now, any right I had to claim expertise in PL/SQL expired at least 8 years ago but I asked some friends who ARE still professional PL/SQL experts and they did not know this either.

Privileges granted via Roles to a user are not available to stored PL/SQL created by that user, correct? This is widely known and understood. You have to grant priveleges directly to the user for them to be seen in the PL/SQL packages, functions etc.

Having found that I needed the ANALYZE ANY privilege as I mentioned above, I asked the DBA team to grant my user that privilege on Production and Staging. They did so – via a role. “it won’t work” I said “I run the code via a package, it won’t see the privilege” and proved it by running DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO via a quickly constructed demo. Except I only proved my ignorance, it worked. WHY?

If a package is created such that it is executed with invokers rights then roles are seen.Β 

This is my test script:

create or replace package test1 is
procedure run_flush;
end test1;
create or replace package test1 authid current_user is
procedure run_flush is
cursor get_ses_roles is
select role
from session_roles;
  for ses_roles_rec in get_ses_roles loop
  end loop;

I create this package as user MDW.

Now as a privileged user I create a role and grant analyze_any to the role.

MGR>create role mdw_role
Role created.
MGR>grant analyze any to mdw_role;
Grant succeeded.

I’ll just prove that user MDW cannot yet execute the monitoring procedure

MDW> exec dbms_stats.flush_database_monitoring_info
BEGIN dbms_stats.flush_database_monitoring_info; END;
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 14135
ORA-06512: at line 1

Now I grant the role

MGR>grant mdw_role to mdw
Grant succeeded.

MDW has to log out and back in again to see the role correctly. Having done this I check for the role and then try to execute the test procedure:

MDW> select * from session_roles

2 rows selected.

MDW> exec test1.run_flush

PL/SQL procedure successfully completed.

You can see that the package sees the roles and it executes the procedure successfully. So, stored PL/SQL can utilise privileges via roles if the packages is created with authid current_user, ie executors rights.

I better admit, as someone else might raise it, that this is not the best demonstration of this feature. I recreated the package with the first line set to:

create or replace package test1 is

ie the default of owners privileges. I now re-execute the call to the package:-

MDW> exec test1.run_flush

PL/SQL procedure successfully completed.

Note that the roles are no longer seen. However, the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO still executed correctly.
Well, because if you look at the package specification of DBMS_STATS you see:-

create or replace package sys.dbms_stats authid current_user is

It makes sense. It is dangerous for system-owned packages to be executing as the owner, ie SYS, as execute access to the package would allow access to SYS privileges.

Which, of course, is why my little proof script executed the flush correctly and I looked a bit silly in front of the DBA team. Oh well.



1. oakesgr - October 23, 2009

That’s a new one for me too. Thanks Martin!

2. coskan - October 26, 2009


Is username XXX or MDW . It is 01:55am so I might be missing something If I am please forgive me πŸ™‚

In codes it looks like username is XXX but in article you say it is MDW ?

mwidlake - October 26, 2009

Argh! Global edit and replace was done incorrectly. Thanks for that Coskan.
I always replace usernames and SIDs (sometimes even table names) in blogs, to avoid any chance of breaching security. Even though it is almost always from test systems, I do this. And this time I messed up. I finished the blog late at night myself.

3. coskan - October 26, 2009

As you Brits say Shit happens .

One more thing

Do you think the bold one in second sentence should be privilege instead of roles ?

“Privileges granted via Roles to a user are not available to stored PL/SQL created by that user, correct? This is widely known and understood. You have to grant roles directly to the user for them to be seen in the PL/SQL packages, functions etc.”

mwidlake - October 26, 2009

Thanks, and corrected.

4. Blogroll Report 16/10/2009-23/10/2009 « Coskan’s Approach to Oracle - November 3, 2009

[…] Martin Widlake-Accessing Roles in stored PL/SQL […]

5. KoenVW - December 3, 2009


I tried this out myself and it did not work. But I used a grant select on an object.
Is it possible that this rule is limited to system privileges only (and so not valid for object grants)?

Kind regards,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: