Accessing Roles in stored PL/SQL October 22, 2009
Posted by mwidlake in development, internals.Tags: data dictionary, PL/SQL, privileges
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: data dictionary, privileges, statistics, system development
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!}.
Martin Widlake's Yet Another Oracle Blog 