Alright, let’s talk about the database bouncer: GRANT and VOKE. This is how you, as the all-powerful admin (or at least someone with the right keys), tell the system exactly who is allowed to do what to which piece of data. It’s object-level privilege management, and it’s the bedrock of keeping your data from becoming a free-for-all. Forget this, and you might as well just post your user table on Pastebin.

The core idea is laughably simple. GRANT gives out a permission, REVOKE takes it back. It’s the “yes, you may” and “I’m afraid I must insist you stop.” You use them on specific database objects—tables, views, schemas, procedures—and assign these privileges to either a user or, more commonly and sanely, a role.

The Core Syntax: Giving and Taking Away

Let’s say you have a table, financials.revenue, which is obviously a bit sensitive. You have a role, auditor, that needs to see the data but should under no circumstances be able to change it or drop the table on a Friday afternoon.

You’d say:

GRANT SELECT ON financials.revenue TO auditor;

Boom. Done. The auditor role can now SELECT from that table. If you need to let them insert new records as well (brave of you), that’s a separate permission:

GRANT INSERT ON financials.revenue TO auditor;

Notice how granular this is? You don’t just grant “access”; you grant a specific action. This is a feature, not a bug. It lets you be surgical. Now, what if you regret that INSERT grant? Maybe the auditors got a bit too enthusiastic. You REVOKE it.

REVOKE INSERT ON financials.revenue FROM auditor;

The FROM keyword here is a bit of a grammatical oddity, but it’s what the database expects. Just go with it.

PUBLIC: The Permission Blast Radius

Here’s where things get spicy, and where most beginners accidentally create a gaping security hole. There’s a special “role” in most SQL databases called PUBLIC. It’s not a role you create; it’s built-in. And every single user in the system is a member of it. Always.

Grant a privilege to PUBLIC, and you’ve given it to every user, present and future. This is almost never what you actually want for your important tables.

-- DO NOT RUN THIS UNLESS YOU ENJOY CHAOS
GRANT ALL ON financials.revenue TO PUBLIC;

I’m not kidding. This is the equivalent of installing a revolving door on a bank vault. Use PUBLIC with extreme caution, typically only for things like granting CONNECT to a database or USAGE on a general-purpose schema. Your revenue table is not a general-purpose object.

WITH GRANT OPTION: Creating Delegates (and Problems)

Sometimes, you want a user to be able to grant their permissions to others. This is the WITH GRANT OPTION clause. It’s powerful and incredibly dangerous in the wrong hands.

GRANT SELECT ON financials.revenue TO senior_auditor WITH GRANT OPTION;

Now, the user senior_auditor can not only select from the table, but they can also run GRANT SELECT ON financials.revenue TO their_buddy;. You’ve created a delegation chain. The huge catch? If you REVOKE the privilege from senior_auditor, the privilege for their_buddy might stay intact depending on your database. This behavior can be counterintuitive and is a classic “wait, why do they still have access?!” moment. The standard SQL behavior is a mess here, and most databases have their own quirks. My advice? Use this option sparingly, and only if you fully trust the grantee to manage permissions correctly. Prefer having a centralized, automated process for privilege management instead of letting it proliferate organically.

Best Practices and the Gotchas

  1. Grant to Roles, Not Users: This is non-negotiable for any system bigger than a hello-world app. You grant privileges to a role (auditor, app_user, reporting_ro) and then assign users to that role. Want to give a new user all the permissions they need? One GRANT statement. Want to change what all auditors can do? Change the grants on the auditor role once, not for fifty individual users.

  2. REVOKE is Granular Too: If you grant SELECT, INSERT and then REVOKE INSERT, the SELECT remains. REVOKE is not a “remove all” command. To nuke all privileges a role has on an object, you can use a catch-all REVOKE ALL PRIVILEGES ON table_name FROM role_name;.

  3. The Schema Trap: Permissions on a table don’t imply permissions on its schema. A user might have SELECT on your table, but if they don’t have USAGE on the schema that contains the table, they’ll get a “permission denied for schema” error. It’s a common foot-gun. Always remember to grant both:

    GRANT USAGE ON SCHEMA financials TO auditor;
GRANT SELECT ON financials.revenue TO auditor;

  4. Inspect What You’ve Done: You will forget what you’ve granted to whom. Use your system’s privilege catalog views to check your work. In PostgreSQL, it’s information_schema.table_privileges. In MySQL, it’s SHOW GRANTS FOR user_name;. Not checking these is like giving out keys without labeling them. Eventually, you’ll have no idea who can open which door.