jump to navigation

Accessing Roles in stored PL/SQL October 22, 2009

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

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;
begin
  dbms_output.put_line('starting');
  for ses_roles_rec in get_ses_roles loop
    dbms_output.put_line(ses_roles_rec.role);
  end loop;
  dbms_output.put_line('flushing');
  dbms_stats.flush_database_monitoring_info;
  dbms_output.put_line('ending');
end;
begin
  null;
end;
/

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

ROLE
------------------------------
CONNECT
MDW_ROLE
2 rows selected.

MDW> exec test1.run_flush
starting
CONNECT
MDW_ROLE
flushing
ending

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
starting
flushing
ending

PL/SQL procedure successfully completed.

Note that the roles are no longer seen. However, the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO still executed correctly.
Why?
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.

Privileges required to FLUSH_DATABASE_MONITORING_INFO October 19, 2009

Posted by mwidlake in development, performance.
Tags: , , ,
1 comment so far

I’m doing some work at the moment on gathering object statistics and it helps me a lot to have access to the number of changed records in SYS.DBA_TAB_MODIFICATIONS. To ensure you have the latest information in this table, you need to first flush any data out of memory with DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO.  For the live system, the DBAs rather understandably {and sensibly} want all users to run with the least access privileges they need, so granting DBA role to my user is out.

I googled for the actual system privilege or privileges needed to flush_database_monitoring_info and drew a blank, so I have had to find out for myself. And being a nice guy {who am I kidding}, I am now recording the info for anyone else who is interested to find:

On 10.2.0.3, to execute DBMS_STATS.FLUSH_DATABASE_MONITORING INFO you need the ANALYZE ANY system privilege.

{Not ANALYZE ANY DICTIONARY, which would make more sense to me}

For those who like such things, here is the proof. I had to use two sessions, thus the constant displaying of system time.

-- current user privs
DWPERFDEV1> @usr_privs
enter user whos privs you wish to see> dwperf
GRANTEE              TYPE PRIVILEGE                           adm
----------------------------------------------------------------
DWPERF               SYSP CREATE JOB                          NO
DWPERF               SYSP CREATE PROCEDURE                    NO
DWPERF               SYSP CREATE PUBLIC SYNONYM               NO
DWPERF               SYSP CREATE SESSION                      NO
DWPERF               SYSP CREATE SYNONYM                      NO
DWPERF               SYSP CREATE TABLE                        NO
DWPERF               SYSP CREATE TRIGGER                      NO
DWPERF               SYSP DEBUG CONNECT SESSION               NO
DWPERF               SYSP DROP PUBLIC SYNONYM                 NO
DWPERF               SYSP EXECUTE ANY PROCEDURE               NO
DWPERF               SYSP SELECT ANY DICTIONARY               NO
DWPERF               SYSP SELECT ANY TABLE                    NO
DWPERF               ROLE CONNECT                             NO
DWPERF               OBJP SYS.DBMS_UTILITY-EXECUTE            NO
DWPERF_ROLE          SYSP ANALYZE ANY                         NO
DWPERFDEV1> @showtime
  Date       Time
--------------------------------------------------------
19-OCT-2009 13:29:16

DWPERFDEV1> 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

DEV1> @showtime
  Date       Time
------------------------------------------------
19-OCT-2009 13:29:30

DEV1> grant analyze any dictionary to dwperf;
Grant succeeded.

DWPERFDEV1> @showtime
  Date       Time
------------------------------------------
19-OCT-2009 13:29:40

DWPERFDEV1> 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

DEV1> @showtime
 Date       Time
---------------------------------------------
19-OCT-2009 13:30:46

DEV1> grant analyze any to dwperf;
Grant succeeded.

DWPERFDEV1> @showtime
  Date       Time
------------------------------------
19-OCT-2009 13:31:20

DWPERFDEV1> exec dbms_stats.flush_database_monitoring_info
PL/SQL procedure successfully completed.

-- SUCCESS!

DEV1> @showtime
  Date       Time
-------------------------------------------
19-OCT-2009 13:31:38
DEV1> revoke analyze any from dwperf
Revoke succeeded.

DWPERFDEV1> @showtime
  Date       Time
------------------------------------------------------
19-OCT-2009 13:31:57

DWPERFDEV1> 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

Of course, I’ll soon find something else that breaks due to my minimum privs before the end of the day, but it’s not easy creating more secure systems {note, I don’t say Secure, just more secure, as in less open!}.

Follow

Get every new post delivered to your Inbox.

Join 158 other followers