Linux Server Deployment & SSH Mastery · Lesson

Database User and Access Control

Create database users, grant specific privileges, and implement robust access control mechanisms for your databases.

Database User and Access Control is a free Linux Server Deployment & SSH Mastery lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Linux Server Deployment & SSH Mastery learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Intro to DB User Management

Welcome to Database User and Access Control! In this lesson, we'll learn how to manage who can do what with your database.

Think of it like setting up different user accounts on your computer, each with specific permissions.

Why Access Control Matters

Proper access control is vital for database security and integrity. It helps you:

Prevent unauthorized access: Only allowed users can connect.
Enforce the principle of least privilege: Users only get the permissions they absolutely need.
Audit actions: Track who made changes to your data.

Creating a New Database User

First, let's create a new user account in PostgreSQL. This user will need a username and a strong password.

Run this command in your PostgreSQL client (e.g., psql):

CREATE USER coddykit_analyst WITH PASSWORD 'SuperSecureP@ssw0rd!';

Granting Read-Only Privileges

Now that we have a user, let's give them permissions. A common starting point is read-only access to specific tables.

The GRANT SELECT command allows a user to read data from a table:

GRANT SELECT ON TABLE public.sales_data TO coddykit_analyst;

Allowing Data Modification

What if your user needs to add, change, or remove data? You'll grant INSERT, UPDATE, or DELETE privileges.

Here's how to allow adding and updating records:

GRANT INSERT, UPDATE ON TABLE public.customer_info TO coddykit_analyst;

Understanding ALL PRIVILEGES

The ALL PRIVILEGES option grants all available permissions on a database object. Use this with extreme caution!

GRANT ALL PRIVILEGES ON DATABASE my_db TO admin_user; is powerful.
WITH GRANT OPTION: Allows the recipient to grant those same privileges to other users. This is a security risk if not managed carefully.

Revoking Privileges

Sometimes, a user's permissions need to be reduced or removed entirely. The REVOKE command does exactly that.

For example, to remove the ability to update customer info:

REVOKE UPDATE ON TABLE public.customer_info FROM coddykit_analyst;

Listing User Permissions

It's important to verify what privileges a user actually has. You can query the database's information schema for this.

This query shows privileges on a specific table for a specific user:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public' AND table_name = 'sales_data' AND grantee = 'coddykit_analyst';

Deleting a Database User

When a user account is no longer needed, it's good practice to remove it to reduce potential security risks.

Use the DROP USER command to delete a user from the database system:

DROP USER coddykit_analyst;

Best Practices for Security

Always follow these guidelines for robust database security:

Least Privilege: Grant only necessary permissions.
Strong Passwords: Enforce complex, unique passwords.
Regular Audits: Periodically review user accounts and their privileges.
Role-Based Access: Create roles with specific permissions and assign users to roles.

Check Your Knowledge

A new user, report_viewer, needs to be able to read data from the daily_logs table and also delete old entries from it. Which set of commands would achieve this?

Recap: User & Access Control

Great job! You've learned the fundamentals of database user and access control. We covered:

Creating and dropping database users.
Granting and revoking specific privileges like SELECT, INSERT, UPDATE, and DELETE.
Understanding powerful options like ALL PRIVILEGES and WITH GRANT OPTION.
Best practices for securing your database with proper user management.

These skills are essential for maintaining a secure and well-managed server environment!

Frequently asked questions

Is the “Database User and Access Control” lesson free?

Yes — the full text of “Database User and Access Control” is free to read here on the web, and the Linux Server Deployment & SSH Mastery course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Linux Server Deployment & SSH Mastery course, upgrade to CoddyKit PRO.

What will I learn in “Database User and Access Control”?

Create database users, grant specific privileges, and implement robust access control mechanisms for your databases. You practise Linux Server Deployment & SSH Mastery with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Linux Server Deployment & SSH Mastery?

No prior experience is required. Linux Server Deployment & SSH Mastery on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Database User and Access Control” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Linux Server Deployment & SSH Mastery lesson?

Yes. Every Linux Server Deployment & SSH Mastery lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Linux Server Deployment & SSH Mastery