페이지 이력

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;