Requirements Analysis and Permissions

In the first course of this series, you saw how requirements analysis^[1] guides the initial design of tables and the creation of a normalized ER diagram. That work answers questions such as “What data must we store?” and “How are entities related?”

Requirements analysis does not stop once the schema is designed. In modern database environments, it also plays a central role in defining who is allowed to do what with that data. In other words, requirements analysis is the starting point for a secure, role-based permission model.

The goal is to identify the smallest set of permissions each role needs to perform its job effectively, without exposing unnecessary data or operations. Done well, this yields:

• Simpler user experience (users see only what they need)
• Stronger security and privacy
• Better data integrity and accountability

During requirements analysis, you are not just collecting data requirements; you are also gathering access requirements. A practical workflow looks like this:

1. Identify roles and responsibilities
   Work with managers and process owners to list job roles (for example, Customer Service Representative, Branch Manager, Accountant, Developer, Reporting Analyst). For each role, capture high-level responsibilities, such as “handles customer inquiries,” “approves refunds,” or “creates financial reports.”
2. Discover information needs
   For each role, ask:
   • Which entities do you work with? (Customers, Orders, Vehicles, Employees, etc.)
   • Which fields do you actually use? (Contact details, order totals, payment status, etc.)
   • Do you need current detail, historical data, or summaries?
   These answers drive which tables, views, and columns each role should see.
3. Identify allowed operations
   Requirements analysis should clearly distinguish between:
   • Read-only needs (viewing data, running reports)
   • Data entry needs (creating new customers, orders, tickets)
   • Update needs (correcting addresses, changing order status)
   • Delete or archive needs (canceling orders, closing accounts)
   • Structural needs (creating constraints, adding foreign keys – usually reserved for DBAs)
   Each of these maps naturally onto permissions such as SELECT, INSERT, UPDATE, DELETE, and REFERENCES.
4. Map needs to roles, not individuals
   Instead of granting permissions user by user, requirements analysis should produce a role-based access model. You define roles that match job functions, grant permissions to those roles, and then assign users to the roles. This makes access simpler to manage as staff join, leave, or change responsibilities.
5. Validate with supervisors and policy
   Finally, review the proposed permissions with supervisors, data owners, and compliance officers. Confirm that the model follows company policies, legal/regulatory requirements, and the principle of least privilege.

Views are a key design tool that connect requirements analysis to practical permission assignments:

1. Views as security filters
   Requirements analysis often reveals that some roles need partial access to a table—such as a subset of columns or rows. Implement these needs using views that:
   • Hide sensitive columns (for example, SSN, salary)
   • Filter out rows a role should not see (for example, other regions, restricted accounts)
   Permissions are then granted on the view, not on the base table.
2. Views to simplify complex schemas
   Non-technical users rarely need to understand all joins and keys in the schema. Requirements analysis can identify the “business objects” they think in (for example, “Customer Orders” or “Vehicle Availability”), and you can expose those concepts as views.
3. Views and the least-privilege model
   Because views can be read-only or tightly controlled, they allow you to grant SELECT on a view while denying direct access to underlying tables. This is a common pattern for reports, dashboards, and self-service analytics.

Views can be added or removed without changing base tables, but you must update any applications that depend on them. Requirements analysis should therefore also document which roles and systems depend on which views.

To see how requirements analysis drives permissions, consider a common role: the Customer Service Representative (CSR).

During interviews with supervisors and CSRs, you might learn that:

• CSRs must look up customer and order records.
• They must create new customers and orders.
• They need to correct typos in contact information.
• They should not delete records or alter financial totals after billing.
• They do not design schema or create foreign keys.

From this requirements analysis, you can derive a permission set:

Derived Permissions for CSRs

• SELECT – Required to look up existing customers and orders.
• INSERT – Required to add new customer and order records.
• UPDATE – Required to correct addresses, phone numbers, or non-financial attributes. You may restrict this to specific columns (for example, allow updating contact details but not invoice totals).
• DELETE – Typically not granted to CSRs. If the business allows cancellation, you may implement this through a status field updated via a stored procedure rather than a hard delete.
• REFERENCES – Not needed for CSRs, because they do not define new tables or constraints.

You might implement this in SQL as:

GRANT SELECT, INSERT, UPDATE
ON   customer_view, order_view
TO   role_customer_service;

Later, individual CSRs are assigned to role_customer_service. If job responsibilities change, you adjust the permissions for the role rather than for each person, keeping the security model aligned with the requirements.

Requirements analysis and permission design reinforce one another. The following best practices reflect current industry standards:

• Start with roles, not tables. Ask “What does this role need to do?” rather than “What should users see in this table?”
• Apply the principle of least privilege. Grant only the permissions that were explicitly justified during requirements analysis. If in doubt, start with read-only access and add write permissions carefully.
• Document an authorization matrix. Maintain a simple matrix (role × object × permission) as part of your requirements and design documentation. This becomes the blueprint for your GRANT/REVOKE or role definitions.
• Use views and stored procedures as boundaries. For many roles, direct table access is unnecessary. Instead, expose views for reading and stored procedures for controlled updates or deletes.
• Review permissions regularly. Requirements change over time. Schedule periodic reviews with supervisors to ensure that permissions still match current duties and policies.
• Align with compliance and privacy rules. During requirements analysis, identify regulatory requirements (for example, retention, masking, or access logging) and build them into the permission model from the start.

By treating permissions as a direct outcome of requirements analysis, you ensure that security is not an afterthought but a core part of database design.

[1]Requirements analysis: The stage in the database design cycle when designers learn what data must be stored, how it will be used, and under what conditions it must be accessed, including which roles require which types of permissions.