Play Store Application link – SQL in 18 steps – App on Google Play

Managing users and securing a database is crucial for maintaining data integrity and preventing unauthorized access. In this blog, we will explore SQL user management and security with practical examples across different databases like MySQL and Oracle.

1. Creating and Managing Users

Every database system allows administrators to create and manage users who can access the database.

MySQL

To create a new user in MySQL:

CREATE USER 'john_doe'@'localhost' IDENTIFIED BY 'SecurePass123';

To check existing users:

SELECT User, Host FROM mysql.user;

To modify a user’s password:

ALTER USER 'john_doe'@'localhost' IDENTIFIED BY 'NewPass456';

To delete a user:

DROP USER 'john_doe'@'localhost';

Oracle

In Oracle, a user is also associated with a schema:

CREATE USER john_doe IDENTIFIED BY SecurePass123;

To modify the password:

ALTER USER john_doe IDENTIFIED BY NewPass456;

To drop a user:

DROP USER john_doe CASCADE;

(The CASCADE keyword removes all objects owned by the user.)

2. Granting and Revoking Permissions (GRANT, REVOKE)

Permissions define what actions a user can perform in a database.

Granting Privileges

MySQL

To grant privileges:

GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'john_doe'@'localhost';

To grant all privileges:

GRANT ALL PRIVILEGES ON mydatabase.* TO 'john_doe'@'localhost';

Oracle

To grant permissions:

GRANT CREATE SESSION, CREATE TABLE TO john_doe;

To allow a user to perform SELECT operations on a specific table:

GRANT SELECT ON employees TO john_doe;

Revoking Privileges

MySQL

To revoke privileges:

REVOKE INSERT, UPDATE ON mydatabase.* FROM 'john_doe'@'localhost';

Oracle

To revoke permissions:

REVOKE CREATE TABLE FROM john_doe;

3. Roles and Privileges

Roles allow you to manage multiple users efficiently by assigning common privileges to a group.

MySQL (Starting from MySQL 8.0)

Creating a role:

CREATE ROLE data_analyst;

Granting privileges to a role:

GRANT SELECT, INSERT ON mydatabase.* TO data_analyst;

Assigning the role to a user:

GRANT data_analyst TO 'john_doe'@'localhost';

Oracle

Creating a role:

CREATE ROLE hr_manager;

Granting privileges to the role:

GRANT CREATE SESSION, SELECT ANY TABLE TO hr_manager;

Assigning the role to a user:

GRANT hr_manager TO john_doe;

To revoke a role:

REVOKE hr_manager FROM john_doe;

4. Database-Specific Variations

• MySQL allows user management using the mysql.user system table.
• Oracle users are tightly coupled with schemas, and the DBA_USERS view helps in managing users.
• SQL Server uses CREATE LOGIN and CREATE USER separately to manage database-level access.

Conclusion

User management and security in SQL ensure that only authorized individuals can access and modify data. By creating users, assigning roles, and managing privileges effectively, organizations can prevent unauthorized access and ensure database security.