Database Access Control and Authentication

Database access control is the set of rules that decides who can connect to a database and what they can do once connected. Think of it as two separate steps that always happen in order: authentication and authorisation.

Authentication is the 'who are you?' check. The person or system trying to connect provides a credential — something they know (a password), something they have (a certificate or a token), or something they are (biometrics, though this is rare for databases). The database checks that credential against its stored records. If the credential matches, the user is authenticated: the database has confirmed their identity. If it does not match, the connection is rejected.

Authorisation is the 'what are you allowed to do?' step, which happens only after authentication succeeds. Once the database knows who you are, it looks up your permissions. Can you read the customer table? Can you delete rows from the orders table? Can you create new databases? Authorisation rules are stored inside the database itself (for example, SQL GRANT statements) or managed externally through a system like AWS Identity and Access Management (IAM).

AWS databases offer several ways to handle authentication and authorisation. The first is database-native authentication. This is the oldest method. You create a user inside the database using a command like 'CREATE USER bob IDENTIFIED BY password123'. The password is stored inside the database. When Bob connects, the database checks the password it has on file. This method is simple but has a problem: the password might be stored in application configuration files, or a developer might paste it into a script, and suddenly it is exposed. Also, managing hundreds of users' passwords individually is a nightmare for large teams.

The second method is IAM database authentication. Here, AWS Identity and Access Management (IAM) becomes the gatekeeper. IAM is the AWS service that manages identities (users, groups, roles) and their permissions across all AWS services, not just databases. Instead of a static password, the user (or application) requests a temporary authentication token from IAM. This token is a short-lived string of characters that is valid for only 15 minutes. The user presents that token to the database instead of a password. The database checks with IAM to confirm the token is valid. If it is, the user is authenticated. This is far more secure because the token expires quickly — even if someone steals it, they cannot use it for long. It also means you never have to embed long-lived database passwords in your code.

The third method involves AWS Secrets Manager. Secrets Manager is a service that securely stores and automatically rotates secrets — database passwords, API keys, and other sensitive credentials. When an application needs to connect to a database, it asks Secrets Manager for the current password. Secrets Manager returns it, and the application uses it to connect. Secrets Manager can also be configured to change the password on a schedule (for example, every 30 days). This rotates the credential automatically, so even if an old password leaks, it becomes useless after rotation. The application never needs to know the new password in advance — it just asks Secrets Manager each time. This is a middle-ground approach: you are still using database-native authentication (username and password), but you have outsourced the insecure storage and rotation problem to a secure vault.

Why do multiple methods exist? Different situations demand different trade-offs. Database-native authentication is simple and works even if the network is completely isolated from the internet, but it is weak on credential theft. IAM authentication is stronger because it removes long-lived passwords, but it requires the database to be able to communicate with the IAM service (which means it needs internet access or a VPC endpoint). Secrets Manager offers a practical solution for legacy applications that need a plain username and password but that you still want to keep secure.

All three methods can be combined. For example, you might use IAM authentication for human users and Secrets Manager for legacy application connections. The key principle is 'least privilege' — give each user or application only the bare minimum permissions needed to do their job. If a user only needs to read customer names, do not give them permission to delete the table. This limits the damage if their credentials are compromised.

Finally, there is network-level access control. Security groups and network ACLs act like the club's neighbourhood: even if you have the perfect membership card, you cannot even knock on the door if your IP address is not in the allowed list. Security groups are virtual firewalls that control traffic to your database instance. They operate at the instance level and are stateful (if you allow incoming traffic, the response is automatically allowed out). Network ACLs (access control lists) operate at the subnet level and are stateless (you must explicitly define both inbound and outbound rules). In practice, most database security uses security groups, not NACLs, for simplicity.

All these layers — authentication, authorisation, credential management, and network controls — together form the full picture of database access control. For the DBS-C01 exam, you need to know the specific features of each AWS database service (Amazon RDS, Amazon Aurora, Amazon DynamoDB, Amazon ElastiCache, Amazon Redshift) and which authentication methods each supports.

Sophie is a database administrator at a company called 'QuickCart', an online grocery delivery startup. QuickCart uses an Amazon Aurora MySQL database to store customer accounts, order history, and payment information. The company has grown from 5 employees to 150 in two years, and security has become a top priority. Sophie needs to set up access control so that (a) developers can run queries to troubleshoot issues, (b) a nightly automated reporting script can read order data, and (c) the payment processing service can write new orders — all without exposing the database to unnecessary risk.

Sophie starts by locking down network access. She configures the Aurora database to be deployed inside a private subnet in the company's Amazon VPC (Virtual Private Cloud, which is a logically isolated section of the AWS cloud). She attaches a security group to the database that only allows inbound traffic from a specific set of IP addresses — the company's office VPN and a few specific application servers. No traffic from the public internet can reach the database at all.

Next, Sophie tackles authentication. For the developers, she sets up IAM database authentication. Each developer has an IAM user in the company's AWS account. Sophie attaches an IAM policy to each developer's user that grants them the permission to generate an authentication token for the Aurora database. When a developer needs to run a query, they use the AWS CLI (Command Line Interface) to request a temporary token. That token is passed to their SQL client, and they authenticate to the database with it. The token expires in 15 minutes, so if a developer leaves their laptop unlocked, the token becomes worthless quickly.

For the nightly reporting script, the situation is different. The script is written in Python and runs on an EC2 instance (Amazon Elastic Compute Cloud, a virtual server). The script does not have a human operator to type commands. Sophie cannot use IAM user authentication for this. Instead, she creates an IAM role — a set of permissions that can be assumed by an AWS resource — and attaches it to the EC2 instance. She also creates a database user inside Aurora that is mapped to this role. When the script runs, it automatically retrieves temporary credentials from the IAM role and uses them to authenticate to Aurora. No passwords are stored anywhere in the script.

For the payment processing service, the situation is more complex. This service is an older application that was written before IAM authentication existed. It expects to connect using a standard username and password. Sophie cannot rewrite the application. So she uses AWS Secrets Manager. She creates a secret in Secrets Manager that contains the database username and a strong password. She configures Secrets Manager to automatically rotate that password every 30 days. She also modifies the application's configuration so that, instead of reading a hardcoded password from a config file, it calls the Secrets Manager API to retrieve the latest password on each restart. Additionally, she applies an IAM policy that restricts the application's EC2 instance to only be able to read that specific secret — not any other secret in the account.

Finally, Sophie sets up database-level authorisation. Even after a user authenticates, they should not have free rein. Inside Aurora, she runs SQL commands to grant permissions: the reporting script gets only SELECT on the orders table; the developers get SELECT, INSERT, and UPDATE on non-payment tables; and the payment service gets INSERT and UPDATE on the payments table only. The 'root' superuser account is locked away and used only for emergency schema changes.

Every month, Sophie reviews the IAM policies and the Secrets Manager rotation logs. She also enables AWS CloudTrail to log every API call made to Secrets Manager and IAM. If someone tries to access a secret they should not, she sees it in the logs. This layered approach means that even if one defence fails — say, a developer's laptop is stolen — the other layers (short-lived token, security group, and database-level grants) still protect the data.