Database Administration - DBA

Overview

Whether you are a DBA by title or not you have to keep the information in this section in the back of your mind as you work with databases.

  • You control the system, so you are responsible for ensuring that the data is secure and complies to all relevant standards. You must also hold yourself to the highest ethical standards. Some organizations include a specific database security administrator role that focuses on these duties, but all DBAs need to keep them in mind.
  • Transparency: When you collect information, you should tell the owners of the information exactly what data you will collect and what you will do with it. Inform them about how you use the data, how you store it, who will have access to it, and how you will dispose of it when you have finished using it.
  • Consent: You should get clear consent from data owners before you collect their data. This should detail what data you will be allowed to collect and how you will be allowed to use it.
  • Integrity: Always be clear about your procedures and policies, and always follow them consistently. As far as you can, make sure that others in your organization also follow the correct procedures and policies.

Consider creating a code of ethics–a written statement of security-related standards and intentions.

Secure System Design Outline

The structure of your system is a powerful tool in keeping your data safe. If your system is built to maintain security, it’s much easier to prevent breaches. To make sure your system works for you, consider these factors.

  • Protection from malicious access: The front line of protection for your data is basic software security. Your firewall and other cybersecurity tools should actively prevent hacking and malware installation, and alert you to threats. Be sure you update this software frequently, to keep scanning lists up to date. Also, educate users about phishing and other ways that they can unwittingly enable malicious access.

  • Secure storage: The storage you choose for your data must be secure not only from malicious access, but also from hardware failure and even natural disasters. Select your services carefully and make sure you understand their security practices and disaster preparedness plans. Back up your data regularly and reliably to minimize data loss in case of an emergency.

  • Accurate access: Only those who need certain data should be able to access it. Establish a system of assigning and tracking privileges that assigns each user only the necessary privileges, and controls what they can do with the data. Ensure that your policy complies with any data usage agreements you have made.

  • Secure movement: Data can be particularly vulnerable to interception when you move it into or out of storage. Be sure to consider safe transfer methods as carefully as you plan safety for the rest of your system.

  • Secure archiving: At some point, you may want to move data from active storage to an archive. This can protect it from accidental access and make your system more efficient. Make sure your archiving system is as secure as the rest of your storage. Data agreements often specify how long you may use the data, so be sure the archived data is regularly weeded for expired rights and don’t retain any more data than you will need for compliance with organization policy. Eliminate your discarded data securely and completely.

Compliance Issues

Maintaining compliance with all relevant laws and standards is a vital concern. Failure can result in data insecurity, professional censure for your organization, and even legal action. This list includes some of the most common types of standards, but it’s not exhaustive; always find out which regulations and standards apply to your organization.

  • National/international regulations: Many industries must be concerned with important legal standards on the national or international level. Some examples include HIPAA regulations for health-related information in the US, the GDPR in Europe, and the Information Technology Act, 2000 in India.

  • Industry standards: Some data standards aren’t enforced by law but can still carry repercussions for your organization’s reputation and standing if they aren’t followed. An example might be the Payment Card Industry Data Security Standard (PCI DSS), which applies to any organization that collects, stores, or transmits cardholder data.

  • Organization best practices: Each organization will formulate standards for handling its internal data; as a DBA, you may work on that as part of your job. Employee confidentiality is often an important part of these policies, as is protecting intellectual property owned by the organization.

DB Life Cycle

The database lifecycle splits into four stages: The initial requirements analysis, designing and planning the database deployment, performing the actual implementation, and monitoring and maintaining day-to-day operations.

  • In the requirements analysis stage, DBAs work with data engineers to understand the purpose and scope of the database.
    • DBAs and the rest of the design team must establish what data is involved
    • talk to the users and producers of that data, and
    • develop samples of how users will use the data, such as reports or dashboards
    • work with the stakeholders to determine their requirements
    • stakeholders may include developers, data engineers, administrators or end-users of the applications supported by the database, technology managers, and other DBAs.
    • A few of the tasks they might perform include:
      • Analyzing the need for the database
      • Clarifying the goals the database fulfills; and
      • Identifying the users of the database.
  • In the design and plan stage, DBA’s job is to develop a plan for implementing the database. To do so, work with database objects such as instances, databases, tables, and indexes.
    • A database model represents the design of a database: which instance contains which databases and tables, how the tables relate to each other, how users access the data, and so on.
    • Database Architects and DBAs model the databases and their objects with the help of Entity Relationship Diagrams or ERDs
    • In self-managed environments such as on-premise databases, DBAs also need to consider size, capacity, and growth
    • They determine appropriate server resources like storage space, memory, processing power,and log file size
    • They also need to plan for how database objects are physically stored. For example, DBAs can choose to store frequently used data on a high-speed disk array or to store indexes separately from the data for better performance.
  • In the implementation stage, DBAs roll out the carefully planned design.
    • DBAs create database objects like instances, databases, tables, views, and indexes.
    • Another task is configuring database security, granting access for database users, groups, and roles, so database objects are accessible only by the specific users and applications authorized to access them
    • Automate repeating database tasks such as backups, restores, and deployments to improve efficiency
    • In populating the database,might import data from other databases, export data based on a query from a different source, or migrate projects from one environment to another, such as moving a project from the Application Development environment to the Production environment.
  • In the monitor and maintain stage, look after the daily operations of the database.
    • Monitor the system for long-running queries and help end-users optimize them to run faster and not overuse system resources
    • A key part is to review reports. Most Relational Database Management Systems have built-in reports to monitor activity, identify expensive queries, resource waits, and other relevant items.
    • Often companies build custom reports on top of these, and DBAs help with that
    • To keep the databases working at top efficiency, may also apply upgrades and security patches to database software
    • Need to stay up to date on issues and advancements in the field so he can recommend and implement emerging database technologies
    • Might also automate deployments and routine tasks such as backups whenever possible to keep processes working efficiently
    • In every database, operational issues will sometimes arise. might troubleshoot these issues, escalating problems where necessary
    • DBAs are responsible for keeping data safe
    • Regularly perform user audits and ensure that only authorized users are allowed to get into the system and that users can only see things they are supposed to see
    • Review logs and alerts, looking for failed logins and data access attempts to identify potential threats and vulnerabilities
    • Maintain database user and application permissions – revoking access to users and groups who should no longer have access and adding new users and roles as required to perform their jobs.