prompt "Granting Access To dds_dml_security.  Please wait. "
SET pages 1000
SET heading off
accept Application_owner prompt 'Enter Oracle Username: '
grant CREATE USER TO &&Application_owner WITH admin OPTION;
grant ALTER USER TO &&Application_owner WITH admin OPTION;
grant grant ANY role TO &&Application_owner WITH admin OPTION;
CREATE role dds_dml_security identified BY joesmith123;
spool dbgrtdml.lst
SELECT 'grant execute on ' || object_name || ' to dds_dml_security;'
FROM user_objects
WHERE object_type = 'PROCEDURE'
UNION
SELECT 'grant execute on ' || object_name || ' to dds_dml_security;'
FROM user_objects
WHERE object_type = 'PACKAGE'
UNION
SELECT 'grant execute on ' || object_name || ' to dds_dml_security;'
FROM user_objects
WHERE object_type = 'FUNCTION'
UNION
SELECT 'grant select, insert, update, delete on ' || object_name || '  to dds_dml_security;'
FROM user_objects
WHERE object_type = 'TABLE'
UNION
SELECT 'grant select, insert, update, delete on ' || object_name || ' to dds_dml_security;'
FROM user_objects
WHERE object_type = 'VIEW'
UNION
SELECT 'grant all on ' || object_name || ' to dds_dml_security;'
FROM user_objects
WHERE object_type = 'SEQUENCE'
/
spool off