quinta-feira, 26 de abril de 2018

Escondendo Colunas no Oracle

Suponha que uma tabela RH.USUARIOS tenha uma coluna SENHA que, por qualquer motivo, não pode transformada num hash. É mais seguro delimitar quem pode ver os dados dessa coluna.

O primeiro passo é criar uma função guardiã para definir as situações em que os dados podem ser vistos. A função abaixo permite apenas que as consultas feitas a partir dos esquemas RH e XY possam ver o que há na coluna. Consultas feitas a partir de outros esquemas verão apenas null.

create or replace function 
hide_col( p_owner in varchar2, p_name in varchar2 ) 
return varchar2
as
begin
  if sys_context( 'userenv', 'session_user' ) in ('RH', 'XY')  then
    return null;
  else
    return '1=0';
  end if;
end;


O valor retornado pela função é usado como uma cláusula de um where, então null vai liberar o acesso. Esse mecanismo permite criar políticas mais complexas, mas neste caso apenas restringimos por esquema.

Depois, é preciso registrar a política de segurança com DBMS_RLS:

BEGIN
  DBMS_RLS.ADD_POLICY(object_schema=>'RH', object_name=>'USUARIO',
    policy_name=>'USUARIO_SENHA',
    function_schema=>'RH',
    policy_function=>'hide_col',
    sec_relevant_cols=>'SENHA',
    sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
END;


E agora as senhas estão protegidas.

sexta-feira, 6 de abril de 2018

Inflação Acumulada Com Funções Analíticas

Imagine ter uma tabela com os valores de inflação mês a mês:

create table ipca (
  data date,
  inflacao number(5,2)
);

Mas, além da inflação do mês, é preciso calcular a inflação acumulada dos últimos doze meses. Com funções analíticas, isso deve ser fácil. Entretanto, o SQL oferece sum() e count(), mas não uma função de agregação que multiplique os valores. Com um pouquinho de esperteza e logaritmos, isso pode ser superado. O segredo é somar os logaritmos e depois tirar o exponencial.

select data, inflacao, (exp(acumulada)-1)*100 from (
  select data, inflacao, 
    sum(ln(1+(inflacao/100)))
      over (order by dt_ipca desc rows between current row and 11 following)
        as acumulada
  from ipca
)

A janela de classificação é autoexplicativa: ela ordena os índices por data decrescente e olha o registro atual e os onze anteriores a ele.

Um índice 0,23% vira 1,0023 (1+0,23/100) e um índice -0,05% vira 0,9995 (1-0,05/100). Por isso, substrai-se 1 do exponencial. Se o índice for 0, o 1 o neutraliza. A soma dos logaritmos desses valores corresponde à multiplicação.

quarta-feira, 4 de abril de 2018

Limite de Conexões Simultâneas com IPTables

Eu precisava de uma maneira simples de limitar o número de conexões simultâneas a um servidor Apache. Há módulos para o Apache para isso, mas nenhum já instalado.

Então, resolvi experimentar algo de mais baixo nível: o iptables.

As regras para limitar a 4 conexões simultâneas o acesso às portas 80 e 443 são:

iptables -A INPUT -p tcp --syn --dport 80 \
  -m connlimit --connlimit-above 4 --connlimit-mask 32 \
  -j REJECT --reject-with tcp-reset
iptables -A INPUT -p tcp --syn --dport 443 \
  -m connlimit --connlimit-above 4 --connlimit-mask 32 \
  -j REJECT --reject-with tcp-reset
Mas eu precisava excluir uma faixa de IPs dessa limitação, então precedi aquelas regras com estas:

iptables -A INPUT -s 10.128.0.0/16 -p tcp --syn --dport 80 \
  -m connlimit --connlimit-above 4 --connlimit-mask 32 \
  -j REJECT --reject-with tcp-reset
iptables -A INPUT -s 10.128.0.0/16 -p tcp --syn --dport 443 \
  -m connlimit --connlimit-above 4 --connlimit-mask 32 \
  -j REJECT --reject-with tcp-reset
A lista das regras ficou assim:

# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source        destination
ACCEPT     tcp  --  10.128.0.0/16 anywhere    tcp dpt:http flags:FIN,SYN,RST,ACK/SYN #conn/32 > 4
ACCEPT     tcp  --  10.128.0.0/16 anywhere    tcp dpt:https flags:FIN,SYN,RST,ACK/SYN #conn/32 > 4
REJECT     tcp  --  anywhere      anywhere    tcp dpt:http flags:FIN,SYN,RST,ACK/SYN #conn/32 > 4 reject-with tcp-reset
REJECT     tcp  --  anywhere      anywhere    tcp dpt:https flags:FIN,SYN,RST,ACK/SYN #conn/32 > 4 reject-with tcp-reset

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination
É importante gravar as configurações (este é o comando do CentOS):

# service iptables save
Saving firewall rules to /etc/sysconfig/iptables:          [  OK  ]
E posso verificar quantas conexões estão abertas assim:

netstat -anpt  | grep httpd  | awk '{print $5}' | \
  cut -d: -f4 | sort | uniq -c | sort -nr
Este comando filtra a lista de conexões abertas ao httpd, recorta o ip, conta o número de entradas de cada um e mostra o resultado ordenado por ordem decrescente.

quinta-feira, 1 de março de 2018

Bayes para Totós

Não achei nenhum texto satisfatório sobre Bayes, então resolvi fazer as contas para entender. Escrevi os resultados para a eventualidade de ser útil para alguém.

Comecei com o problema dos potes com biscoitos:
  1. O pote P1 tem 30 biscoitos de baunilha (b) e 10 de chocolate (c);
  2. O pote P2 tem 20 biscoitos de baunilha e 20 de chocolate.
Se eu tirar um biscoito de baunilha, qual a probabilidade de que seja do pote 1?

Bayes nos diz que P(P1|b)=[P(P1)P(b|P1)]/P(b).

Fiz todas as contas possíveis para entender o que isso quer dizer e cheguei a esses passos:
  1. P(P1) é a probabilidade de que um biscoito venha do pote 1 (50%, porque os potes têm 40 biscoitos cada um);
  2. P(b|P1) é a probabilidade de um biscoito de baunilha no pote P1 (75%, ou 30/40);
  3. Multiplicando P(P1)P(b|P1) temos a quantidade de biscoitos de baunilha vindos de P1. Temos 80 biscoitos e 30 deles são de baunilha e estão no pote P1. Isto é, 30/80 = 0,5*0,75.
  4. Dividindo esse valor por P(b), temos a probabilidade desses 30 biscoitos de baunilha em relação ao total de biscoitos de baunilha (50/80). Então, 30/80 divididos por 50/80 nos dá 0,6.
Então, a probabilidade de que um biscoito de baunilha venha do pote P1 é 60%.
O próximo exemplo é o do teste de câncer:
  1. 1% das pessoas têm câncer (99% não têm);
  2. 80% dos exames detectam a doença (20% falham);
  3. 10% dos exames são falsos positivos (as pessoas não têm a doença, mas o exame dá positivo).
Então, se o exame deu positivo para uma pessoa qualquer, qual a probabilidade de que ela realmente tenha a doença?

Bayes nos dirá que P(D|P)=[P(D)P(P|D)]/P(P), mas eu acho mais divertido pegar 1000 pessoas e metê-las em potes. Teremos o pote P (de positivo) e o pote N (de negativo). Nesses potes teremos as pessoas D (doentes) e as pessoas S (saudáveis, por enquanto).

Se aplicarmos o teste em todo mundo, vamos ter no pote P oito pessoas doentes (o teste identificou 80% dos casos) e 99 falsos positivos (dos 990 saudáveis, 10% foram falsamente classificados como doentes). O pote N terá os dois doentes exonerados pelo exame e os 891 que viverão um pouco mais.

Então, o pote P tem 107 pessoas (8 D e 99 S) e o pote N tem 893 (2 D e 891 S). Para alívio geral, a soma é 1000.

Então, voltando ao problema, se alguém tirar um exame positivo, qual a probabilidade de que esteja realmente doente? No fundo, o que buscamos é saber quantos estão realmente doentes no pote P. Será 8/(99+8), ou ~7,4%. Mas, para apaziguar Bayes, a conta seria:

P(D|P)=[P(D)P(P|D)]/P(P)=[0,01*0,8]/(107/1000)=8/107
Ou seja, um há grupo de 0,8% de pessoas que estão dentro de P e que estão doentes (0,01 * 0,8) e essas 8 pessoas representam 7,4% das pessoas cujo exame saiu positivo.

E depois vem aquele problema das três máquinas que produzem peças com defeitos. Trocando as máquinas por potes, fica bem mais fácil, como já deve ter ficado claro.

sexta-feira, 16 de fevereiro de 2018

Largura Máxima de Cada Coluna num CSV

Após tentar carregar um CSV cheio de inconsistências, resolvi buscar o tamanho máximo de cada coluna usando apenas a linha de comando no Linux.

O resultado é o comando que segue:

 head -1 arquivo.csv | \
 grep -Po ';' | \
 cat -n | \
 grep -Po '\d+' | \
 xargs -I'{}' bash -c "cut -d';' -f'{}' arquivo.csv | \
 awk 'length(\$0) > max { max=length(\$0) } END { print max }'"

Os passos são:
  1. Pegar a primeira linha (o cabeçalho);
  2. Elimina todos os caracteres exceto o separadores (para contar as colunas);
  3. Numera as colunas;
  4. Elimina os separadores para deixar apenas os números das colunas;
  5. Para cada coluna, executa um comando composto que retira a enésima coluna e imprime a largura do valor mais largo.
Então, para um cabeçalho do tipo COL1;COL2;COL3, os comandos de 1 a 4 produzem o seguinte:

 % head -1 arquivo.csv | grep -Po ';' |  cat -n | grep -Po '\d+'
 1
 2
 3


Depois, o xargs vai executar os seguintes comandos:

 bash -c cut -d';' -f'1' arquivo.csv | \
   awk 'length($0) > max { max=length($0) } END { print max }'
 bash -c cut -d';' -f'2' arquivo.csv | \
   awk 'length($0) > max { max=length($0) } END { print max }'
 bash -c cut -d';' -f'3' arquivo.csv | \
   awk 'length($0) > max { max=length($0) } END { print max }'

E o resultado final será uma lista de larguras:

 10
 25
 100

Para facilitar a leitura, dá para adicionar o número da coluna com um echo bem posicionado:

 head -1 arquivo.csv | \
 grep -Po ';' | \
 cat -n | \
 grep -Po '\d+' | \
 xargs -I'{}' bash -c "echo -n '{}: '; cut -d';' -f'{}' arquivo.csv | \
 awk 'length(\$0) > max { max=length(\$0) } END { print max }'"

E o resultado sairá assim:

 1: 10
 2: 25
 3: 100

Se faltar uma coluna, basta adicionar uma ao primeiro comando:

 bash -c "echo -n ';' &&  head -1 arquivo.csv"

Ou, sendo mais prático, basta usar o número de colunas e evitar a contagem:

 seq 1 3 | \
 xargs -I'{}' bash -c "cut -d';' -f'{}' arquivo.csv  | \
   awk 'length(\$0) > max { max=length(\$0) } END { print max }'"

O cabeçalho pode gerar problemas, quando suas colunas forem maiores que os dados propriamente ditos. A solução é usar o tail para pular a primeira linha.

 seq 1 3 | \
 xargs -I'{}' bash -c "tail -n +2 arquivo.csv | \
   cut -d';' -f'{}'  | \
   awk 'length(\$0) > max { max=length(\$0) } END { print max }'"

Isso vai falhar se o arquivo tiver campos com quebra de linha. Então, uma solução mais robusta pode ser obtida com um pouco de perl.

#!/usr/bin/perl
use Text::CSV_PP;
use List::Util qw(max);

my @max=();
my $csv=Text::CSV_PP->new({sep_char=>';',auto_diag=>1,binary=>1});
open(my $fh, '<:encoding(UTF-8)', $ARGV[0]) or die "Can't read file '$file' [$!]\n";
<$fh>; #Ignore header
while (my $line = $csv->getline($fh)) {
  my @fields=@$line;
  $max[$_]=max(length($fields[$_]),$max[$_]) for 0..$#fields;
};
print "@max\n";

Esse script recebe um único parâmetro: o nome de um arquivo. Ele percorre todas as linhas, exceto a primeira (ignorando o cabeçalho).

quinta-feira, 1 de fevereiro de 2018

Visitando os Departamentos do Uruguai

Um projeto que eu gostaria de colocar em marcha um dia é o de visitar todos os departamentos do Uruguai. Então, o primeiro passo é ter uma ideia da distância a ser percorrida. Assim, posso avaliar a viabilidade e quanto tempo seria necessário.



Eu comecei com uma tabela de todas as distâncias entre as capitais departamentais. Depois, uma função que percorre recursivamente todas as possibilidades partindo de uma cidade específica. Este não é o problema do caixeiro viajante: não é preciso terminar no mesmo lugar.

Para acelerar a busca, o código corta os ramos da árvore de busca que forem maiores que a menor distância já encontrada. Então, o programa vai acelerando com o tempo. Mesmo assim, ele levou um dia inteiro para encontrar o melhor caminho a partir de Artigas.

A solução encontrada tem 1927km. Ou seja, o projeto é viável, mesmo para ser executado em uma semana.

A tabela abaixo tem todos os passos e as distâncias percorridas em cada um.

Artigas
Rivera 183
Tacuarembó 111
Melo 204
Treinta y Tres 113
Rocha 172
Maldonado 85
Minas 75
Montevideo 122
Canelones 46
Florida 52
Durazno 85
Trindad 41
San José 95
Colonia 108
Mercedes 176
Fray Bentos 31
Paysandú 110
Salto 118



#!/usr/bin/perl
use strict;
use experimental 'smartmatch';

$|=1;

my $capitals={
0=>'Artigas',
1=>'Canelones',
2=>'Colonia',
3=>'Durazno',
4=>'Florida',
5=>'Fray Bentos',
6=>'Maldonado',
7=>'Melo',
8=>'Mercedes',
9=>'Minas',
10=>'Montevideo',
11=>'Paysandú',
12=>'Rivera',
13=>'Rocha',
14=>'Salto',
15=>'San José',
16=>'Tacuarembó',
17=>'Treinta y Tres',
18=>'Trinidad'
};

my $distances=[
  [0,555,611,418,503,435,748,392,435,711,601,325,183,671,207,580,211,503,459],
  [555,0,145,137,52,268,155,378,237,131,46,332,455,220,450,47,344,282,151],
  [611,145,0,218,178,207,301,507,176,276,177,286,549,360,404,108,429,427,176],
  [418,137,218,0,85,201,298,418,170,273,183,229,318,363,348,136,207,424,41],
  [503,52,178,85,0,286,209,329,255,184,98,318,403,274,437,88,292,355,126],
  [435,268,207,201,286,0,422,622,31,395,309,110,452,483,228,220,341,546,160],
  [748,155,301,298,209,422,0,325,391,75,134,487,572,85,605,202,509,212,301],
  [392,378,507,418,329,622,325,0,590,276,387,435,262,285,428,407,204,113,460],
  [435,237,176,170,255,31,391,590,0,363,278,110,452,452,228,189,341,415,129],
  [711,131,276,273,184,395,75,276,363,0,122,463,604,132,582,182,484,164,276],
  [601,46,177,183,98,309,134,387,278,122,0,378,501,210,496,93,390,286,188],
  [325,332,286,229,318,110,487,435,110,463,378,0,342,553,118,285,231,614,190],
  [183,455,549,318,403,452,572,262,452,604,501,342,0,541,335,473,111,373,359],
  [671,220,360,363,274,483,85,285,452,132,210,553,541,0,672,272,490,172,366],
  [207,450,404,348,437,228,605,428,228,582,496,118,335,672,0,403,224,534,309],
  [580,47,108,136,88,220,202,407,189,182,93,285,473,272,403,0,353,327,95],
  [211,344,429,207,292,341,509,204,341,484,390,231,111,490,224,353,0,320,248],
  [503,282,427,424,335,546,212,113,514,164,286,614,373,172,534,327,320,0,427],
  [459,151,176,41,126,160,301,460,129,276,188,190,359,366,309,95,248,427,0]
];

sub print_path {
  my $distance=shift;
  my $marker=shift;
  my @path=@_;
  my @names=map { $capitals->{$_} } @path;
  print "$distance km $marker "; 
  print join '->', @names;
  print "\n";
}

my $limit=3_000;

sub run {
  my $capital=shift;
  my $distance=shift||0;
  my @path=@_;

  if(scalar(@path)==18) { 
    $limit=$distance if $distance<$limit;
    print_path($distance, '*', @path, $capital);
  } else {
    for my $c (0..18) {
      if (!($c~~@path) && $c!=$capital) {
        my $d=$distances->[$capital]->[$c];
        run($c, $distance+$d, @path, $capital) if ($d+$distance)<$limit;
      }  
    }
  }   
}

run(0);


A função run() recebe o índice de uma cidade e recursivamento adiciona todas as outras, uma a uma, até ter um caminho completo. Mas ela aborta qualquer caminho que ultrapasse o valor de $limit, além de atualizar $limit quando encontra um caminho menor.

sábado, 20 de janeiro de 2018

Combinações de dígitos IV

Para completar as minhas funções de combinações, só faltavam os desarranjos: aqueles embaralhamentos nos quais nenhum elemento está no seu lugar original.

Por exemplo, para o conjunto (A B), o único embaralhamento no qual os dois elementos estão forma de suas posições originais é (B A). Para o conjunto (A B C), há duas possibilidades: (B C A) e (C A B).

Escrevi uma pequena função que recebe uma função de callback e uma lista de elementos para embaralhar.

#!/usr/bin/perl
use strict;
use experimental 'smartmatch';

sub derange(&\@;$@) {
  my $callback=\&{shift @_};
  my $items=shift;
  my $count=shift||0;

  if($count>$#$items) {
    $callback->(@_);
  } else {
    my @column=@$items;
    splice @column, $count, 1;

    for my $el (@column) {
      if(!($el~~@_)) {
        derange($callback, $items, $count+1, @_, $el);
      } 
    }
  }
}

derange {print "@_\n"} @ARGV;


Minha pequena função lançou mão do operador experimental ~~ para verificar se um elemento já não foi usado. Então, a função basicamente caminha pelas permutações, usando para cada posição apenas os elementos que não estavam ali originalmente e descartando os elementos já usados no ramo atual da busca.

Uma otimização óbvia seria deixar calculados os elementos que podem aparecer em cada posição.

O operador ~~ é experimental, então é preciso declarar seu uso.

Como ele imprime um desarranjo por linha, pude testar o resultado com o wc:

$ perl desarranjo.pl A B C D E F G H I J | wc -l
1334961