Privileges required to FLUSH_DATABASE_MONITORING_INFO

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!}.

Advertisement