A database login that can do everything is easy to set up and difficult to justify. If an application only reads products, it should not also be able to drop unrelated tables. Roles and grants make the required access explicit. This reference explains principals, permissions, least privilege, and separate application and migration identities. It uses PostgreSQL as the concrete grant example, then compares MySQL, SQL Server, and SQLite. You will test allowed and denied operations under the intended role rather than relying on an administrator's successful query. Permissions also complement application authorization; they do not replace per-user ownership checks in a shared application.
Separate identity from permitted actions
Authentication establishes a database identity; authorization determines which operations that identity can perform on objects. A principal may be a user, login, role, or another engine-specific identity. Permissions include actions such as SELECT, INSERT, UPDATE, DELETE, and schema administration. Roles can group permissions so several identities share a responsibility without duplicating every grant. Understand membership and inheritance rules in the selected engine. An object owner or administrator often has powers beyond ordinary grants, so testing only as the owner gives misleading evidence. Record which identity the application actually uses and which objects it needs. A role name such as readonly is not proof that its effective permissions are read-only.
Design responsibilities before issuing GRANT
List runtime reads and writes, reporting, migrations, backups, and operational administration separately. The web application may need row operations on a defined schema, while deployment migrations require controlled DDL access. A reporting role can often read a curated view rather than every confidential base table. Avoid using a root, superuser, or database owner account for ordinary requests. Default privileges and new objects need a policy so future tables do not accidentally become public or inaccessible. Grant only the required scope and review it when features change. The useful unit is a responsibility, not one universal account shared by all scripts because it was convenient during development.
Understand PostgreSQL object and schema privileges
PostgreSQL roles can be login identities or group-like roles. Access to a table inside a schema may require schema USAGE as well as the table privilege. Database connection privileges and sequence access can also matter under the chosen design. GRANT SELECT on a table does not necessarily grant every related object's access. Default privileges apply under their owner-specific rules and do not retroactively change existing objects. Membership and role switching should follow the documented engine behavior. The example below assumes roles and objects already exist and uses a controlled administrator. Do not copy a broad grant to PUBLIC merely to resolve a missing permission without understanding who receives it.
Compare MySQL, SQL Server and SQLite
MySQL account identities include host-related matching, and grants can apply at global, database, table, and other supported scopes. Modern role behavior and account-creation syntax should be checked against the selected version. SQL Server distinguishes server-level logins and database users, with securable scopes, roles, and permission interactions including DENY. Do not translate PostgreSQL syntax mechanically into either engine. SQLite is embedded and does not provide the same server-user GRANT model; filesystem access and the hosting application's authorization are central. SQL engine permissions are one layer within network, operating-system, and application security. A diagram labeling every platform as 'GRANT SELECT' hides these meaningful differences.
Limit data exposure as well as operations
Read access can expose sensitive records even when writes are denied. Use views or supported row-level security features where they fit the architecture, and understand their ownership and execution semantics. A shared runtime identity still needs application queries to enforce tenant and ownership rules unless the database policy explicitly handles them. Prepared statements prevent value injection but do not limit a permitted account's accessible data by themselves. Keep credentials in protected runtime configuration and rotate exposed values. Audit role changes, privileged operations, and suspicious access with a privacy-aware logging policy. Avoid storing raw passwords or connection secrets in the audit trail. Backups and replicas need equivalent protection because they contain the same valuable data.
Test effective permissions and maintain them
Connect as the actual runtime or reporting role and perform a controlled allowed read, allowed write if relevant, and prohibited operation in an isolated test environment. Verify denied DDL without risking production structures. Inspect effective grants and role memberships, including unexpected inheritance or PUBLIC access. Remove obsolete access after responsibility changes, but test before revoking privileges needed by live workflows. Changes can affect cached sessions or deployment behavior under the engine's rules, so follow a controlled rollout. Document why each grant exists and which feature requires it. Periodic review is more useful when permissions map to named responsibilities rather than a long unexplained list of commands.
Practice task and worked solution
Define roles for a shop's web runtime, reporting job, and migration process. The solution lists required objects and operations for each, keeps schema administration outside ordinary requests, and restricts reporting to permitted fields or views. Choose one dialect and write its exact grants under a controlled setup plan. Test as each role rather than as the owner. Include a denied update for reporting and denied schema change for runtime. Review new-object default policy, memberships, and PUBLIC grants. Explain how per-customer authorization still works when all web requests share one database identity. Keep real credentials out of the exercise and source files.
Continue learning
Review database security, views, and constraints.
See PostgreSQL privileges, MySQL GRANT, and SQL Server permissions for engine-specific rules.
SQL Security, Roles and Permissions Examples
Each example shows the code, the output it produces and what each part does.
A PostgreSQL read-only table role
These PostgreSQL statements assume app_reporting already exists and shop.products is an existing table. Execute only through a controlled administrator in a disposable permission-test database.
The role can read the stated table when connection and membership conditions permit. An update should be denied if the role has no inherited, owner, or other write privilege. The actual result rows and permission messages depend on the test database.
GRANT USAGE ON SCHEMA shop TO app_reporting;
GRANT SELECT ON TABLE shop.products TO app_reporting;
-- Then connect as app_reporting and test:
SELECT id, name FROM shop.products ORDER BY id;
-- A controlled UPDATE test should be denied if no other grants apply.
-- UPDATE shop.products SET name = 'Changed' WHERE id = 1;SELECT: permitted for shop.products
UPDATE: permission denied when no other write privilege appliesSQL Security, Roles and Permissions FAQ
Does a readonly name enforce anything?
No. Calling a role readonly is only a label; effective grants and memberships decide what it can do. Check the actual privileges, and remember that a role can inherit permissions from other roles it belongs to.
Is SELECT harmless?
No. Reading can expose confidential data, such as customers' personal details or salaries, which may be a legal breach on its own. Grant SELECT only on the tables or views each role needs, and use views to hide sensitive columns.
Can GRANT replace application authorization?
No. When a web application connects with one shared account, the database can't tell users apart. A shared account still needs resource-level rules in the application, such as "users can only see their own orders"; PostgreSQL's row-level security can add a second layer.
Does SQLite use the same role commands?
No. SQLite has no users, roles or GRANT; its embedded security model relies on file permissions and on the application that opens the database. Control access at the operating-system and application level.
What is the difference between a user and a role?
In PostgreSQL they're the same kind of object: a role that can log in is a user, and roles can be members of other roles to inherit privileges. MySQL and SQL Server distinguish logins or users from roles, but the idea is the same: grant privileges to roles and add users to roles.
SQL Security, Roles and Permissions Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Reporting needs product names but no updates. What should be granted?
SQL Security, Roles and Permissions Quiz
2 questions to check what you learned.
Which account should normally run public web requests?
- A superuser to avoid future permission errors
- A role with only required runtime privileges
- The migration owner reused for every web request
- One unrestricted identity shared by reporting and runtime
Why test as the intended role rather than the table owner?
- The owner has exactly the same permissions as every role
- Role names override all inherited privileges automatically
- Ownership can grant capabilities the role lacks
- A successful administrator query proves the runtime can execute it