1Z0-006 Database Administration and Security Questions and Answers — Questions and Answers
Question 1: A new user account, 'DEV01', has been successfully created using the `CREATE USER` statement. However, when the user attempts to connect to the database, they receive an 'ORA-01045: user DEV01 lacks CREATE SESSION privilege; logon denied' error. What is the most likely cause of this issue?
- The user account is locked.
- The user has not been granted the CREATE SESSION system privilege. (Correct answer)
- The user's default tablespace quota is set to zero.
- The user entered an incorrect password.
Correct answer: The user has not been granted the CREATE SESSION system privilege.
In an Oracle database, successfully creating a user with `CREATE USER` only establishes the user's identity and authentication method. To actually log in and establish a connection, the user must be explicitly granted the `CREATE SESSION` system privilege. This is a fundamental security measure to control database access.
Question 2: A database administrator needs to manage permissions for a team of 20 analysts who all require the same 15 `SELECT` privileges on various tables. What is the most efficient and scalable method for managing these permissions?
- Create a script to grant each of the 15 privileges to each of the 20 analysts individually.
- Grant the `SELECT ANY TABLE` privilege to all 20 analysts.
- Create a role, grant the 15 `SELECT` privileges to the role, and then grant the role to each of the 20 analysts. (Correct answer)
- Have each analyst request privileges from the table owners directly.
Correct answer: Create a role, grant the 15 `SELECT` privileges to the role, and then grant the role to each of the 20 analysts.
Roles are the preferred method for managing privileges for groups of users with similar job functions. By creating a role and granting the necessary privileges to it, an administrator can then grant that single role to multiple users. This simplifies administration, as revoking or adding privileges only needs to be done once on the role, and the changes will propagate to all users granted that role.
Question 3: Which of the following is an example of an object privilege, as opposed to a system privilege?
- CREATE USER
- DROP ANY TABLE
- INSERT on the `sales.orders` table (Correct answer)
- CREATE TABLE
Correct answer: INSERT on the `sales.orders` table
An object privilege grants permission to perform a specific action on a specific schema object, such as a table, view, or sequence. `INSERT` on the `sales.orders` table is a direct permission on a particular object. In contrast, system privileges (like `CREATE USER`, `DROP ANY TABLE`, and `CREATE TABLE`) grant the ability to perform broader, database-wide actions.
Question 4: A database administrator implements a backup strategy that involves a full backup (Level 0) on Sunday. On Monday, a backup is run that captures only the blocks changed since Sunday. On Tuesday, another backup is run that captures only the blocks changed since Monday. What type of incremental backup was performed on Monday and Tuesday?
- Cumulative incremental backup
- Logical backup
- Full backup
- Differential incremental backup (Correct answer)
Correct answer: Differential incremental backup
A differential incremental backup (the default for Level 1) captures only the data blocks that have changed since the most recent incremental backup at either the same level (Level 1) or the base level (Level 0). In this scenario, Tuesday's backup only includes changes since Monday's backup, which is characteristic of a differential strategy.
Question 5: A new accounting clerk needs to run a pre-written application that only reads data from the `invoices` and `payments` tables. According to the principle of least privilege, which of the following is the MOST appropriate set of privileges to grant?
- The `DBA` role.
- `SELECT` privilege on the `invoices` and `payments` tables. (Correct answer)
- The `SELECT ANY TABLE` system privilege.
- `SELECT`, `INSERT`, `UPDATE`, and `DELETE` privileges on the `invoices` and `payments` tables.
Correct answer: `SELECT` privilege on the `invoices` and `payments` tables.
The principle of least privilege dictates that a user should be granted only the minimum permissions necessary to perform their required tasks. Since the clerk only needs to read data from two specific tables, granting the `SELECT` privilege on only those two tables is the correct approach, minimizing potential security risks.
Question 6: The user `BSMITH` was previously granted the ability to add new records to the `hr.departments` table. Due to a change in job roles, this permission is no longer necessary. Which SQL statement should the DBA execute to remove this specific privilege?
- ALTER USER BSMITH REMOVE INSERT ON hr.departments;
- DROP PRIVILEGE INSERT ON hr.departments FROM BSMITH;
- REVOKE INSERT ON hr.departments FROM BSMITH; (Correct answer)
- DELETE PRIVILEGE INSERT ON hr.departments FOR BSMITH;
Correct answer: REVOKE INSERT ON hr.departments FROM BSMITH;
The `REVOKE` statement is the correct SQL command used to remove privileges from a user or a role. The syntax `REVOKE [privilege] ON [object] FROM [user/role]` is used to take away a specific permission that was previously granted.
1Z0-006 Database Administration and Security Questions and Answers
A new user account, 'DEV01', has been successfully created using the `CREATE USER` statement.
However, when the user attempts to connect to the database, they receive an 'ORA-01045: user DEV01 lacks CREATE SESSION privilege; logon denied' error.
What is the most likely cause of this issue?