Information concernant les Cookies et services Web tiers
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
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 307 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