Question

Q1. What do you mean by A view in SQL language? Give an example?

Q2. Explain the role of SQL authorization mechanisms that allow to differentiate among the users of the database as far as the type of access they are permitted on various data values in the database?

Answer 1

Answer 1.

• A view is a virtual table which is based on the result set of an SQL statement.
• A view contains all rows of a table or few rows can be selected from the table.
• A view can be created from one or many tables which depends on the written SQL query to create a view.
• Example:
  • CREATE VIEW DEPARTMENTS_VIEW AS SELECT DepartmentName, DepartmentId FROM Departments.

Answer 2.

• GRANT and REVOKE SQL statements are used to set the user privileges on a table or view.
• The GRANT statement is used to grant specific privileges to users or to roles on particular tables or views.
• The REVOKE statement is used to remove privileges and role grants. From table/view.
• The grant and revoke privileges are:
  • System Privileges
    • CREATE
    • ALTER
    • DROP
  • Object Privileges
    • SELECT
    • EXECUTE
    • INSERT
    • UPDATE
    • DELETE
• GRANT syntax:
  • GRANT privilege_name ON object_name TO {userName |PUBLIC |roleName} [WITH GRANT OPTION];
  • Explanation:
    • GRANT: To give grant permission/privilege.
    • privilege_name: Privilege granted to the user (i.e. CREATE, INSERT, DROP etc.).
    • object_name: Table or view name.
    • userName: Grants privilege to this user.
    • PUBLIC: Access rights to all (Optional).
    • roleName: Set of privileges combined (Optional).
    • WITH GRANT OPTION: To access grants to other users (Optional).
• REVOKE syntax:
  • REVOKE privilege_name ON object_name FROM {userName |PUBLIC |roleName}
  • Explanation:
    • REVOKE: To remove permission/privilege.
    • privilege_name: Privilege to be removed.
    • object_name: Table or view name.
    • userName: Privilege removed for this user.
    • PUBLIC: Optional field.
    • roleName: Optional field.