In SQL (Structured Query Language), Security and Permissions are mechanisms that control who can access the database and what actions they are allowed to perform.
This ensures that sensitive data is protected and only authorized users can read, insert, update, or delete data.
1. Authentication
- Determines who the user is (login system).
- Example: In SQL Server, a user must log in with a username and password.
2. Authorization (Permissions)
- Determines what a user can do inside the database.
- Controlled using GRANT, REVOKE, and DENY statements.
3. Types of Permissions
- Database-level permissions: access to the whole database.
- Object-level permissions: access to tables, views, procedures, etc.
- Statement permissions: rights to run certain SQL commands.
4. Examples
Example 1: Create a User
CREATE USER john IDENTIFIED BY 'mypassword';
Example 2: Grant Permissions
Allow user john to read data from the employees table:
GRANT SELECT ON employees TO john;
Example 3: Grant Multiple Permissions
Allow john to insert and update data:
GRANT INSERT, UPDATE ON employees TO john;
Example 4: Revoke Permission
Remove update rights:
REVOKE UPDATE ON employees FROM john;
Example 5: Deny Permission (SQL Server specific)
Explicitly deny delete:
DENY DELETE ON employees TO john;
✅ In simple words:
- GRANT → give permission
- REVOKE → take back permission
- DENY → explicitly block permission (overrides GRANT)