Nos partenaires et nous-mêmes utilisons différentes technologies, telles que les cookies, pour personnaliser les contenus et les publicités, proposer des fonctionnalités sur les réseaux sociaux et analyser le trafic. Merci de cliquer sur le bouton ci-dessous pour donner votre accord. Vous pouvez changer d’avis et modifier vos choix à tout moment. Informations RGPD
--=================================================================================== -- ------------------------------ -- 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