-- ------------------------------------------------------------------------- --
-- --
-- Title: lib_util_b.sql --
-- Author: Steve Roach --
-- Date: 15-SEP-2006 --
-- Version: 01.00 --
-- --
-- Description: Container for all procedures and functions relating to --
-- LIB_UTIL. --
-- --
-- History: --
-- --
-- Date Ver Author Description --
-- --------- ----- ------ -------------------------------------------------- --
-- 15-SEP-06 01.00 SRR First release --
-- --
-- ------------------------------------------------------------------------- --
-- Package body definition.
CREATE OR REPLACE PACKAGE BODY lib_util
AS
-- create_synonyms
-- Creates synonyms to tables, views and sequences owned by other users,
-- excluding system objects.
PROCEDURE create_synonyms
IS
BEGIN
-- For each synonym in USER_SYNONYMS...
FOR r_sql IN
(
SELECT 'DROP SYNONYM ' || synonym_name stmt
FROM user_synonyms
WHERE table_owner IN ('CTL', 'STA', 'VAL', 'ODS')
ORDER BY synonym_name
)
LOOP
-- dbms_output.put_line('r_sql.stmt = ' || r_sql.stmt);
-- Create and run a 'DROP SYNONYM...' statement for the synonym.
EXECUTE IMMEDIATE r_sql.stmt;
END LOOP;
-- For each object in ALL_OBJECTS conforming to the following criteria:
-- Object type is one of: 'TABLE', 'VIEW', 'SEQUENCE' or 'PACKAGE'.
-- Owner is one of: 'CTL', 'STA', 'VAL' or 'ODS'.
-- Owner is not self.
-- Object name does not start with 'SYS', 'BIN$', 'WB_RT_' or 'OWB'.
-- A synonym for the object does not already exist)...
FOR r_sql IN
(
SELECT 'CREATE SYNONYM ' || ao.object_name || ' for ' || ao.owner ||
'.' || ao.object_name stmt
FROM all_objects ao
WHERE ao.object_type IN ('TABLE', 'VIEW', 'SEQUENCE', 'PACKAGE')
AND ao.owner IN ('CTL', 'STA', 'VAL', 'ODS')
AND ao.owner != USER
AND ao.object_name NOT LIKE 'SYS%'
AND ao.object_name NOT LIKE 'BIN$%'
AND ao.object_name NOT LIKE 'WB_RT_%'
AND ao.object_name NOT LIKE 'OWB%'
AND ao.object_name != 'LIB_UTIL'
AND NOT EXISTS
(
SELECT 1
FROM user_synonyms us
WHERE us.synonym_name = ao.object_name
)
ORDER BY ao.owner
,ao.object_name
)
LOOP
-- dbms_output.put_line('r_sql.stmt = ' || r_sql.stmt);
-- Create and run a 'CREATE SYNONYM...' statement for the synonym.
EXECUTE IMMEDIATE r_sql.stmt;
END LOOP;
END create_synonyms;
-- create_grants
-- Revokes and creates grants to all users for tables, views, sequences and
-- packages owned by user.
PROCEDURE create_grants
IS
l_stmt VARCHAR2(4000);
BEGIN
-- Revoke all from everyone.
-- For each object in user_objects...
FOR r_object IN
(
SELECT object_name,
object_type
FROM user_objects
WHERE object_type NOT IN ('INDEX'
,'SYNONYM'
,'PACKAGE BODY'
,'JAVA CLASS'
,'JAVA RESOURCE')
AND object_name NOT LIKE ('%$%')
AND object_name NOT LIKE ('WB%')
AND object_name NOT LIKE ('OWB_%')
AND object_name NOT IN ('LIB_UTIL')
ORDER BY object_name
)
LOOP
-- dbms_output.put_line('r_object.object_name = ' ||
-- r_object.object_name || ' (' || r_object.object_type || ')');
-- For all users...
FOR r_user IN
(
SELECT 'CTL' usr
FROM DUAL
UNION
SELECT 'STA'
FROM DUAL
UNION
SELECT 'VAL'
FROM DUAL
UNION
SELECT 'ODS'
FROM DUAL
)
LOOP
-- dbms_output.put_line('r_user.usr = ' || r_user.usr);
-- If the user isn't me...
IF r_user.usr != USER
THEN
-- Create a REVOKE statement for the object / user.
l_stmt := 'REVOKE ALL ON ' || r_object.object_name ||
' FROM ' || r_user.usr ||
' CASCADE CONSTRAINTS';
-- dbms_output.put_line('l_stmt = ' || l_stmt);
-- Run the 'REVOKE ...' statement.
EXECUTE IMMEDIATE l_stmt;
END IF;
END LOOP;
END LOOP;
-- Grant all to everyone.
-- For each object in user_objects...
FOR r_object IN
(
SELECT object_name,
object_type
FROM user_objects
WHERE object_type NOT IN ('INDEX'
,'SYNONYM'
,'PACKAGE BODY'
,'JAVA CLASS'
,'JAVA RESOURCE')
AND object_name NOT LIKE ('%$%')
AND object_name NOT LIKE ('WB%')
AND object_name NOT LIKE ('OWB_%')
AND object_name NOT IN ('LIB_UTIL')
ORDER BY object_name
)
LOOP
-- dbms_output.put_line('r_object.object_name = ' ||
-- r_object.object_name || ' (' || r_object.object_type || ')');
-- For all users...
FOR r_user IN
(
SELECT 'CTL' usr
FROM DUAL
UNION
SELECT 'STA'
FROM DUAL
UNION
SELECT 'VAL'
FROM DUAL
UNION
SELECT 'ODS'
FROM DUAL
)
LOOP
-- dbms_output.put_line('r_user.usr = ' || r_user.usr);
-- If the user isn't me...
IF r_user.usr != USER
THEN
-- Create a GRANT statement for the object / user.
l_stmt := 'GRANT ALL ON ' || r_object.object_name ||
' TO ' || r_user.usr;
-- dbms_output.put_line('l_stmt = ' || l_stmt);
-- Run the 'GRANT ...' statement.
EXECUTE IMMEDIATE l_stmt;
END IF;
END LOOP;
END LOOP;
END create_grants;
END lib_util;
/
SHOW ERRORS
|
0 Comments:
Post a Comment
<< Home