gdmmonitor ora.sql
À propos du fichier
- 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: 17 heures
- Statistiques
- La présente page de téléchargement a été vue 848 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 gdmmonitor-ora.sql sur le Web et les réseaux sociaux:
Télécharger le fichier gdmmonitor-ora.sql
Télécharger gdmmonitor-ora.sql