Install OpenVPN on CentOS with Google OTP

OpenVPN is a full-featured open source SSL VPN solution. OpenVPN is a cost-effective, lightweight alternative to other VPN technologies. OpenVPN combines security with ease-of-use. OpenVPN runs on Linux, Windows XP/Vista/7 and higher, OpenBSD, FreeBSD, NetBSD, Mac OS X, and Solaris.

Modify SELinux policy policy

semanage port -a -t openvpn_port_t -p udp 1194

If you get Error message: "-bash: semanage: command not found" then install policycoreutils-python package via yum







yum -y install policycoreutils-python

Make sure you have the EPEL repository installed. If it is not installed, install epel yum repository with command below.

rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

Install openvpn and easy-rsa packages

yum install openvpn easy-rsa

Create directory to store rsa keys and copy contents of easy-rsa executable to the directory

mkdir /etc/openvpn/rsa

cp -rf /usr/share/easy-rsa/2.0/* /etc/openvpn/rsa

Export variable and start building the keys.

export KEY_SIZE=4096

export CA_EXPIRE=3654

export KEY_EXPIRE=3654

export KEY_COUNTRY="NP"

export KEY_PROVINCE="Bagmati"

export KEY_CITY="Kathmandu"

export KEY_ORG="Organization"

export KEY_EMAIL="info@dilli.com.np"

export KEY_OU="Technical"

export KEY_NAME="vpnkeys"

cd /etc/openvpn/rsa/

source ./vars

./clean-all

./build-ca

./build-key-server server

./build-dh

Create configuration file with the following settings.

cd /etc/openvpn
vi server.conf

port 1194 #- port
proto udp #- protocol
dev tun
tun-mtu 1500
tun-mtu-extra 32
mssfix 1450
reneg-sec 0
ca /etc/openvpn/rsa/keys/ca.crt
cert /etc/openvpn/rsa/keys/server.crt
key /etc/openvpn/rsa/keys/server.key
dh /etc/openvpn/rsa/keys/dh2048.pem
plugin /usr/share/openvpn/plugin/lib/openvpn-auth-pam.so openvpn
client-cert-not-required
username-as-common-name
server 10.251.0.0 255.255.255.0
push "route 202.79.55.140 255.255.255.255" # Mailservers
push "dhcp-option DNS 8.8.8.8"
push "dhcp-option DNS 8.8.4.4"
keepalive 5 30
comp-lzo
persist-key
persist-tun
status 1194.log
verb 3
log-append /var/log/openvpn.log
client-connect /etc/openvpn/scripts/up.sh

Create symbolic link for the openvpn-auth-pam.so file.

mkdir -p /usr/share/openvpn/plugin/lib/

ln -s /usr/lib64/openvpn/plugin/lib/openvpn-auth-pam.so /usr/share/openvpn/plugin/lib/openvpn-auth-pam.so

Modify /etc/sysctl.conf to allow ipv4 forwarding

vi /etc/sysctl.conf
net.ipv4.ip_forward = 1

sysctl -p

Masquerade all the traffic via eth0(eth0 is my interface with public IP address)

iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

Create OS user gauth that is the owner for the google authenticator. 

useradd gauth

Download google-authenticator-libpam from the URL below and compile it.

wget https://github.com/google/google-authenticator-libpam/archive/master.zip
unzip master.zip
cd  google-authenticator-libpam-master
./bootstrap.sh
./configure
make
make install

Configure google authenticator.

Create directory to store google authenticator files and change ownership to gauth.

mkdir /etc/openvpn/google-authenticator
chown gauth:gauth /etc/openvpn/google-authenticator 

chmod 700 /etc/openvpn/google-authenticator

Create script file with the content below for the creation of the OTP username.

cat > /root/create-gauth.sh

#!/bin/sh

# Parse arguments

USERNAME="$1"

if [ -z "$USERNAME" ]; then

  echo "Usage: $(basename $0) "

  exit 2

fi

# Set the label the user will see when importing the token:

LABEL='OpenVPN Server'

su -c "google-authenticator -t -d -r3 -R30 -W -f -l \"${LABEL}\" -s /etc/openvpn/google-authenticator/${USERNAME}" - gauth

Make the script file executable for owner of the script file.

chmod 700 /root/create-gauth.sh

Create file /etc/pam.d/openvpn with the contents below.

cat > /etc/pam.d/openvpn
auth [user_unknown=ignore success=ok ignore=ignore default=bad] pam_securetty.so
auth required /usr/local/lib/security/pam_google_authenticator.so secret=/etc/openvpn/google-authenticator/${USER} user=gauth forward_pass
auth include system-auth
account include system-auth
password include system-auth

Create vpn user with the OS command below

useradd -M -c "Mr. Dilli Raj Maharjan,email@dilli.com.np" -s /sbin/false vpn_dilliraj
passwd vpn_dilliraj

/root/create-gauth.sh vpn_dilliraj

Create script file that will be automatically executed once the vpn connection is successful. This script file is helpful in send the email with the details. Following is the contents used for sending mail.

cat > /etc/openvpn/scripts/up.sh
#!/bin/bash
smtp="A.B.C.D"
from="vpnadmin@dilli.com.np"
full_name=$(/usr/bin/getent passwd $common_name | /usr/bin/cut -d: -f5 | /usr/bin/awk -F "," '{print $1}')
email_address=$(/usr/bin/getent passwd $common_name | /usr/bin/cut -d: -f5 | /usr/bin/awk -F "," '{print $2}')
subject="VPN connected from $untrusted_ip\nContent-Type: text/html"

date_n_time=$(date +%c)
Message=$(echo "Dear ${full_name},


Your VPN Username $common_name has been connected from IP Address: $untrusted_ip. Make sure it is you or you are aware of it.
Please change your password if it is not you and contact System administrator for further assistance.
Following is the details
==========================================
Connected Since: ${date_n_time}

Public Address: $untrusted_ip
Virtual Address: $ifconfig_pool_remote_ip
Name: $common_name

Full Name: $full_name
Email Address: $email_address


Regards,
IT Team
Idealab.")

echo $Message | /bin/mailx -r ${from} -s "$(echo -e ${subject})" -S smtp="${smtp}" ${email_address}

Make the file executable

chmod 755 /etc/openvpn/scripts/up.sh

Start openvpn server process and make sure it will start automatically at run level 3,5

chkconfig openvpn --list
chkconfig openvpn on --level=35
chkconfig openvpn --list

/etc/init.d/openvpn start

Create configuration file for client with following details and copy ca.crt to client machine. Configure Google authenticator app on your smart phone. Use the key provided while creating the google authenticator account.

vi idealab_vpn.ovpn

client

dev tun

proto udp

remote 202.166.166.251 

resolv-retry infinite

nobind

tun-mtu 1500

tun-mtu-extra 32

mssfix 1450

persist-key

persist-tun

ca ca.crt

auth-user-pass

comp-lzo

reneg-sec 0

verb 3

We can login with the Password+OTP.

Read More

Oracle 11g SQL Fundamental II Answers

Lesson 3

1. select table_name from user_tables;

2. select table_name,owner from all_tables;

3. select column_name,data_type, data_length,data_precision,data_scale, nullable

from user_tab_columns where table_name='DEPARTMENTS';

4. select column_name, constraint_name, constraint_type, search_condition, status

from user_constraints Natural join user_cons_columns;

5. Comment on table departments is 'Company department information including name, code, and loction';

select comments from user_tab_comments;

6. Create synonym emp for employees;

select synonym_name, table_owner, table_name, db_link

from user_synonyms;

7. create view dept50

as select employee_id empno, last_name employee, department_id deptno from employees where department_id=50;

select view_name, text from user_views;

8. select sequence_name,min_value, max_value,increment_by, last_number from user_sequences;

select DEPARTMENTS_SEQ.nextval from dual;

create table dept2 as select * from departments;

create table emp2 as select * from employees;

alter table dept2 add constraint my_dept_id_pk primary key(department_id);

alter table emp2 add constraint my_emp_id_pk primary key(employee_id);

alter table emp2 add constraint my_emp_dept_id_fk foreign key(department_id) references departments(department_id);

9. select table_name from user_tables where table_name in ('EMP2','DEPT2');

10. select constraint_name, constraint_type from user_constraints where table_name in ('EMP2','DEPT2');

11. select object_name, object_type from user_objects where object_name in ('EMP2','DEPT2');

12. Create table sales_dept(

team_id number(3) primary key using index(

create index sales_pk_idx on sales_dept(team_id)),

location varchar2(30)

);

select index_name, table_name, uniqueness

from user_indexes where table_name='SALES_DEPT';

Lesson 4

1. CREATE TABLE sal_history(

employee_id number(6,0),

hire_date date,

salary number(8,2));

2. SELECT c.column_name, c.data_type, c.nullable, 

c.data_default, c.column_id, decode(uc.constraint_type,'P','Primary',NULL) "Primary Key"

FROM user_tab_columns c left join user_cons_columns cc

on (c.table_name=cc.table_name and c.column_name=cc.column_name)

left join user_constraints uc

on (cc.constraint_name=uc.constraint_name)

where c.table_name='SAL_HISTORY';

3. CREATE TABLE mgr_history(

employee_id number(6),

manager_id number(6),

salary number(8,2));

4. SELECT c.column_name, c.data_type, c.nullable, 

c.data_default, c.column_id, decode(uc.constraint_type,'P','Primary',NULL) "Primary Key"

FROM user_tab_columns c left join user_cons_columns cc

on (c.table_name=cc.table_name and c.column_name=cc.column_name)

left join user_constraints uc

on (cc.constraint_name=uc.constraint_name)

where c.table_name='MGR_HISTORY';

5. CREATE TABLE special_sal(

employee_id number(6),

salary number(8,2));

6. SELECT c.column_name, c.data_type, c.nullable, 

c.data_default, c.column_id, decode(uc.constraint_type,'P','Primary',NULL) "Primary Key"

FROM user_tab_columns c left join user_cons_columns cc

on (c.table_name=cc.table_name and c.column_name=cc.column_name)

left join user_constraints uc

on (cc.constraint_name=uc.constraint_name)

where c.table_name='SAL_HISTORY';

7. INSERT ALL

when salary > 20000 then

into special_sal values(employee_id,salary)

when 1=1 then

into sal_history values(employee_id,hire_date,salary)

into mgr_history values(employee_id,manager_id,salary)

SELECT employee_id,hire_date,salary,manager_id FROM employees where employee_id < 125;

SELECT * FROM special_sal;

SELECT * FROM sal_history;

SELECT * FROM mgr_history;

8. 

a.CREATE TABLE sales_week_data(

id number(6),

week_id number(2),

qty_mon number(8,2),

qty_tue number(8,2),

qty_wed number(8,2),

qty_thur number(8,2),

qty_fri number(8,2));

b. insert into sales_week_data

values(200,6,2050,2200,1700,1200,3000);

c. desc sales_week_data;

d. SELECT * FROM sales_week_data;

e. CREATE TABLE emp_sales_info(

id number(6),

week number(2),

qty_sales number(8,2));

f. DESC emp_sales_info;

g. INSERT ALL

into emp_sales_info values(id,week_id,qty_mon)

into emp_sales_info values(id,week_id,qty_tue)

into emp_sales_info values(id,week_id,qty_wed)

into emp_sales_info values(id,week_id,qty_thur)

into emp_sales_info values(id,week_id,qty_fri)

SELECT id,

week_id,

qty_mon,

qty_tue,

qty_wed,

qty_thur,

qty_fri

FROM sales_week_data;

h. SELECT * FROM emp_sales_info;

9. CREATE TABLE EMP_DATA (

name varchar2(25), 

email varchar2(25))

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY emp_dir

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

NOBADFILE

NOLOGFILE

FIELDS TERMINATED BY ','

)

LOCATION ('emp.dat'));

SELECT * FROM EMP_DATA;

10. CREATE TABLE emp_hist(

name varchar2(25), 

email varchar2(25));

a. alter table emp_data

modify email varchar2(45);

truncate table emp_hist;

insert into emp_hist values('arun','email_arun');

insert into emp_hist values('rajendra','rajendra_email');

insert into emp_hist values('riya','email_riya');

insert into emp_hist values('sujan','s_email');

insert into emp_hist values('pradeep','pr_email');

insert into emp_hist values('dilip','dilip_email');

insert into emp_hist values('dhirendra','dndra_email');

COMMIT;

b. merge into emp_hist eh

using emp_data ed

on (eh.name=ed.name)

when matched then

update set eh.email=ed.email

when not matched then

insert  values(ed.name,ed.email);

c. SELECT * FROM emp_hist;

11. CREATE TABLE EMP3

as SELECT * FROM employees;

SELECT * FROM emp3;

update emp3

set department_id=60

where last_name='Kochhar';

update emp3

set department_id=90

where last_name='Kochhar';

COMMIT;

SELECT * FROM emp3

where last_name='Kochhar';

SELECT versions_starttime "START_DATE",

versions_endtime "END_DATE",department_id FROM emp3

versions between scn minvalue and maxvalue

where last_name='Kochhar';

Lesson 5

1. ALTER session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS.';

2. SELECT tz_offset('US/PACIFIC-NEW') FROM dual;
SELECT tz_offset('SINGAPORE') FROM dual;
SELECT tz_offset('EGYPT') FROM dual;

b. ALTER SESSION SET time_zone='US/Pacific-New';

c. SELECT current_date, current_timestamp, localtimestamp
FROM dual;

d. ALTER SESSION SET time_zone='SINGAPORE';

e. SELECT current_date, current_timestamp, localtimestamp
FROM dual;

3. SELECT dbtimezone, sessiontimezone FROM dual;

4. SELECT last_name, extract(year FROM hire_date) FROM employees;

5. ALTER SESSION set nls_date_format='DD-MON-YYYY';

6. CREATE TABLE sample_dates(
date_col date);
insert into sample_dates
values(sysdate);
commit;
SELECT * FROM sample_dates;

ALTER table sample_dates
modify date_col timestamp;

SELECT * FROM sample_dates;

ALTER table sample_dates
modify date_col timestamp with time zone;

7. SELECT last_name, hire_date,CASE when extract(year FROM hire_date) = '2006' THEN 
'Needs Review' 
ELSE
'Not this year'
END
FROM employees;

8. SELECT last_name,hire_date,sysdate,
CASE
when hire_date + to_yminterval('15-0') <= sysdate THEN
'15 years of service'
when hire_date + to_yminterval('10-0') <= sysdate THEN
'10 years of service'
when hire_date + to_yminterval('5-0') <= sysdate THEN
'5 years of service'
ELSE
'maybe next year!'
END
FROM employees;

Lesson 6

1. SELECT last_name, department_id, salary FROM employees e
WHERE (department_id, salary) IN (SELECT department_id, salary FROM employees WHERE commission_pct is not null);

2. SELECT last_name, department_name, salary 
FROM employees e NATURAL JOIN departments d
WHERE (salary,commission_pct) IN (
SELECT salary,commission_pct FROM employees NATURAL JOIN departments WHERE location_id=1700);
SELECT * FROM departments;

3. SELECT last_name, hire_date, salary
FROM employees WHERE (salary,nvl(commission_pct,0)) =
(SELECT salary,nvl(commission_pct,0) FROM employees WHERE last_name='Kochhar')
and last_name!='Kochhar';

4. SELECT last_name, job_id, salary
FROM employees WHERE salary > all (SELECT salary FROM employees WHERE job_id='SA_MAN')
order by salary desc;

5. SELECT employee_id,last_name, department_id
FROM employees e WHERE exists (
SELECT department_id
FROM departments NATURAL JOIN locations
WHERE department_id=e.department_id
and city like 'T%');

6. SELECT last_name, salary, e.department_id, av.dept_avg
FROM employees e join (SELECT department_id,round(avg(salary),2) dept_avg FROM employees GROUP BY department_id) av
on (e.department_id=av.department_id)
and e.salary > av.dept_avg ;

7. SELECT last_name FROM employees e
WHERE not exists (SELECT 'X' FROM employees WHERE manager_id = e.employee_id);

8.SELECT last_name FROM employees e
WHERE exists (SELECT avg(salary) FROM employees 
WHERE department_id=e.department_id 
having avg(salary) > e.salary);

9. SELECT last_name FROM employees e
WHERE 2 < (SELECT count(last_name) FROM employees WHERE department_id=e.department_id 
and hire_date > e.hire_date
and salary > e.salary); 

10. SELECT employee_id,last_name, (SELECT department_name FROM departments WHERE department_id=e.department_id) AS departments
FROM employees e ;

11. with 
Summary AS (
SELECT department_id,sum(salary) dept_total FROM employees
GROUP BY department_id)
SELECT d.department_name, dept_total
FROM departments d join summary s
USING(department_id)
WHERE dept_total > (SELECT sum(salary)/8 FROM employees);

Lesson 7

1. SELECT first_name,last_name FROM employees where regexp_like(last_name,'^K(i|o)');

2. SELECT street_address,regexp_replace(street_address,' *','') FROM locations;

3. SELECT regexp_replace(street_Address,'ST$','STREET') FROM locations;
/*
Create a contacts table and add a check constraint to the p_number column to enforce the
following format mask to ensure that phone numbers are entered into the database in the
following standard format: (XXX) XXX-XXXX. The table should have the following
columns:
*/

4. DROP TABLE contacts;
CREATE TABLE contacts(
l_name varchar2(30),
p_number varchar2(30) check (regexp_like(p_number,'^\(\d\d\d\) \d\d\d-\d\d\d\d$')));


5. INSERT INTO contacts(P_NUMBER)
VALUES('(650) 555-5555');
INSERT INTO contacts(P_NUMBER)
VALUES('(215) 555-3427');
INSERT INTO contacts(P_NUMBER)
VALUES('650 555-5555');
INSERT INTO contacts(P_NUMBER)
VALUES('650 555 5555');
INSERT INTO contacts(P_NUMBER)
VALUES('(650)555-5555');
INSERT INTO contacts(P_NUMBER)
VALUES(' (650) 555-5555');



6. SELECT regexp_count('gtctcgtctcgttctgtctgtcgttctg','ctc',1,'i') FROM dual;

Read More