Hébergeur de fichiers indépendant

gdmmonitor ora.sql

À propos

Type de fichier
Fichier SQL de 10 Ko (text/plain)
Confidentialité
Fichier public, envoyé le 13 septembre 2017 à 14:16, depuis l'adresse IP 77.158.x.x (France)
Sécurité
Ne contient aucun Virus ou Malware connus - Dernière vérification: 3 jours
Statistiques
La présente page de téléchargement a été vue 293 fois depuis l'envoi du fichier
Page de téléchargement

Aperçu du fichier


--===================================================================================
-- ------------------------------
-- Description
-- ------------------------------
-- Database Type: Oracle
--
-- This script creates a 'gdmmonitor' role required for Classification and Assessment on the database.
--
-- Note: This script grants execution of the user-defined
--       password verification function to 'gdmmonitor' so that
--       assessment tests may evaluate password strength.
--       Make sure that the user executing this script has
--       Authority to grant execution to the function used
--       to verify password strength.
--
-- ------------------------------
-- before running this script
-- ------------------------------
-- Nothing
--
-- ------------------------------
-- after runnign this script
-- ------------------------------
-- need to assign this role to user(s) using the following command:
--    GRANT gdmmonitor to <username>;
--
-- ------------------------------
-- User/Password to use
-- ------------------------------
-- User: user defined (<username>)
-- Pass: user defined
-- Role: gdmmonitor

--===================================================================================

spool gdmmonitor-ora.log
set serveroutput on format wrapped size 1000000;
set linesize 80
set pagesize 44
clear scr

declare
	type RoleMembers is table of DBA_ROLE_PRIVS.GRANTEE%type;
	members RoleMembers;
	objectExists number;
	memberCount number;
	type Funs is table of varchar2(255);
	pwVerifyFuns Funs;
	pwVerifyFun varchar2(255);

begin

	dbms_output.put_line('>>>==========================================================>>>');
	dbms_output.put_line('>>>  Creating the GDMMONITOR role...');
	dbms_output.put_line('>>>==========================================================>>>');

	-- Check whether the role already exists
	dbms_output.put_line('==> Checking whether role: "GDMMONITOR" already exists.');
	begin
		select 1 into objectExists
		from DBA_ROLES
		where ROLE = 'GDMMONITOR';
		exception
			when NO_DATA_FOUND then
				objectExists := 0;
	end;

	-- If the role exists, preserve the users and drop the role
	if (objectExists = 1) then
		dbms_output.put_line('==> Role: "GDMMONITOR" exists.');

		-- Get the count of members for later iteration
		select count(*) into memberCount
		from DBA_ROLE_PRIVS prv
		where prv.GRANTED_ROLE = 'GDMMONITOR';

		-- If we have members, capture them for later re-adding
		if (memberCount > 0) then
		  dbms_output.put_line('==> Preserving (' || memberCount || ') gdmmonitor role members.');
		  select prv.GRANTEE bulk collect into members
		  from DBA_ROLE_PRIVS prv
		  where prv.GRANTED_ROLE = 'GDMMONITOR';
		end if;

		-- Drop the role
		dbms_output.put_line('==> Dropping role: "GDMMONITOR"');
		execute immediate 'drop role GDMMONITOR';
	end if;

	-- Create the role and grant privileges
	dbms_output.put_line('==> Creating role: "gdmmonitor".');
	execute immediate 'create role GDMMONITOR';
	dbms_output.put_line('==> Granting privileges to GDMMONITOR');
	execute immediate 'grant CONNECT to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.DBA_LIBRARIES to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.DBA_OBJECTS to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.DBA_PROFILES to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.DBA_ROLE_PRIVS to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.DBA_ROLES to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.DBA_SYS_PRIVS to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.DBA_TAB_PRIVS to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.DBA_USERS to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.PROXY_USERS to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.REGISTRY$HISTORY to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.V_$ARCHIVE_DEST to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.V_$CONTROLFILE to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.V_$DATABASE to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.V_$DATAFILE to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.V_$INSTANCE to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.V_$LOGFILE to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.V_$PARAMETER to GDMMONITOR';
	execute immediate 'grant SELECT on SYS.V_$SYSTEM_PARAMETER to GDMMONITOR';
	/* This is granted to PUBLIC by default, but customer may revoke this from PUBLIC */	
	execute immediate 'grant SELECT on SYS.USER$ to GDMMONITOR';			
	execute immediate 'grant SELECT on SYS.DBA_TABLES to GDMMONITOR';
	

	-- Grant SELECT on SYSTEM.PRODUCT_USER_PROFILE if it exists
	begin
		select 1 into objectExists
		from ALL_OBJECTS
		where OWNER = 'SYSTEM' and OBJECT_NAME = 'SQLPLUS_PRODUCT_PROFILE';
		exception
			when NO_DATA_FOUND then
				objectExists := 0;
				dbms_output.put_line('==>        ' || SQLERRM);
	end;

	if (objectExists = 1) then
		dbms_output.put_line('==> Granting SELECT on SYSTEM.SQLPLUS_PRODUCT_PROFILE');
		begin
			execute immediate 'grant SELECT on SYSTEM.SQLPLUS_PRODUCT_PROFILE to GDMMONITOR';
			exception when OTHERS then
			dbms_output.put_line('==> ERROR:   Could not grant SELECT on SYSTEM.SQLPLUS_PRODUCT_PROFILE.');
			dbms_output.put_line('==>          Please make sure the script runner has sufficient privileges.');
			dbms_output.put_line('==>          ' || SQLERRM);
		end;
	end if;


	-- Grant SELECT on SYS.DBA_USERS_WITH_DEFPWD if it exists
	begin
		select 1 into objectExists
		from ALL_OBJECTS
		where OWNER = 'SYS' and OBJECT_NAME = 'DBA_USERS_WITH_DEFPWD';
		exception
			when NO_DATA_FOUND then
				objectExists := 0;
				dbms_output.put_line('==> SYS.DBA_USERS_WITH_DEFPWD object not found ' || SQLERRM);
	end;

	if (objectExists = 1) then
		dbms_output.put_line('==> Granting SELECT on SYS.DBA_USERS_WITH_DEFPWD');
		begin
			execute immediate 'grant SELECT on SYS.DBA_USERS_WITH_DEFPWD to GDMMONITOR';
			exception when OTHERS then
			dbms_output.put_line('==> ERROR:   Could not grant SELECT on SYS.DBA_USERS_WITH_DEFPWD.');
			dbms_output.put_line('==>          Please make sure the script runner has sufficient privileges.');
			dbms_output.put_line('==>          ' || SQLERRM);
		end;
	end if;
	
	-- Grant SELECT on SYS.GV_$CELL_STATE if it exists
	begin
		select 1 into objectExists
		from ALL_OBJECTS
		where OWNER = 'SYS' and OBJECT_NAME = 'GV_$CELL_STATE';
		exception
			when NO_DATA_FOUND then
				objectExists := 0;
				dbms_output.put_line('==> SYS.GV_$CELL_STATE object not found ' || SQLERRM);
	end;

	if (objectExists = 1) then
		dbms_output.put_line('==> Granting SELECT on SYS.GV_$CELL_STATE');
		begin
			execute immediate 'grant SELECT on SYS.GV_$CELL_STATE to GDMMONITOR';
			exception when OTHERS then
			dbms_output.put_line('==> ERROR:   Could not grant SELECT on SYS.GV_$CELL_STATE.');
			dbms_output.put_line('==>          Please make sure the script runner has sufficient privileges.');
			dbms_output.put_line('==>          ' || SQLERRM);
		end;
	end if;	

	-- Grant select on GDM$PARAMETER if it exists
	begin
		select 1 into objectExists from ALL_OBJECTS where OBJECT_TYPE = 'VIEW' and OBJECT_NAME = 'GDM_$PARAMETER';
		exception when NO_DATA_FOUND then
			objectExists := 0;
	end;

	if (objectExists = 1) then
		dbms_output.put_line('==> Granting SELECT on GDM_$PARAMETER.');
		begin
			execute immediate 'grant SELECT on GDM_$PARAMETER to GDMMONITOR';
			exception when OTHERS then
				dbms_output.put_line('==> ERROR:   Could not grant SELECT on view GDM_$PARAMETER.');
				dbms_output.put_line('==>          Please make sure the script is being runs under SYS as SYSDBA.');
				dbms_output.put_line('==>          ' || SQLERRM);
		end;
	else
		begin
			execute immediate 'create or replace view GDM_$PARAMETER as select n.KSPPINM NAME, v.KSPPSTVL VALUE from X$KSPPI n, X$KSPPCV v where v.INDX = n.INDX';
			execute immediate 'grant SELECT on GDM_$PARAMETER to GDMMONITOR';
			execute immediate 'create or replace public synonym GDM$PARAMETER for GDM_$PARAMETER';
			exception when OTHERS then
			dbms_output.put_line('==> ERROR:   Could not grant SELECT on view GDM_$PARAMETER.');
			dbms_output.put_line('==>          Please make sure the script is being runs under SYS as SYSDBA.');
			dbms_output.put_line('==>          ' || SQLERRM);
		end;
	end if;

	-- Grant the user password validation function
	select LIMIT bulk collect into pwVerifyFuns
	from DBA_PROFILES
	where RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION'
	and LIMIT not in ('UNLIMITED', 'NULL', 'DEFAULT');

	-- Loop through potentially multiple functions and grant each of them
	for i in 1..pwVerifyFuns.count loop
		pwVerifyFun:=pwVerifyFuns(i);
		if (length(pwVerifyFun) > 0) then
			dbms_output.put_line('==> Granting EXECUTE on password verification function to gdmmmonitor.');
			begin
				execute immediate 'grant EXECUTE on ' || pwVerifyFun || ' TO gdmmonitor';
				exception when OTHERS then
					dbms_output.put_line('==> ERROR:   Could not grant execute on the password verify function.');
					dbms_output.put_line('==>          Please make sure the script runner has grant privileges.');
					dbms_output.put_line('==>          ' || SQLERRM);
			end;
		else
			dbms_output.put_line('==>  Password Verification Function was not found.');
		end if;
	end loop;

  -- Re-add existing members, if any
  if (memberCount > 0) then
    dbms_output.put_line('==> Restoring (' || memberCount || ') GDMMONITOR role members.');
    for i in 1..memberCount loop
      dbms_output.put_line('==>    Restoring member: ' || members(i) );
      execute immediate 'grant GDMMONITOR to ' || members(i);
    end loop;
  end if;

  dbms_output.put_line('<<<==========================================================<<<');
  dbms_output.put_line('<<<  ...Creation of the gdmmonitor role is complete!');
  dbms_output.put_line('<<<==========================================================<<<');
end;
/
spool off



Partager le fichier


Télécharger gdmmonitor-ora.sql


Télécharger gdmmonitor-ora.sql