Roles

In a database management system, roles are used to ease the management of user rights by combining related privileges. Roles can be assigned to users or other roles, making it easier to grant and revoke access permissions.

Creating a Role:
CREATE ROLE manager;

Granting Privileges to a Role:
GRANT SELECT, INSERT, UPDATE ON employees TO manager;

Assigning a Role to a User:
GRANT manager TO user1;

Roles assist in efficiently managing permissions, particularly in large organizations with many users.

View

Views are virtual tables that store the results of a SQL query. They allow you to present data from one or more tables in a specified format or subset without storing it separately. Views may make difficult queries easier, improve security by limiting access to specific data, and give a uniform interface for diverse user groups.

Creating a View:

CREATE VIEW employee_view AS
SELECT id, name, department
FROM employees 
WHERE status = 'active';

Using a View:

SELECT * FROM employee_view;

Views can be dynamically changed based on the underlying tables, ensuring that the data is shown accurately.

Transfer and Revoking of Privileges

Transferring privileges entails assigning specific permissions to users or roles to conduct operations on database objects, and revoking privileges entails removing such rights.

Granting Privileges

• Granting Privileges to a User:
  GRANT SELECT, INSERT ON employees TO user1;

This command allows user1 to perform SELECT and INSERT operations on the employees table.

• Granting Privileges to a Role:
  GRANT UPDATE, DELETE ON employees TO manager;

This command allows the manager role to perform UPDATE and DELETE operations on the employees table.

Revoking Privileges

• Revoking Privileges from a User
  REVOKE SELECT, INSERT ON employees FROM user1;

This command removes the SELECT and INSERT privileges on the employees table from user1.

• Revoking Privileges from a Role:
  REVOKE UPDATE, DELETE ON employees FROM manager;

This command removes the UPDATE and DELETE privileges on the employees table from the manager role.