catnools.sql
Rem
Rem $Header: rdbms/admin/catnools.sql /main/21 2012/12/11 23:22:49 risgupta Exp $
Rem
Rem catnools.sql
Rem
Rem Copyright (c) 2001, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem catnools.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem This script drops the OLS product and all of its LBACSYS
Rem objects from a database instance. All OLS policies will
Rem also be dropped but user tables will not have their OLS
Rem policy columns automatically dropped.
Rem
Rem NOTES
Rem Must be run as SYSDBA.
Rem
Rem MODIFIED (MM/DD/YY)
Rem risgupta 11/27/12 - Bug 14259254: remove OLS-OID status from prop$
Rem sanbhara 08/22/12 - Bug 14526500 - adding call to disable_ols.
Rem aramappa 02/19/12 - bug 13606907:remove ols_audit_trail
Rem risgupta 02/13/12 - Bug 13529466: No need to recreate sys.aud$
Rem risgupta 09/13/11 - Proj 5700 - Drop sys.OLS_AUDIT_TRAIL view
Rem mjgreave 05/10/10 - maintain delete_catalog_role privs. #9697811
Rem aramappa 04/28/10 - bug 9554753: use dbms_assert
Rem srtata 02/17/09 - remove logon trigger
Rem sarchak 04/27/08 - Bug 6925041,Creating aud$ in correct tablespace.
Rem nkgopal 01/22/08 - SYS.I_AUD1 index is dropped in this release
Rem cchui 11/24/03 - Bug 3278427.
Rem srtata 04/25/02 - remove startup trigger.
Rem shwong 10/10/01 - remove OLS from registry
Rem srtata 05/02/01 - Add error handling.
Rem gmurphy 04/13/01 - document run as SYSDBA
Rem gmurphy 04/12/01 - drop after_drop trigger first
Rem gmurphy 04/06/01 - add index to aud$ table
Rem gmurphy 04/03/01 - drop synonyms
Rem gmurphy 03/02/01 - cleanup
Rem kraghura 02/06/01 - Echo Off
Rem gmurphy 02/02/01 - Merged gmurphy_ols_2rdbms
Rem gmurphy 01/30/01 - complete rewrite of pl/sql script
Rem gmurphy 01/29/01 - move cleanup from catlbacs.sql
Rem gmurphy 01/15/01 - rename droplbacsys.sql to catnools.sql
Rem rsripada 11/13/00 - add commit at the end
Rem rsripada 10/16/00 - Created
Rem
WHENEVER SQLERROR EXIT;
BEGIN
-- Check the user who is executing this script.
if sys_context('userenv','session_user') != 'SYS' then
RAISE_APPLICATION_ERROR(-20000,
'Execute the script as user SYS as SYSDBA');
end if;
END;
/
DECLARE
l_ols_config_status VARCHAR2(4000);
BEGIN
-- disable OLS before uninstalling OLS
select value$ into l_ols_config_status from lbacsys.ols$props where NAME = 'OLS_CONFIGURED_FLAG';
IF (l_ols_config_status = '1') THEN
LBACSYS.OLS_ENFORCEMENT.disable_ols;
END IF;
END;
/
WHENEVER SQLERROR CONTINUE;
-- remove OLS via registry
EXECUTE DBMS_REGISTRY.REMOVING('OLS');
-- drop OLS database triggers.
DROP TRIGGER LBACSYS.lbac$after_drop;
DROP TRIGGER LBACSYS.lbac$after_create;
DROP TRIGGER LBACSYS.lbac$before_alter;
-- cleanup OLS by removing any roles, contexts,or synonyms
-- owned by LBACSYS.
DECLARE
CURSOR lbacroles IS
SELECT granted_role
FROM dba_role_privs
WHERE grantee = 'LBACSYS'
AND granted_role like '%_DBA'
AND admin_option = 'YES';
CURSOR lbaccontexts IS
SELECT namespace
FROM dba_context
WHERE schema = 'LBACSYS';
CURSOR lbacsynonyms IS
SELECT synonym_name
FROM dba_synonyms
WHERE table_owner = 'LBACSYS';
rolename VARCHAR2(30);
BEGIN
-- drop roles
FOR r IN lbacroles LOOP
dbms_output.put_line('Dropping role ' || r.granted_role );
BEGIN
EXECUTE IMMEDIATE 'DROP ROLE ' ||
dbms_assert.enquote_name(r.granted_role,FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to drop role ' || r.granted_role);
END;
END LOOP;
-- drop contexts
FOR c IN lbaccontexts LOOP
dbms_output.put_line('Dropping context ' || c.namespace);
BEGIN
EXECUTE IMMEDIATE 'DROP CONTEXT ' ||
dbms_assert.enquote_name(c.namespace,FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to drop context ' || c.namespace);
END;
END LOOP;
-- drop synonyms
FOR s IN lbacsynonyms LOOP
dbms_output.put_line('Dropping public synonym ' || s.synonym_name);
BEGIN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' ||
dbms_assert.enquote_name(s.synonym_name,FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to drop synonym ' || s.synonym_name);
END;
END LOOP;
END;
/
-- Bug 14259254: remove OLS-OID status from props$
EXECUTE LBACSYS.LBAC_CACHE.UPDATE_PROPS_TABLE(0, TRUE);
-- now remove lbacsys account and it's content
-- drop user cascade will also remove the OLS entry from the registry
DROP USER LBACSYS CASCADE;
DELETE FROM exppkgact$ WHERE PACKAGE = 'LBAC_UTL';
COMMIT;
-- 19c 수동 작업 내용 추가
@?/rdbms/admin/utlrp.sql
EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS;
drop package LBAC_EXP;
drop package OLS_ENFORCEMENT;
