PL/SQL Security: Understanding Roles, Grants, and Privileges Quiz

Explore the essentials of PL/SQL security, focusing on roles, user privileges, and the use of grants for database access. This quiz helps you assess your understanding of database security and permission management concepts in PL/SQL environments.

1. User Privileges Basics

   Which type of privilege allows a user to create a new table in their schema?

   1. CREATE privilege
   2. DROP privilege
   3. SELECT privilege
   4. UPDATE privilege

   Explanation: The CREATE privilege enables a user to create new objects such as tables in their schema. SELECT is used only to read data from tables but cannot create them. DROP privilege allows a user to remove an object, not create it. UPDATE is used to modify existing data within a table, not to create structures.

2. Granting Privileges

   Which SQL statement properly grants permission to a user named JANE to update the EMPLOYEES table?

   1. UPDATE GRANT ON EMPLOYEES TO JANE;
   2. GRANT EMPLOYEES UPDATE TO JANE;
   3. GRANT UPDATE ON EMPLOYEES TO JANE;
   4. GIVE UPDATE TO JANE ON EMPLOYEES;

   Explanation: The correct command is GRANT UPDATE ON EMPLOYEES TO JANE; which gives the specified privilege on the given table. The other options have incorrect syntax or word order, making them invalid or meaningless in SQL. The word order and keywords are essential for the statement to execute successfully.

3. Role Purpose

   Why are roles used in PL/SQL security management for multiple users?

   1. To encrypt user passwords
   2. To prevent table creation
   3. To speed up database queries
   4. To group privileges for easier management

   Explanation: Roles allow administrators to assign a set of privileges to a group and then to users, simplifying management. They do not directly affect query speed, password encryption, or prohibit table creation. Their primary function is to streamline privilege assignment and maintenance.

4. Revoking Privileges

   If a privilege is no longer needed, which SQL statement removes an UPDATE privilege from the SALES table for user JOE?

   1. WITHDRAW UPDATE SALES TO JOE;
   2. REMOVE UPDATE SALES FOR JOE;
   3. REVOKE UPDATE ON SALES FROM JOE;
   4. DELETE UPDATE ON SALES TO JOE;

   Explanation: The REVOKE UPDATE ON SALES FROM JOE; statement removes the specified privilege from the user. The other options use incorrect SQL syntax and are not recognized SQL commands. The official SQL keyword for taking back privileges is REVOKE.

5. Types of Privileges

   Which of the following is a system privilege in PL/SQL security?

   1. DELETE ON PROJECTS
   2. SELECT ON EMPLOYEES
   3. UPDATE ON SALARY
   4. CREATE USER

   Explanation: CREATE USER is a system privilege because it allows the creation of new users in the database. The rest are object privileges as they pertain to operations on specific database objects. System privileges generally allow users to perform administrative tasks.

6. Object Privileges

   What privilege should be granted to allow a user to retrieve data from another user’s table?

   1. TRUNCATE privilege
   2. INSERT privilege
   3. ALTER privilege
   4. SELECT privilege

   Explanation: The SELECT privilege allows a user to retrieve or read data from a table. ALTER is used to modify the structure, TRUNCATE removes data, and INSERT is for adding new records. Only SELECT is related to data retrieval.

7. Grant Option

   What does the WITH GRANT OPTION clause enable when granting privileges?

   1. Allows only read access
   2. Enables password changes
   3. Allows the recipient to grant the same privilege to others
   4. Prevents the privilege from being revoked

   Explanation: WITH GRANT OPTION enables the user to pass on their received privileges to other users. It does not prevent revocation, restrict the privilege to read-only, or deal with passwords. This option is important in maintaining flexible privilege delegation.

8. Default Privileges

   When a new user is created, what default privileges do they typically possess?

   1. No privileges at all
   2. Only CREATE SESSION privilege
   3. Automatically granted DBA role
   4. Full access to all tables

   Explanation: A new user is often given the CREATE SESSION privilege so they can log in, but not perform further actions. Full table access and the DBA role are never granted by default for security reasons. Granting no privileges would prevent even basic login, which is not practical.

9. Roles vs. Direct Grants

   What is an advantage of using roles over directly granting privileges to individual users?

   1. Prevents all privilege inheritance
   2. Restricts users to one privilege per role
   3. Simplifies privilege management for many users
   4. Grants administrative access only

   Explanation: Roles aggregate privileges, so assigning a role to multiple users is more efficient than granting each privilege individually. Roles do not limit users to one privilege or involve only administrative access. They actually enable privilege inheritance through role assignment.

10. Viewing User Privileges

    Which data dictionary view can be queried to see which privileges have been granted to the current user?

    1. DBA_TABLES
    2. ALL_OBJECTS
    3. V$SESSION
    4. USER_TAB_PRIVS

    Explanation: USER_TAB_PRIVS displays table privileges granted to the current user. ALL_OBJECTS lists accessible objects, DBA_TABLES gives table information for all users, and V$SESSION relates to session data, not privileges. Only USER_TAB_PRIVS focuses on user privileges.