Skip to content

Sql Scripts, java keystores, email

Jump to bottom Edit New page
ferng edited this page Nov 17, 2020 · 15 revisions

Oracle SQL

Environment

set head offs colsep , pagesize 0 trimspool on linesize 999
alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
cl scr;

SET LINESIZE 32767;
SET TRIMSPOOL ON;
SET TRIMOUT ON;
SET WRAP OFF;
SET TERMOUT OFF;
SET PAGESIZE 0;
set colsep |;
set heading off;
spool C:\temp\myFile.csv;
select * from SCHEMA.TABLE;
spool off;



#updates
update db_change_log set applied='N' where obje ct_name in ('db_change_HD24307', 'db_change_HD24298', 'db_change_HD24297', 'db_change_HD24297', 'matm_db_change_HD23988');

#load and run procedure
@script.sql
execute mpiis_db_rollback_HD24616 (<param>,...);

#synonyms and access one schema to another
sqlplus realadm
connect <schema_1>/<password>
create synonym <table_synonym> for <schema2>.<table>;
connect <schema_2>/<password>
grant select on <table> to <schema_1>;
connect reladm
grant create synonym to <schema_1>;
</code>

# command line
sql user/pw@host:1571/SERVICE_NAME

MsSQL SQL

Reports

  • Amalgamate rows into a single column, temporary tables, multiple queries in one result set
USE [Database_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 /*
 *	Author: ferng
 *	Date:	23/01/17
 *
 *	Description:	Used to generate report about new codes
 *			
 *	Revision History:
 *
 *	Date		Author		Description
 *	-------------------------------------------------------------------------------------------------------------
 *	23/01/17	ferng	    Inital report 
 *	31/01/17	ferng	    Refactor, broken up reports further to correct missing data issues 
 */
CREATE procedure dbo.my_special_report AS

BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @startDate DATE
DECLARE  @endDate DATE

SELECT @startDate = DATEADD(dd,-1,getdate())
SELECT @endDate = getdate()

/*	Before running any queries we gather all account modifications and their corresponding groups. This is not used as criteria for any report queries,
	it's just simpler to collate all the details separately into a comma delimited field which is then joined into all other reports
*/
CREATE TABLE #aagc (
	account_id INT PRIMARY KEY NOT NULL,
	modifications VARCHAR(MAX),
	group_codes VARCHAR(MAX)
);

INSERT INTO #aagc
SELECT DISTINCT
	a.account_id,
	modifications =	STUFF((S_ELECT distinct ', ' + aa1.name
							FROM Database_Name..account_modifications AS aa1
							WHERE aa1.account_id = aa2.account_id
							AND aa1.deleted_ind <>1
					FOR XML PATH(''), TYPE).value('.','nvarchar(max)'), 1, 2, ''),
	group_codes =	STUFF((S_ELECT distinct ', ' + ag.group_name
							FROM Database_Name..account_modifications AS aa1
								LEFT JOIN Database_Name..modification_group ag ON aa1.group_id = ag.group_id
							WHERE aa1.account_id = aa2.account_id
							AND aa1.deleted_ind <>1
					FOR XML PATH(''), TYPE).value('.','nvarchar(max)'), 1, 2, '')
FROM Database_Name..account_modifications AS aa2
INNER JOIN Database_Name..accounts a ON aa2.account_id = a.account_id
WHERE aa2.modification_id IS NOT NULL
AND a.account_status IN (6, 0, 7, 8, 1)
ORDER BY a.account_id;

/*	checks validated by agency for eligibility checks
*/
SELECT DISTINCT
	a.an AS 'AN',
	NULL AS 'CN',
	c.forename AS 'FORENAME',
	c.surname AS 'SURNAME',
	der.user_number AS 'LICENCE',
	dercr.code AS 'CODE',
	aaagc.modifications AS 'ACCOUNT MODIFICATIONS',
	aaagc.group_codes AS 'GROUP CODES',
	m.manufacturer_desc AS 'MANUFACTURER',
	a.hp_model_desc AS 'MODEL',
	'ELIGIBILITY' AS 'REPORT TYPE'
FROM Other_Database_Name..enquiry_response der
	INNER JOIN Database_Name..account_nominated_user agnd
		ON der.person_id = agnd.customer_id
	INNER JOIN Database_Name..customers c
		ON der.person_id = c.customer_id
	INNER JOIN Database_Name..accounts a
		ON der.account_id = a.account_id
	LEFT JOIN Other_Database_Name..enquiry_response_category derc
		ON der.feed_id = derc.feed_id 
	LEFT JOIN Other_Database_Name..enquiry_response_category_restriction dercr
		ON derc.response_category_id = dercr.response_category_id
	LEFT JOIN #aagc aaagc
		ON aaagc.account_id = a.account_id
	INNER JOIN Database_Name..manufacturers m
		ON a.hp_manufacturer_id = m.manufacturer_id
WHERE der.last_modified >= @startDate AND der.last_modified < @endDate 
	AND dercr.code IN ('10','15','20','25','30','31','32','33','35','40','42','43','78','79','106','114')
	AND der.person_id <> -1
	AND a.account_status IN (6, 0, 7, 8)

UNION
/*	Driving licence checks validated by agency for in-life checks
*/
SELECT DISTINCT
	a.an AS 'AN',
	NULL AS 'CN',
	NULL AS 'FORENAME',
	NULL AS 'SURNAME',
	der.driver_number AS 'LICENCE',	
	dercr.code AS 'CODE',
	aaagc.modifications AS 'ACCOUNT MODIFICATIONS',
	aaagc.group_codes AS 'GROUP CODES',
	m.manufacturer_desc AS 'MANUFACTURER',
	a.hp_model_desc AS 'MODEL',
	'IN-LIFE' AS 'REPORT TYPE'
FROM Other_Database_Name..enquiry_response der
	INNER JOIN Database_Name..accounts a
		ON der.account_id = a.account_id
	LEFT JOIN Other_Database_Name..enquiry_response_category derc
		ON der.feed_id = derc.feed_id 
	LEFT JOIN Other_Database_Name..enquiry_response_category_restriction dercr
		ON derc.response_category_id = dercr.response_category_id
	LEFT JOIN #aagc aaagc
		ON aaagc.account_id = a.account_id
	INNER JOIN Database_Name..manufacturers m
		ON a.hp_manufacturer_id = m.manufacturer_id
WHERE der.last_modified >= @startDate AND der.last_modified < @endDate 
	AND dercr.code IN ('10','15','20','25','30','31','32','33','35','40','42','43','78','79','106','114')
	AND der.person_id = -1
	AND a.account_status IN (8, 1)

ORDER BY [REPORT TYPE], [AN], [CN]

DROP TABLE #aagc
END

Keystores

Java

#list certs
keytool -keystore mc-idbi-mpi-ssl-cert.jks -list
keytool -keystore us.generic.jks -delete -alias "fern development (fern ca)"
openssl s_client -connect localhost:443 -showcerts

#pull out certificate
openssl pkcs12 -in Movida_Contakt.pfx -out fern.pem -nodes
openssl s_client -connect localhost:443 -ssl3 -cert fern.pem

#test endpoints
openssl s_client -connect localhost:443 -key 3v_it.key
curl --anyauth --post301 https://localhost:17143//fernWebService/fws
curl -k --ssl --post301 https://localhost:17143//fernWebService/fws

Windows (self signed certs)

$cert = New-SelfSignedCertificate -DnsName <machine> -CertStoreLocation "cert:\localMachine\my"
$pwd = ConvertTo-SecureString -String 'password1234' -Force -AsPlainText
$path = ‘cert:\localMachine\my\’ + $cert.thumbprint
Export-PfxCertificate -cert $path -FilePath C:\Users\me\Documents\powershellcert.pfx -Password $pwd
  • run mmc -32
  • File | Add/Remove Snap-in
  • Certificates > Add
  • Computer Account
  • Local computer
  • Certificates (Local Computer) > Personal > Certificates > Find new cert
  • right click | properties | friendly name
  • right click | copy
  • Certificates (Local Computer) > Trusted Root Certification Auth
  • right click | paste

PGP

Generate key

gpg --gen-key
#last sixteen chars are your key ID

#update .profile with key if necessary
export GPGKEY=D8FC66D2

#create ASCII armored version
gpg -o pubkey.asc --export -a $GPGKEY
gpg --armour -o seckey.asc --export-secret-keys

Managing keys

#import someone else's keys
gpg --import 0xB56BA117BC104F8CF96BDC530B922CA7649D5576.asc 

#add another email address to my key
gpg --edit-key <key-id>
  gpg> adduid
  Real Name: <name>
  Email address: <email>
  Comment: <comment or Return to none>
  Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
  Enter passphrase: <password>
  gpg> uid <uid>
  gpg> trust
  Your decision? 5
  Do you really want to set this key to ultimate trust? (y/N) y
  gpg> save

#revoke email address from my key
gpg --edit key <key-id>
# this will list the uids with a selector
uid 1
revuid
# deluid will really delete it but don't ever do it if key has been published

#show fingerprints
cat pub | gpg --with-colons --import-options show-only --import

pass

pass init <gpgId>

# view entries
pass ls work
pass ls mine
pass find vpn

# add password
pass insert mine/gpg

# add multiline password
pass insert mine/gpg

# generate password (and copy to cliboard)
pass generate -c work/vpn 20

# generate alphanumeric
pass generate -n work/vpn 20

# view password
pass mine/gpg

#copy to clipboard
pass -c mine/gpg

# delete password
pass remove work/vpn

OpenVPN

Configure DNS

  • add work dns sever to list of DNS servers

Connection Management

#connect
sudo openvpn --config fern.ovpn --auth-user-pass

#check connection (look for tun0)
ifconfig

#connect to work server (as defined in .ssh/config)
ssh work

Email

command line

MAIL FROM: spiderman@marvel.com
RCPT TO: ferng001@ycheese.com
RCPT TO: fernando.gonzalez@chees.com

RCPT TO: dennis.pang@cheese.com
RCPT TO: anh-le.chung@cheese.com
RCPT TO: fernando.gonzalez@cheese.com


DATA
to: dennis.pang@cheese.com, anh-le.chung@cheese.com,
fernando.gonzalez@cheese.com
subject:-we need your help

<msg>

QUIT
Add a custom footer
Add a custom sidebar

Clone this wiki locally