Basic procedures to give db level grants to users

Some times we need to grant a particular db user to kill sessions and for that we need to  grant alter system access which is not a best practice. So to over come we create a simple procedure to do the required actions and grant execute permissions to the user on that procedure.

Here are few examples..

Killing session procedure

CREATE OR REPLACE procedure kill_db_session
 ( v_sid number, v_serial number )  as
 v_varchar2 varchar2(100);
begin
execute immediate 'ALTER SYSTEM KILL SESSION '''   || v_sid || ',' || v_serial || ''' IMMEDIATE';
end;
/

Refresh materialized view under a different user

CREATE OR REPLACE PROCEDURE SSS.REFRESH_MV AS

BEGIN

    DBMS_MVIEW.REFRESH('xxx','C');

END REFRESH_MV;

/

Here xxx is tablename and C means complete refresh

Make sure you give grants on tables explicitly for mviews or else they will throw table not found error