[python]Email alert when Tufin has a failed automated step

This is the enhanced code for sending customized mail when automation step fails in Tufin. When the Tufin triggers, it sends the ticket_info to the input stream. the code has to read from the input stream and parse the xml.

#!/opt/tufin/securitysuite/ps/python/bin/python3
import smtplib
from sys import stdin
from email.mime.text import MIMEText
from lxml import etree

lines = ""

try:
    lines = etree.tostring(etree.parse(stdin), pretty_print=True, encoding="utf-8").decode("utf-8")
except EOFError:
    pass
except KeyboardInterrupt:
    pass


SERVER = "obfuscated.obfuscated.com.sg"
FROM = "tufin-noreply@obfuscated.com.sg"
TO = "myself@obfuscated.com.sg"
CC = ""

SUBJECT = "Test - Tufin securechange auto step stuck"
TEXT = "Dear admin,\r\
This message is generated for your attention that a step in tufin secure change is stucked.\r \
Manual intervention by you is required.\r\
On next version this message will add the tufin ticket number.\r\
This is a test message please IGNORE.\r\
{} ".format(str(lines))

message = MIMEText(TEXT)
message["From"] = FROM
message["To"] = TO
message["Subject"] = SUBJECT
message["Cc"] = CC
server = smtplib.SMTP(SERVER)
server.sendmail(FROM, TO, message.as_string())
server.quit()
Advertisements
Posted in General stuffs | Leave a comment

[python]Using cx_Oracle module

This is a try out for using cx_Oracle module maintained by Oracle.
The version I am trying is version 12.2
Here’s the same code:

import cx_Oracle


dsn = cx_Oracle.makedsn("dbhostname_or_fqdn", "1580", "student")
try:
    conn = cx_Oracle.connect("username", "password", dsn)
except BaseException as e:
    print(e)
    exit(1)
query = "select * from table_owner.table_name"
cur = conn.cursor()
try:
    cur.execute(query)
except BaseException as e:
    print(e)
    exit(1)
for result in cur:
    print(result)
quit()

The above is a test out code, to test on how to connect to the oracle database. Now i am expanding the code a little by doing two things:

  • To list the tables which I can access.
  • To list the tables I owned.
  • Duplicate from another owner’s table to your own.
  • Drop a table that belongs to you.

The username, password, service ID (db_sid) and the DB hostname will be obfuscated.

import cx_Oracle

# setting up the parameters
DSN = cx_Oracle.makedsn("db_hostname", "1580", "db_sid")
USERNAME = "db_username"
PASSWORD = "db_password"
QUERY = {
    '1': """select owner,table_name from all_tables 
            where owner = 'table_owner' or owner = 'db_username'
        """,
    '2': """select table_name
            from user_tables
        """
}


def drop_table(table_name):
    drop_string = "drop table {}".format(table_name)
    call_oracle(drop_string, USERNAME, PASSWORD, True)

def create_table(table_name, from_table):
    CREATE_TABLE = """
    CREATE TABLE {} AS
    SELECT * FROM {}
    """.format(table_name, from_table)
    # print(CREATE_TABLE)
    call_oracle(CREATE_TABLE, USERNAME, PASSWORD, True)


def call_oracle(query, username, password, is_create=False):
    try:
        conn = cx_Oracle.connect(username, password, DSN)
    except BaseException as e:
        print(e)
        exit(1)
    cur = conn.cursor()
    try:
        cur.execute(query)
        if is_create:
            conn.commit()
            print('Query executed successfully.')
    except BaseException as e:
        print(e)
        exit(1)
    if not is_create:
        for result in cur:
            print(result)
    cur.close()
    conn.close()
    quit()


def show_menu():
    menu = {
        '1': '[1] Show tables you are allowed to access.',
        '2': '[2] Show tables that you owned.',
        '3': '[3] Duplicate from another table.',
        '4': '[4] Drop a table.',
        '0': '[0] Quit'
    }
    options = menu.keys()
    sorted(options)
    for option in options:
        print(menu[option])
    select = input('Please choose an option: ')
    while True:
        if select is '1':
            call_oracle(QUERY[select], USERNAME, PASSWORD)
        elif select is '2':
            call_oracle(QUERY[select], USERNAME, PASSWORD)
        elif select is '3':
            table_name = input('Please enter table name: ')
            from_table = input('Type in the table you want to duplicate: ')
            create_table(table_name,from_table)
        elif select is '4':
            table_name = input('Type the table you wish to drop: ')
            drop_table(table_name)
        elif select is '0':
            break
        else:
            print('you have entered an invalid option.')
            break


if __name__ == "__main__":
    show_menu()

You do not need to put a semi colon at the end of every query, because the cx_Oracle will put that for you, if you put a semi colon you will receive an sql command not ended properly error from Oracle.

Posted in Database, Python | Tagged | Leave a comment

[python]Trying out AWS SDK

I got myself a free basic account to do some hands on try outs. It is not necessary to use the AWS SDK to invoke the API. This try out is to write a simple static script to create a new VPC and subnet from this new vpc.
The SDK is known as boto3, you can use pip to download the sdk. For this script to run I created an api account with the below three permissions:
Screenshot 2018-10-19 at 10.08.50 PM.png
Here’s the code:

import boto3


# For a list of region names refer https://docs.aws.amazon.com/general/latest/gr/rande.html
boto3.setup_default_session(
    aws_access_key_id='key is obfuscated',
    aws_secret_access_key='key is obfuscated',
    region_name='ap-southeast-1'
)

ec2 = boto3.resource('ec2')
try:
    vpc = ec2.create_vpc(CidrBlock='192.168.1.0/24')
    vpc.create_subnet(CidrBlock='192.168.1.0/28')
    ec2.create_internet_gateway()
except BaseException as e:
    print(e)

Here’s the result:
Screenshot 2018-10-19 at 10.12.53 PM
Screenshot 2018-10-19 at 10.13.46 PM

Posted in Python, Scripting | Tagged , , | Leave a comment

Database – Normalisation

All the examples will use this simple table for explanation.
Screenshot 2018-10-18 at 10.59.59 PM
Attribute is column, and tuple is row. In this table the attributes are from A to F.

Functional dependency
Screenshot 2018-10-18 at 11.03.44 PM
A is determinant, B is dependant.
B is functional dependent on A.

Screenshot 2018-10-18 at 11.05.35 PM
A and B are composite key which functionally determines C.

The determinant must only determine a unique value, the same determinant cannot have more than one unique value, the below example is not allowed. A is the determinant which cannot determine B and C, it must be either determining B or C not both.
Screenshot 2018-10-19 at 4.45.20 PM

Different determinants however can have the same values. Such as attribute A determines B, attribute C also determines B.
Screenshot 2018-10-19 at 4.48.07 PM

Partially functional dependency
Screenshot 2018-10-18 at 11.09.59 PM
A and B are candidate keys, but C is dependent only to A. This is partially functional dependent.

Transitive functional dependency
Screenshot 2018-10-18 at 11.21.02 PM

F is dependent on D, but D is not part of candidate key.

Screenshot 2018-10-18 at 11.21.53 PM
E and F dependent on D, but D is not part of candidate key.

Why normalisation?
Normalisation is to remove duplicates. Duplicates or redundant values in a database will create problems. These problems are:
Update anomaly, deletion anomaly, and insert anomaly.

  • Update anomaly.
    Consider below table, there supposed I need to change C5 to something else, I need to change all instances that has C5.
    Screenshot 2018-10-19 at 5.00.07 PM

  • Insert anomaly.
    Suppose I want to insert a new instance in attribute B, I will need to update a new value in attribute A as well. Insert anomaly arises when inserting a new value requires to insert another attribute as well.

  • Deletion anomaly.
    This anomaly happens when a deletion of a value in an attribute also deletes the values of other attribute(s). Consider if I delete A4, I inadvertently lost B4, C5, D4, E4 and F4.

1NF
To qualify for 1NF these are the requirements:
a. Attributes must be unique.
b. Values in tuple must be atomic i.e. no multiple values, only one value.
c. Values in tuple must be the same domain. Example if the attribute is date, the value must be date related.

2NF
2NF must have the qualification of 1NF and in addition must not have attributes that are partially functional dependent.
Example of 2NF


A and B determine C, A determine B, no partially dependent.

3NF
3NF must be qualified for 2NF, and must not have attribute(s) is/are dependent on another non-key attribute(s) i.e. no transitive dependency

Posted in Database | Tagged , | Leave a comment

[python]Email script

#!/usr/bin/env python
import smtplib
import sys
from email.mime.text import MIMEText

lineitems = sys.stdin.readlines()
SERVER = "obfuscated.email.server"
FROM = "tufin-noreply@obfuscated.com"
TO = "admin@ofuscated.com"
CC = "xyz@obfuscated.com"

SUBJECT = "Test - Tufin securechange auto step stuck"
TEXT = "Dear admin,\r\
This message is generated for your attention that a step in tufin secure change \ is stucked.\r \
Manual intervention by you is required.\r\
On next version this message will add the tufin ticket number.\r\
This is a test message please IGNORE.\r\
%s " % (lineitem for line in lineitems)

message = MIMEText(TEXT)
message["From"] = FROM
message["To"] = TO
message["Subject"] = SUBJECT
message["Cc"] = CC
server = smtplib.SMTP(SERVER)
server.sendmail(FROM, TO, message.as_string())
server.quit()

The above code snippet is an email script that is triggered when Tufin fails an automated step. The challenge now is to include a ticket number to the email.

Posted in General stuffs | Leave a comment

Database: count number of rows in a table

QL> select count(*) from cyrus.customer;

  COUNT(*)
----------
	11

SQL> 

This is to count the number of rows in a table. Cyrus is the owner and customer is the table.

Posted in Database | Tagged | Leave a comment

Database: Check all tables from a known owner

This is an oracle 12c query to find out all tables that are owned by “cyrus”.

select owner,table_name from all_tables where owner='CYRUS';

the contents in the row is case sensitive, in this example the content in row “owner” is CYRUS not Cyrus or cyrus.

another way if you do not know the case of the content use this:

select owner,table_name from all_tables where upper(owner)=upper('cyrus');

another way is to convert the owner name to lower case

select owner,table_name from all_tables where lower(owner)='cyrus';

Posted in Database | Tagged , | Leave a comment