Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 79 ยท Introduction to Databases

DBA, Developers and Users: Roles Around a Database

Meet the people around a database: DBA duties, designers, developers and end users, with live SQL showing each role's view of the same data.

A database is never used by one anonymous "user". A small shop might have one person who does everything, but a real system involves several kinds of people with different goals, different skills and different permissions. Textbooks list them as roles: the database administrator, the designer, the application developer, the end user and a few more. Knowing who does what helps you understand why a DBMS has so many features. Each feature exists because some role needs it.

This lesson walks through the roles, describes what a database administrator (DBA) actually does day to day, and then lets you play three of the roles in the browser by running queries from each point of view. For the software side of the picture, see DBMS Architecture.

Who works around a database

DBA Runs it, protects it, keeps it fast.
Designer Decides what to store and how tables relate.
Developer Writes the programs that use the data.
End user Reads and changes data through an application.
1 / 2

The main roles

Database administrator (DBA)

The DBA is responsible for the database as a running service. The job exists because the DBMS gives the organization one shared, central resource, and someone must be accountable for it. A typical list of duties:

  • Installation and configuration. Install the DBMS, choose memory and storage settings, apply patches and plan upgrades.
  • Physical design and tuning. Choose indexes, partitioning and storage layout, read query plans, and fix slow queries. The DBA exploits physical data independence: the physical layer can change without breaking programs.
  • Security and access control. Create accounts, grant and revoke privileges, audit activity and manage encryption, as covered in Authorization and Access Control.
  • Backup and recovery. Define backup schedules, test restores and know the plan for a crash, using the mechanisms from Logging and WAL. A backup that has never been restored is only a hope.
  • Monitoring and capacity planning. Watch disk growth, connection counts, locks and replication lag, and forecast when more hardware is needed.
  • Availability. Set up replication, failover and maintenance windows (see Replication and Partitioning).
  • Change control. Apply schema changes safely and keep development, test and production in step.

In small teams one person does all of this. In large organizations the work is split into specialties: system DBAs (the server), application DBAs (schemas and queries) and security specialists. In cloud services much of the routine work, such as patching and backups, is automated, and the DBA role shifts toward design, cost and performance.

Database designer

The designer decides what the database holds. They interview the future users, identify the entities, attributes and relationships, draw an ER diagram, turn it into tables and normalize them. Their output is the schema: the structure that everything else depends on. Good design is the cheapest performance and quality improvement there is, because mistakes found after millions of rows exist are painful to fix. In small teams the designer is a developer or the DBA.

Application developer

Developers write the programs that talk to the database: websites, mobile apps, back-office tools and batch jobs. They write SQL, call stored procedures or use an ORM, handle transactions and deal with errors. Their concerns are correctness and productivity: use parameterized queries, keep transactions short and avoid sending thousands of queries for one page. They usually work against a development database and have limited rights on production.

End users

End users never see the schema. The classic textbook categories are:

Category What they do Example
Naive (parametric) users Use prepared forms and menus A cashier, an online shopper
Casual users Occasionally run ad-hoc queries A manager looking up last week's sales
Sophisticated users Write their own queries and analyses Analysts, data scientists
Specialized users Build unusual applications GIS, CAD or scientific systems

Other roles

  • System analysts gather requirements and specify the transactions that users will run.
  • Tool and DBMS developers build the DBMS itself, drivers, modeling tools and reporting tools.
  • Operators and maintenance staff look after the hardware, networks and data center.
  • Data stewards and governance staff own the meaning and quality of data, such as who may use customer data and for how long.

Playing the DBA

A DBA's best friend is the system catalog: tables where the DBMS describes itself. In SQLite the catalog is sqlite_master, and PRAGMA statements give more detail. Run this example to take inventory of the bookstore database exactly as an administrator would.

SQL The DBA's view: the system catalog ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

In a server DBMS such as PostgreSQL or MySQL the same ideas appear as information_schema and system views, plus account tables listing which users hold which privileges. The point is that the database describes itself in ordinary tables you can query.

Playing the developer and the end user

Different people should see different slices of the same data. A view is a stored query that looks like a table. The developer defines it, and the DBA grants access to the view instead of the underlying table. In this example, a support clerk needs customer names and cities but not other details, and an analyst needs order totals without any personal details.

SQL Three roles, three views of the same data ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQLite has no user accounts, so it cannot enforce who may query what. In a server DBMS the DBA would finish the job with statements such as GRANT SELECT ON support_customers TO support_staff; and would not grant anything on the base table. That combination of views plus privileges is the standard way to serve many roles safely.

How the roles work together

A new feature usually passes through all the roles. An analyst states a need (a weekly revenue report). The designer checks that the schema can answer it and may add a table. A developer writes the query and the report page. The DBA reviews the query plan, adds an index if needed, grants the reporting account access to the right views and schedules the backup that includes the new table. The end user opens the page and sees a chart, unaware of any of it. When something goes wrong, for example the report is slow at month end, the same people trace it through the same chain.

Two principles keep this healthy:

  • Least privilege. Every role and every application account gets only the rights its job needs, no more.
  • Separation of duties. The person who can change production data should not be the only one who approves the change, and developers should not routinely hold DBA rights in production.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which task belongs most clearly to the DBA?

    Backup, recovery, security and tuning are administration duties. Interviews and ER diagrams are design work, pages are development, and order forms are what end users do.
  2. 2. A cashier uses a fixed screen with menus and buttons to look up and record sales. Which kind of user is this?

    Naive users interact through prepared forms and menus and never write queries.
  3. 3. Why give an analyst access to a view instead of the base table?

    A view with granted privileges implements least privilege: the role sees only what the job requires.
  4. 4. What is the system catalog?

    The catalog holds metadata about tables, columns, indexes, users and more, and the DBA can query it like any other data.
  5. 5. Which statement about the designer role is true?

    The designer turns requirements into entities, relationships and normalized tables. In small teams the same person may also be a developer or DBA.

Summary

  • Several roles surround a database: DBA, designer, developer, end users (naive, casual, sophisticated, specialized) and supporting roles such as analysts and DBMS developers.
  • The DBA owns the running system: installation, tuning, security, backup and recovery, monitoring and availability.
  • The designer produces the schema; developers build programs that use it; end users work through applications or ad-hoc queries.
  • The system catalog lets administrators inspect the database with ordinary queries.
  • Views plus privileges give each role the slice of data it needs, following least privilege and separation of duties.

Next, look at how the data itself is organized in Data Independence and Schemas, or continue to DBMS Architecture.