terça-feira, 16 de abril de 2013

Como encriptar um código PL/SQL


Durante o desenvolvimento de regras de negócio, muitas vezes a equipe de desenvolvimento precisa “encriptar” o código para protegê-lo de direitos autorais ou mesmo para evitar que outras pessoas alterem o código inadivertidamente.

Para “encriptar” códigos de PL/SQL no banco de dados, deve-se usar o utilitário “wrap”.

Os arquivos de fontes “encriptados” podem ser movidos, copiados, e executados no SQL*Plus ou por utilitários de importação e exportação (exp/imp ou datapump).

Os códigos “encriptados” não podem ser visualizados no banco de dados.

A “encriptação” pode ser realizada para:

§  FUNCTION
§  PROCEDURE
§  PACKAGE
§  PACKAGE BODY
§  TYPE
§  TYPE BODY

A sintaxe para usar o utilitário é:

wrap iname=[nome_codigo_fonte][oname=nome_codigo_encriptado]
 
Para testar o comando “wrap” vamos criar um código para teste, segue abaixo.
 
$ vi teste_wrap.sql
 
CREATE OR REPLACE PROCEDURE TESTE_DE_WRAP
IS
 
CURSOR DB_STATUS IS
select host_name,
       to_char(STARTUP_TIME, 'dd-mm-yyyy HH24:mi:ss') STARTUP,
       trunc(SYSDATE-(STARTUP_TIME)) DAY
  from sys.v_$instance;
 
BEGIN
   FOR WDB_STATUS IN DB_STATUS LOOP
    dbms_output.put_line('Nome do Servidor : ' ||WDB_STATUS.host_name);
    dbms_output.put_line('Startup          : ' ||WDB_STATUS.STARTUP); 
    dbms_output.put_line('Qdte. Dias       : ' ||WDB_STATUS.DAY) ;
   END LOOP;
END;
/
 
Depois de validar o código na compilação, vamos “encriptar” o código fonte da procedure.
 
$ wrap iname=teste_wrap.sql oname=teste_wrap.plb
 
PL/SQL Wrapper: Release 11.2.0.1.0- 64bit Production on Fri Apr 05 14:56:58 2013
Copyright (c) 1993, 2009, Oracle.  All rights reserved.
 
Processing teste_wrap.sql to teste_wrap.plb



Depois da “encriptação” o arquivo fonte “teste.wrap.sql” gerou um arquivo de saída chamado “teste_wrap.plb”.
 
Abaixo segue o conteúdo do arquivo “teste_wrap.plb”.
 
$ cat teste_wrap.plb
 
CREATE OR REPLACE PROCEDURE TESTE_DE_WRAP wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
1d1 181
EqW1Ewvh4oT9lWBsDzHJz3EW1BEwgxDI1ydGfI4CmP9eaOk1HT3pKSdxlV3clCEtOil8CnwO
mR/gzLoWR2p4DWpHiSWtL+f6+r9yoYyUgO4hrOkLs5nS2terCavmU+tg3i++eKeTF8p2q9jI
uhaJLYfCqyzn5IXFcpnohnA9PCoZp+7TUWEuQKg6dKxRMM+DdHPJQe+eSQVDvp/xxskgNgN1
5FFpnqzN1lIKXLKv8byS+Ak5bV118/GGxz6drQUvn7+NK4fpZIknPJNmZPDq/9//6Dg65YkE
gMB2exGAfFXSs8SGNv3jjzA0UCXHC+xVj3VDfGK7ILg8LP8miNePOYtjTEWOoqp1tWpGLGtL
GzU+PJ+IvZiKuZk0hxK5
/
 
Observe que o cabeçalho da procedure ficou mantido, porém todo o código ficou “encriptado”.
 
Agora vamos compilar a procedure, utilizando o arquivo “encriptado”.
 
SQL> @teste_wrap.plb
 
Procedure created.
 
A procedure foi compilada com sucesso, porém se verificarmos o conteúdo da procedure dentro do banco de dados também não poderemos ver os detalhes do código.
 
SQL> SELECT text FROM USER_SOURCE WHERE name = 'TESTE_DE_WRAP';
 
TEXT
--------------------------------------------------------------------------------
PROCEDURE TESTE_DE_WRAP wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
1d1 181
EqW1Ewvh4oT9lWBsDzHJz3EW1BEwgxDI1ydGfI4CmP9eaOk1HT3pKSdxlV3clCEtOil8CnwO
mR/gzLoWR2p4DWpHiSWtL+f6+r9yoYyUgO4hrOkLs5nS2terCavmU+tg3i++eKeTF8p2q9jI
uhaJLYfCqyzn5IXFcpnohnA9PCoZp+7TUWEuQKg6dKxRMM+DdHPJQe+eSQVDvp/xxskgNgN1
5FFpnqzN1lIKXLKv8byS+Ak5bV118/GGxz6drQUvn7+NK4fpZIknPJNmZPDq/9//6Dg65YkE
gMB2exGAfFXSs8SGNv3jjzA0UCXHC+xVj3VDfGK7ILg8LP8miNePOYtjTEWOoqp1tWpGLGtL
GzU+PJ+IvZiKuZk0hxK5
 
Agora vamos executar a procedure para verificar o funcionamento.
 
SQL> set serveroutput on
SQL> exec dbms_output.enable(1000000);
 
PL/SQL procedure successfully completed.
 
SQL> exec TESTE_DE_WRAP
 
Nome do Servidor : db_teste.ux
Startup          : 12-04-2013 02:40:42
Qdte. Dias       : 4

PL/SQL procedure successfully completed.

Algumas limitações do utilitário “wrap”.

§  O código de PL / SQL para ser “encriptado” não pode ter  variáveis ​​de substituição usando o SQL * Plus (DEFINE).
§  O código fonte “encriptado” é analisado pelo compilador de código  PL/SQL, e não pelo SQL * Plus.
§  O utilitário “wrap” remove a maioria dos comentários dos arquivos envolvidos.

Referências: Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2)



MSc. Rubens Oliveira
DBA Oracle Consultor

terça-feira, 2 de abril de 2013

Limpando a tablespace SYSAUX


Muitas vezes o DBA poderá se deparar com um tamanho exagerado da tablespace SYSAUX. Esse evento muitas vezes acontece devido ao crescimento dos objetos do AWR*.

*(Conceito)
                         AWR:  Automatic Workload Repository é um repositório integrado (na tablespace SYSAUX) que existe no banco de dados Oracle. Em intervalos regulares, o banco de dados Oracle realiza um “snapshot”  de todas as suas estatísticas vitais e informações de carga e armazena-os neste repositório.

Este artigo fornece algumas dicas para a limpeza de dados antigos do AWR para que o DBA possa ajustar a tablespace ou mesmo evitar o crescimento.

O script abaixo irá listar os objetos e seus respectivos tamanhos dentro da tablespace SYSAUX.
 
SQL> set lines 200
SQL> col occupant_name  for a20
SQL> col schema_name    for a20
SQL> col move_procedure for a35

SQL> SELECT occupant_name,
  2   round( space_usage_kbytes/1024) "Space (M)",
  3   schema_name,
  4   move_procedure
  5   FROM v$sysaux_occupants
  6   ORDER BY 1;

OCCUPANT_NAME         Space (M) SCHEMA_NAME          MOVE_PROCEDURE
-------------------- ---------- -------------------- -----------------------------------
AO                            2 SYS                  DBMS_AW.MOVE_AWMETA
AUDIT_TABLES                  0 SYS                  DBMS_AUDIT_MGMT.move_dbaudit_tables
AUTO_TASK                     0 SYS
EM                           46 SYSMAN               emd_maintenance.move_em_tblspc
EM_MONITORING_USER            2 DBSNMP
EXPRESSION_FILTER             4 EXFSYS
JOB_SCHEDULER                34 SYS
LOGMNR                        8 SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY                      1 SYSTEM               SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ORDIM                         0 ORDSYS               ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA                 0 ORDDATA              ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS              0 ORDPLUGINS           ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SC          0 SI_INFORMTN_SCHEMA   ordsys.ord_admin.move_ordim_tblspc
HEMA
PL/SCOPE                    119 SYS
SDO                           0 MDSYS                MDSYS.MOVE_SDO
SM/ADVISOR                  330 SYS
SM/AWR                     3935 SYS
SM/OPTSTAT                  278 SYS
SM/OTHER                      8 SYS
SMON_SCN_TIME                16 SYS
SQL_MANAGEMENT_BASE           2 SYS
STATSPACK                     0 PERFSTAT
STREAMS                       1 SYS
TEXT                          3 CTXSYS               DRI_MOVE_CTXSYS
TSM                           0 TSMSYS
ULTRASEARCH                   0 WKSYS                MOVE_WK
ULTRASEARCH_DEMO_USER         0 WK_TEST              MOVE_WK
WM                            3 WMSYS                DBMS_WM.move_proc
XDB                          57 XDB                  XDB.DBMS_XDB.MOVEXDB_TABLESPACE
XSAMD                         0 OLAPSYS              DBMS_AMD.Move_OLAP_Catalog
XSOQHIST                      2 SYS                  DBMS_XSOQ.OlapiMoveProc

31 rows selected.

Na relação acima, pode-se observar em destaque, que os objetos do AWR estão ocupando 3.9 GB na base de dados, ou seja, esse espaço pode ser disponibilizado para novas coletas. Mas para realizarmos a limpeza desta base de dados, o ideal é que vejamos as datas históricas das informações do AWR que estão armazenadas no banco de dados.

Para isso vamos executar o script abaixo:

SQL> col begin_interval_time for a30
SQL> col end_interval_time   for a30

SQL> SELECT snap_id, instance_number, begin_interval_time, end_interval_time
  2    FROM SYS.WRM$_SNAPSHOT
  3   WHERE snap_id = (SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
  4  UNION
  5  SELECT snap_id, INSTANCE_NUMBER, begin_interval_time, end_interval_time
  6    FROM SYS.WRM$_SNAPSHOT
  7   WHERE snap_id = (SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
  8  ORDER BY 2;

SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME        END_INTERVAL_TIME
------- --------------- -------------------------  -------------------------
 139120               1 26-FEB-13 12.10.28.427 AM  26-FEB-13 12.20.22.376 AM
 142237               1 19-MAR-13 03.40.31.694 PM  19-MAR-13 03.50.49.959 PM
 139120               2 26-FEB-13 12.10.28.445 AM  26-FEB-13 12.20.22.349 AM
 142237               2 19-MAR-13 03.40.31.675 PM  19-MAR-13 03.50.49.943 PM

Com essa consulta é possível ver o menor e o maior intervalo de informações do AWR armazenadas na base de dados. Podemos, portanto escolher um grupo intervalo para que possamos remover as informações históricas.

Para removermos as informações desejadas podemos verificar ainda os detalhes dos intervalos desejados executando a consulta abaixo:

SQL> SELECT snap_id, instance_number, begin_interval_time
  2    FROM SYS.WRM$_SNAPSHOT
  3   WHERE instance_number = 1
  4*    AND begin_interval_time like '%FEB%';

   SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME
   ------- --------------- -------------------------
    139266               1 27-FEB-13 12.30.16.908 AM
    139267               1 27-FEB-13 12.40.37.822 AM
    139288               1 27-FEB-13 04.10.35.362 AM
    139289               1 27-FEB-13 04.20.17.264 AM
    139290               1 27-FEB-13 04.30.38.435 AM
    .                    .         .               .
    .                    .         .               .
    .                    .         .               .

    139489               1 28-FEB-13 01.40.30.552 PM
    139493               1 28-FEB-13 02.20.02.346 PM
    139496               1 28-FEB-13 02.50.33.550 PM
    139498               1 28-FEB-13 03.10.21.943 PM
    139507               1 28-FEB-13 04.40.35.345 PM

Para remover as informações mais antigas, executar a package DBMS_WORKLOAD_REPOSITORY:


SQL> execute dbms_workload_repository.drop_snapshot_range( low_snap_id => 139266, high_snap_id=>139507);

PL/SQL procedure successfully completed.

Para verificar a remoção executar novamente a consulta.

 

SQL> col begin_interval_time for a30
SQL> col end_interval_time   for a30
SQL>
SQL> SELECT snap_id, instance_number, begin_interval_time, end_interval_time
  2    FROM SYS.WRM$_SNAPSHOT
  3   WHERE snap_id = (SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
   UNION
   SELECT snap_id, instance_number, begin_interval_time, end_interval_time
  6    FROM SYS.WRM$_SNAPSHOT
  7   WHERE snap_id = (SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
   ORDER BY 2;

   SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME            END_INTERVAL_TIME
---------- --------------- ------------------------------ ------------------------------
    139265               1 27-FEB-13 12.20.23.605 AM      27-FEB-13 12.30.16.908 AM
    142240               1 19-MAR-13 04.10.08.089 PM      19-MAR-13 04.20.17.728 PM
    139265               2 27-FEB-13 12.20.23.636 AM      27-FEB-13 12.30.16.611 AM

    142240               2 19-MAR-13 04.10.08.106 PM      19-MAR-13 04.20.17.745 PM 


Com isso verificamos que os dados mais antigos foram removidos da base de dados do AWR.

Referências: Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)
                   Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 (11.2)




MSc. Rubens Oliveira
DBA Oracle Consultor