Strengthening Security in Data Warehousing and Business Intelligence

In today’s digital world, where data is incredibly valuable, security is a big concern for developers. This is especially true for Data Engineering (DE) and Business Intelligence (BI) professionals who work with different data systems. In this article I will try to explain how Delinea Password Vault can help Data Engineers and BI experts improve security and protect sensitive information.

The Special Challenges in DE and BI perspective: People working in Data Engineering and Business Intelligence face unique security challenges due to the kind of work they do:

1. Working with Lots of Data: Unlike some developers who deal with a few databases, DE and BI folks handle many databases, cloud services, and systems.
2. Protecting Sensitive Data: The information we work with is often private, like customer data or business secrets. If this data gets into the wrong hands, it can cause serious problems.
3. Complex Access Needs: Data Engineers often need different levels of access to different systems, which makes managing who can access what quite tricky.
4. Complex Pipelines: Data moves through complicated pipelines with different steps, tools, and systems. Keeping this data safe as it moves around is tough.
5. Handling Passwords: Keeping track of many passwords for different systems and regularly changing them is hard and time-consuming.

Password Vault

Password Vault

To ensure security to access for these databases there must be mechanism to prevent hard coded username and passwords in our codes. Also this approach is good for password rotation. At that point password vaults are perfect fit for our issue.

Password vault is a PAM solution that stores your username and passwords and you can programmaticly access these passwords by using it.

Delinea Password Vault: A Security Solution

Delinea Password Vault is a solution to the many security challenges faced by Data Engineers and BI professionals. It’s a secure place to keep important information, like passwords, keys, and tokens, cloud credentials, service accounts etc. Here’s how it helps:

1. Keeping Things Together: Delinea puts all the passwords in one place instead of scattering them around. This makes it easier to control who can get access and when to change passwords.
2. Extra Protection: The information in Delinea is locked up using special codes. Even if someone gets in, they won’t be able to understand the information.
3. Picking Who Gets What: Delinea lets you decide who can use which passwords. This means only the right people can get to sensitive information.
4. Watching Closely: Delinea keeps track of who uses what passwords and when. This helps keep an eye on things and find out if something unusual happens.
5. Automatic Changes: Delinea can change passwords automatically, which is important for security. People don’t have to do it manually.
6. Fitting In Easily: Delinea works smoothly with common tools Data Engineers and BI professionals use. It fits into their work without causing problems.

Using python-tss-sdk : Easy Access

With the python-tss-sdk, Data Engineers and BI experts can easily get the passwords they need from Delinea. This toolkit makes it simpler to work with Delinea and include secure passwords in their work.

With the python-tss-sdk, Data Engineers and BI experts can easily get the passwords they need from Delinea. This toolkit makes it simpler to work with Delinea and include secure passwords in their work.

from delinea.secrets.server import SecretServerCloud
from delinea.secrets.server import PasswordGrantAuthorizer

# base url for secret server cloud and credentials
tenant_id = ""
base_url = f"https://{tenant_id}.secretservercloud.com/"

# store this credentials in environment file
# and restrict access to this file from OS level
master_username = ""
master_password = ""

# path os the key from delinea
# example \Personal Folders\passwords\mysql
path_of_key = ""

# create authorizer from master credentials
authorizer = PasswordGrantAuthorizer(base_url, master_username, master_password)
secret_server = SecretServerCloud(tenant_id, authorizer)
 
# return server info from delinea
def get_db_credentials(path):
    secret = secret_server.get_secret_by_path(path_of_key)
    server = secret.get("items")[0]["itemValue"]
    username = secret.get("items")[1]["itemValue"]
    password = secret.get("items")[2]["itemValue"]

    credentials = {
        "server" : server,
        "username" : username,
        "password" : password
    }

    return credentials

Above function can be used while fetching data from any database. While using connection string this function can be used like this;

from urllib.parse import quote
from sqlalchemy import create_engine
from util import get_db_credentials

# name of the secret from delinea
secret_name = ""

# retrive credentials using function
db_credentials = get_db_credentials(secret_name)

login = db_credentials["username"]
password = db_credentials["password"]
host = db_credentials["server"]
schema = ""

# create pymysql connection with credentials
def get_pymysql_connection_engine(login, password, host):
    print('creating pymysql connection ..')
    engine = create_engine(
        'mysql+pymysql://{username}:{pw}@{host}/{schema}'.format(username=login,
                                                                 pw=quote(password),
                                                                 host=host,
                                                                 schema=schema))

    print('engine is ready')
    return engine

In addition you can get the password and username via sdk, but to retrive json-key from delinea, you need to implement below code.

from delinea.secrets.server import PasswordGrantAuthorizer
import requests

# base url for secret server cloud and credentials
base_url = ""
username = ""
password = ""

# generate authorizer from credentials
authorizer = PasswordGrantAuthorizer(base_url, username, password)
# retrieve access token
token = authorizer.get_access_token()

# build bearer token for request
token_bearer = f"Bearer {token}"
# build header for request
header = {"Authorization": token_bearer, 'Content-Type': 'application/json'}

# input secret id for json private key
secret_id = 123
slug = "json-private-key"

# secret json retrieved from api endpoint
secret_url = f"{base_url}api/v1/secrets/{secret_id}/fields/{slug}"

# make api call and retrieve json secret
resp = requests.get(secret_url, headers=header)
print(resp.text)

Conclusion: For people in Data Engineering and Business Intelligence, security is super important. Delinea Password Vault is like a superhero in this story, helping us keep our data safe. But remember, Delinea isn’t the only thing needed for security. It’s like a part of a bigger plan that also includes other safety practices like strong passwords, keeping bad guys out, and regularly checking for problems.

As the world of data keeps changing and bad guys get smarter, Data Engineers and BI experts can trust tools like Delinea to keep their data safe and earn the trust of the people who rely on them.

Thanks for reading and for your time, you can always reach me via LinkedIn. 👋