julho 2018 – Rodrigo Silva e Silva

Automatizando inicialização OpenVPN Client com arquivo senha

Publicado em 20 de julho de 2018 by rodrigosilvaesilva

Instalação OpenVPN

[root@localhost ~]# yum install openvpn
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.xpg.com.br
 * epel: mirror.globo.com
 * extras: centos.xpg.com.br
 * updates: centos.xpg.com.br
Resolving Dependencies
--> Running transaction check
---> Package openvpn.x86_64 0:2.4.6-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================================================
 Package                     Arch                       Version                            Repository                Size
==========================================================================================================================
Installing:
 openvpn                     x86_64                     2.4.6-1.el7                        epel                     518 k

Transaction Summary
==========================================================================================================================
Install  1 Package

Total download size: 518 k
Installed size: 1.2 M
Is this ok [y/d/N]: y
Downloading packages:
openvpn-2.4.6-1.el7.x86_64.rpm                                                                     | 518 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : openvpn-2.4.6-1.el7.x86_64                                                                             1/1
  Verifying  : openvpn-2.4.6-1.el7.x86_64                                                                             1/1

Installed:
  openvpn.x86_64 0:2.4.6-1.el7

Complete!

[root@localhost ~]# yum install openvpn

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: centos.xpg.com.br

* epel: mirror.globo.com

* extras: centos.xpg.com.br

* updates: centos.xpg.com.br

Resolving Dependencies

--> Running transaction check

---> Package openvpn.x86_64 0:2.4.6-1.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================================================

Package Arch Version Repository Size

==========================================================================================================================

Installing:

openvpn x86_64 2.4.6-1.el7 epel 518 k

Transaction Summary

==========================================================================================================================

Install 1 Package

Total download size: 518 k

Installed size: 1.2 M

Is this ok [y/d/N]: y

Downloading packages:

openvpn-2.4.6-1.el7.x86_64.rpm | 518 kB 00:00:00

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : openvpn-2.4.6-1.el7.x86_64 1/1

Verifying : openvpn-2.4.6-1.el7.x86_64 1/1

Installed:

openvpn.x86_64 0:2.4.6-1.el7

Complete!

Modelo Cert teste.conf O arquivo teste.conf deve estar no diretório /etc/openvpn

[root@localhost bkp]# cat teste.conf
dev tun
persist-tun
persist-key
cipher BF-CBC
auth SHA1
tls-client
client
resolv-retry infinite
remote destino.com.br 1194 tcp-client
auth-user-pass /etc/openvpn/senha
remote-cert-tls server
compress lzo

<ca>
-----BEGIN CERTIFICATE-----
MIIEPzCCAyegAwIBAgIBADANBgkqhkiG9w0BAQsFADBzMQswCQYDVQQGEwJCUjEL
MAkGA1UECBMCU1AxCzAJBgNVBAcTAlJQMQwwCgYDVQQKEwNDQ00xKzApBgkqhkiG
9w0BCQEWHHN1cG9ydGVAY2NtdGVjbm9sb2dpYS5jb20uYnIxDzANBgNVBAMTBkNB
LVZJUDAeFw0xODA2MjUxOTAxNTVaFw0yODA2MjIxOTAxNTVaMHMxCzAJBgNVBAYT
AkJSMQswCQYDVQQIEwJTUDELMAkGA1UEBxMCUlAxDDAKBgNVBAoTA0NDTTErMCkG
CSqGSIb3DQEJARYcc3Vwb3J0ZUBjY210ZWNub2xvZ2lhLmNvbS5icjEPMA0GA1UE
AxMGQ0EtVklQMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAyzp0fw72
XhEUx7EbUwMcpTqSSw4wcWo8k0RCSaCxYaYTcbMsrMtv+D5kzjWIMQhih9tXyJ/t
BZq6eNPKLATw7l5nAjggeSF0LOSKjRUMkCqn6G1Mp49rcec5bYyoRTQtbuUVWo6p
z3vZPQQDEwZDQS1WSVCCAQAwDAYDVR0TBAUwAwEB/zALBgNVHQ8EBAMCAQYw
DQYJKoZIhvcNAQELBQADggEBALH+uPieHUJ6mulImbI6grkLve7POGiEICE2dtWg
SmwK4MiBzFPPi9tJA7D+Q9RlY25vbG9naWEuY29tLmJy
MQ8wDQYDVQQDEwZDQS1WSVCCAQAwDAYDVR0TBAUwAwEB/zALBgNVHQ8EBAMCAQYw
DQYJKoZIhvcNAQELBQADggEBALH+uPieHUJ6mulImbI6grkLve7POGiEICE2dtWg
SmwK4MiBzFPPi9tJA7D+Q98jhpngTB4G6v5pvQcKV0ShHuS1ddoUujgRWhG2xIJz
wNcdTL3ze5WoUbvR3FwFLOv5dX259uMADWAl8oCBdwO6ZWms6Q7jPad2myAJPTPY
gWmRGrSkGIs9H+i5vAi6lJ9wJhCRoKzdcCeueN9kwmgvHtyY3NeqxiD5syLvmYSJ
EOv9UCuM+WjRsZdiiACYwtYw9MbH0BmE3rv3RTE8evzGRlJq8J6BOOqZAW+4eTmS
FL2vZjlFpxKIj+7IvYYUKfTQ657MDWCdt9ESmlz7ZfO3NnM=
-----END CERTIFICATE-----
</ca>
setenv CLIENT_CERT 0

[root@localhost bkp]# cat teste.conf

dev tun

persist-tun

persist-key

cipher BF-CBC

auth SHA1

tls-client

client

resolv-retry infinite

remote destino.com.br 1194 tcp-client

auth-user-pass /etc/openvpn/senha

remote-cert-tls server

compress lzo

<ca>

-----BEGIN CERTIFICATE-----

MIIEPzCCAyegAwIBAgIBADANBgkqhkiG9w0BAQsFADBzMQswCQYDVQQGEwJCUjEL

MAkGA1UECBMCU1AxCzAJBgNVBAcTAlJQMQwwCgYDVQQKEwNDQ00xKzApBgkqhkiG

9w0BCQEWHHN1cG9ydGVAY2NtdGVjbm9sb2dpYS5jb20uYnIxDzANBgNVBAMTBkNB

LVZJUDAeFw0xODA2MjUxOTAxNTVaFw0yODA2MjIxOTAxNTVaMHMxCzAJBgNVBAYT

AkJSMQswCQYDVQQIEwJTUDELMAkGA1UEBxMCUlAxDDAKBgNVBAoTA0NDTTErMCkG

CSqGSIb3DQEJARYcc3Vwb3J0ZUBjY210ZWNub2xvZ2lhLmNvbS5icjEPMA0GA1UE

AxMGQ0EtVklQMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAyzp0fw72

XhEUx7EbUwMcpTqSSw4wcWo8k0RCSaCxYaYTcbMsrMtv+D5kzjWIMQhih9tXyJ/t

BZq6eNPKLATw7l5nAjggeSF0LOSKjRUMkCqn6G1Mp49rcec5bYyoRTQtbuUVWo6p

z3vZPQQDEwZDQS1WSVCCAQAwDAYDVR0TBAUwAwEB/zALBgNVHQ8EBAMCAQYw

DQYJKoZIhvcNAQELBQADggEBALH+uPieHUJ6mulImbI6grkLve7POGiEICE2dtWg

SmwK4MiBzFPPi9tJA7D+Q9RlY25vbG9naWEuY29tLmJy

MQ8wDQYDVQQDEwZDQS1WSVCCAQAwDAYDVR0TBAUwAwEB/zALBgNVHQ8EBAMCAQYw

DQYJKoZIhvcNAQELBQADggEBALH+uPieHUJ6mulImbI6grkLve7POGiEICE2dtWg

SmwK4MiBzFPPi9tJA7D+Q98jhpngTB4G6v5pvQcKV0ShHuS1ddoUujgRWhG2xIJz

wNcdTL3ze5WoUbvR3FwFLOv5dX259uMADWAl8oCBdwO6ZWms6Q7jPad2myAJPTPY

gWmRGrSkGIs9H+i5vAi6lJ9wJhCRoKzdcCeueN9kwmgvHtyY3NeqxiD5syLvmYSJ

EOv9UCuM+WjRsZdiiACYwtYw9MbH0BmE3rv3RTE8evzGRlJq8J6BOOqZAW+4eTmS

FL2vZjlFpxKIj+7IvYYUKfTQ657MDWCdt9ESmlz7ZfO3NnM=

-----END CERTIFICATE-----

</ca>

setenv CLIENT_CERT 0

Arquivo de senha diretório /etc/openvpn/senha Dentro do arquivo de senha deve conter

usuario
senha

1 2	usuario senha

Iniciar serviço openvpn teste.conf

[root@localhost openvpn]# systemctl start openvpn@teste

1	[root@localhost openvpn]# systemctl start openvpn@teste

Verificando o status

[root@localhost openvpn]# systemctl status openvpn@teste
● openvpn@teste.service - OpenVPN Robust And Highly Flexible Tunneling Application On teste1
   Loaded: loaded (/usr/lib/systemd/system/openvpn@.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2018-07-20 17:13:16 -03; 7s ago
 Main PID: 984 (openvpn)
   Status: "Initialization Sequence Completed"
   CGroup: /system.slice/system-openvpn.slice/openvpn@teste1.service
           └─984 /usr/sbin/openvpn --cd /etc/openvpn/ --config teste1.conf

Jul 20 17:13:17 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:17 2018 TCP connection established with [AF_IN...1684
Jul 20 17:13:17 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:17 2018 TCP_CLIENT link local: (not bound)
Jul 20 17:13:17 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:17 2018 TCP_CLIENT link remote: [AF_INET]200.2...1684
Jul 20 17:13:17 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:17 2018 WARNING: this configuration may cache ...this
Jul 20 17:13:18 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:18 2018 [Server-VIP] Peer Connection Initiated...1684
Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 TUN/TAP device tun0 opened
Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 do_ifconfig, tt->did_ifconfig_ipv6_setup=0
Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 /sbin/ip link set dev tun0 up mtu 1500
Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 /sbin/ip addr add dev tun0 172.19.77.2....255
Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 Initialization Sequence Completed
Hint: Some lines were ellipsized, use -l to show in full.

[root@localhost openvpn]# systemctl status openvpn@teste

● openvpn@teste.service - OpenVPN Robust And Highly Flexible Tunneling Application On teste1

Loaded: loaded (/usr/lib/systemd/system/openvpn@.service; enabled; vendor preset: disabled)

Active: active (running) since Fri 2018-07-20 17:13:16 -03; 7s ago

Main PID: 984 (openvpn)

Status: "Initialization Sequence Completed"

CGroup: /system.slice/system-openvpn.slice/openvpn@teste1.service

└─984 /usr/sbin/openvpn --cd /etc/openvpn/ --config teste1.conf

Jul 20 17:13:17 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:17 2018 TCP connection established with [AF_IN...1684

Jul 20 17:13:17 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:17 2018 TCP_CLIENT link local: (not bound)

Jul 20 17:13:17 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:17 2018 TCP_CLIENT link remote: [AF_INET]200.2...1684

Jul 20 17:13:17 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:17 2018 WARNING: this configuration may cache ...this

Jul 20 17:13:18 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:18 2018 [Server-VIP] Peer Connection Initiated...1684

Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 TUN/TAP device tun0 opened

Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 do_ifconfig, tt->did_ifconfig_ipv6_setup=0

Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 /sbin/ip link set dev tun0 up mtu 1500

Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 /sbin/ip addr add dev tun0 172.19.77.2....255

Jul 20 17:13:19 localhost.localdomain openvpn[984]: Fri Jul 20 17:13:19 2018 Initialization Sequence Completed

Hint: Some lines were ellipsized, use -l to show in full.

Verificando tunnel ativo

[root@localhost openvpn]# ifconfig tun
tun0: flags=4305<UP,POINTOPOINT,RUNNING,NOARP,MULTICAST>  mtu 1500
        inet 172.5.7.2  netmask 255.255.255.0  destination 172.5.7.2
        inet6 fe80::a1a5:803a:c7bb:81ce  prefixlen 64  scopeid 0x20<link>
        unspec 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00  txqueuelen 100  (UNSPEC)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 3  bytes 144 (144.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@localhost openvpn]# ifconfig tun

tun0: flags=4305<UP,POINTOPOINT,RUNNING,NOARP,MULTICAST> mtu 1500

inet 172.5.7.2 netmask 255.255.255.0 destination 172.5.7.2

inet6 fe80::a1a5:803a:c7bb:81ce prefixlen 64 scopeid 0x20<link>

unspec 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00 txqueuelen 100 (UNSPEC)

RX packets 0 bytes 0 (0.0 B)

RX errors 0 dropped 0 overruns 0 frame 0

TX packets 3 bytes 144 (144.0 B)

TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

Habilitando serviço serviço OpenVpn inicialização

[root@localhost ~]# systemctl -f enable openvpn@teste
Created symlink from /etc/systemd/system/multi-user.target.wants/openvpn@teste.service to /usr/lib/systemd/system/openvpn@.service.

1 2	[root@localhost ~]# systemctl -f enable openvpn@teste Created symlink from /etc/systemd/system/multi-user.target.wants/openvpn@teste.service to /usr/lib/systemd/system/openvpn@.service.

ORA-01940: cannot drop a user that is currently connected

Publicado em 19 de julho de 2018 by rodrigosilvaesilva

Dropando usuário ORCL

SYS@ORCL > drop user ORCL cascade;

1	SYS@ORCL > drop user ORCL cascade;

Erro ORA-01940

ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

1 2	ERROR at line 1: ORA-01940: cannot drop a user that is currently connected

Solução: Parando

SYS@ORCL > Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ORCL > Shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Iniciar em modo restrict

SYS@ORCL > startup restrict;
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size 2262656 bytes
Variable Size 1979713920 bytes
Database Buffers 8690597888 bytes
Redo Buffers 16900096 bytes
Database mounted.
Database opened.

SYS@ORCL > startup restrict;

ORACLE instance started.

Total System Global Area 1.0689E+10 bytes

Fixed Size 2262656 bytes

Variable Size 1979713920 bytes

Database Buffers 8690597888 bytes

Redo Buffers 16900096 bytes

Database mounted.

Database opened.

Dropando usuário ORCL

SYS@NBS > drop user ORCL cascade;

User dropped.

SYS@NBS > drop user ORCL cascade;

User dropped.

Parando

SYS@ORCL > shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ORCL > shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Iniciando

21:50:44 SYS@ORCL > startup
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size 2262656 bytes
Variable Size 1979713920 bytes
Database Buffers 8690597888 bytes
Redo Buffers 16900096 bytes
Database mounted.
Database opened.

21:50:44 SYS@ORCL > startup

ORACLE instance started.

Total System Global Area 1.0689E+10 bytes

Fixed Size 2262656 bytes

Variable Size 1979713920 bytes

Database Buffers 8690597888 bytes

Redo Buffers 16900096 bytes

Database mounted.

Database opened.

Em outro post mostrarei como matar processo do usuário.

ORA-28001: the password has expired

Publicado em 17 de julho de 2018 by rodrigosilvaesilva

Ao realizar a análise de uma rotina de backup Data Pump me deparei com seguinte erro no log abaixo:

EXP-00056: ORACLE error 28001 encountered
ORA-28001: the password has expired
EXP-00030: Unexpected End-Of-File encountered while reading input
EXP-00000: Export terminated unsuccessfully

EXP-00056: ORACLE error 28001 encountered

ORA-28001: the password has expired

EXP-00030: Unexpected End-Of-File encountered while reading input

EXP-00000: Export terminated unsuccessfully

Verificando contas com senha expirada.

SYSTEM@ORCL> select username, account_status from dba_users where lock_date is not null;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
FLOWS_FILES                    EXPIRED & LOCKED
MDSYS                          EXPIRED & LOCKED
ORDDATA                        EXPIRED & LOCKED
ORDSYS                         EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
ANONYMOUS                      EXPIRED & LOCKED
EXFSYS                         EXPIRED & LOCKED
OUTLN                          EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
APEX_PUBLIC_USER               EXPIRED & LOCKED
WMSYS                          EXPIRED & LOCKED
XDB                            EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
APPQOSSYS                      EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED
APEX_030200                    EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
SYSTEM                         EXPIRED & LOCKED

18 rows selected.

SYSTEM@ORCL> select username, account_status from dba_users where lock_date is not null;

USERNAME ACCOUNT_STATUS

------------------------------ --------------------------------

FLOWS_FILES EXPIRED & LOCKED

MDSYS EXPIRED & LOCKED

ORDDATA EXPIRED & LOCKED

ORDSYS EXPIRED & LOCKED

CTXSYS EXPIRED & LOCKED

ANONYMOUS EXPIRED & LOCKED

EXFSYS EXPIRED & LOCKED

OUTLN EXPIRED & LOCKED

DIP EXPIRED & LOCKED

APEX_PUBLIC_USER EXPIRED & LOCKED

WMSYS EXPIRED & LOCKED

XDB EXPIRED & LOCKED

ORACLE_OCM EXPIRED & LOCKED

APPQOSSYS EXPIRED & LOCKED

ORDPLUGINS EXPIRED & LOCKED

XS$NULL EXPIRED & LOCKED

APEX_030200 EXPIRED & LOCKED

SI_INFORMTN_SCHEMA EXPIRED & LOCKED

SYSTEM EXPIRED & LOCKED

18 rows selected.

Analisando profile do usuário SYSTEM

SYSTEM@ORCL > select username, profile from dba_users where username='SYSTEM';

USERNAME                       PROFILE
------------------------------ ------------------------------
SYSTEM                         DEFAULT

SYSTEM@ORCL > select username, profile from dba_users where username='SYSTEM';

USERNAME PROFILE

------------------------------ ------------------------------

SYSTEM DEFAULT

Analisando DBA_PROFILES

SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_LIFE_TIME               180

SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME LIMIT

-------------------------------- ----------------------------------------

PASSWORD_LIFE_TIME 180

Está definido 180 dias por default. Alterando Profile para ilimitado.

SYSTEM@ORCL> ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SYSTEM@ORCL> ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

Verificando novamente DBA_PROFILES.

SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_LIFE_TIME               UNLIMITED

SYSTEM@ORCL> select RESOURCE_NAME, LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME LIMIT

-------------------------------- ----------------------------------------

PASSWORD_LIFE_TIME UNLIMITED

Alterando a senha.

SYSTEM@ORCL> ALTER USER system IDENTIFIED BY password;         
User altered.

1 2	SYSTEM@ORCL> ALTER USER system IDENTIFIED BY password; User altered.

Desbloqueando…