1Z0-082 - Oracle Database Administration I Administering User Security Questions and Answers — Questions and Answers
Question 1: A database administrator needs to create a new user named 'APP_USER' who will own application objects. Which SQL statement correctly creates the user, assigns a default tablespace for their objects, and allows them to use 100M of space in that tablespace?
- CREATE USER app_user IDENTIFIED BY a_password DEFAULT TABLESPACE app_data QUOTA 100M ON app_data; (Correct answer)
- CREATE NEW USER app_user WITH PASSWORD a_password TABLESPACE app_data QUOTA 100M;
- ADD USER app_user IDENTIFIED BY a_password DEFAULT TABLESPACE app_data;
- CREATE USER app_user WITH a_password ON app_data QUOTA 100M;
Correct answer: CREATE USER app_user IDENTIFIED BY a_password DEFAULT TABLESPACE app_data QUOTA 100M ON app_data;
The correct syntax for creating a user in Oracle includes the `CREATE USER` keywords, followed by the username, the `IDENTIFIED BY` clause for the password, the `DEFAULT TABLESPACE` clause to specify where the user's objects will be stored, and the `QUOTA ... ON` clause to set a space limit within that tablespace.
Question 2: A new developer, 'John', has been created with the `CREATE USER john IDENTIFIED BY ...` command. When John tries to connect to the database, he receives an `ORA-01045: user JOHN lacks CREATE SESSION privilege; logon denied` error. What is the most likely cause of this error?
- The user 'john' has an expired password.
- The user 'john' was not granted the necessary privilege to connect to the database. (Correct answer)
- The user 'john' does not have a quota on any tablespace.
- The database listener is not running.
Correct answer: The user 'john' was not granted the necessary privilege to connect to the database.
When a user is created, their privilege domain is empty. To be able to log on to the database, a user must be granted the `CREATE SESSION` system privilege. The error message explicitly states that this privilege is lacking.
Question 3: Which of the following is a key benefit of using roles to manage database security?
- Roles can be assigned their own storage quotas on tablespaces.
- Roles allow for password-protected access to specific schemas.
- Roles simplify privilege management by grouping multiple privileges that can be granted to users or other roles collectively. (Correct answer)
- Roles automatically audit all activities performed by users who are granted the role.
Correct answer: Roles simplify privilege management by grouping multiple privileges that can be granted to users or other roles collectively.
Roles are designed to simplify security administration. Instead of granting a large number of individual privileges to each user, you can group related privileges into a role and then grant that single role to users. This makes granting, revoking, and managing privileges much more efficient.
Question 4: A database administrator is tasked with enforcing a stricter password policy. They need to ensure that user accounts are locked after 3 failed login attempts. Which database security mechanism should be used to configure this setting?
- System Privileges
- Object Privileges
- Database Triggers
- Profiles (Correct answer)
Correct answer: Profiles
Profiles are used to manage password policies and resource limits for users. The `FAILED_LOGIN_ATTEMPTS` parameter within a profile can be set to lock an account after a specified number of consecutive unsuccessful login attempts.
Question 5: Which of the following is an example of a system privilege?
- SELECT on the EMPLOYEES table
- UPDATE on the ORDERS table
- CREATE TABLE (Correct answer)
- EXECUTE on the CALC_BONUS procedure
Correct answer: CREATE TABLE
System privileges grant the ability to perform actions on a type of object or to perform a system-level action, such as `CREATE TABLE`, `CREATE VIEW`, or `CREATE SESSION`. In contrast, object privileges grant permission to perform a specific action (like SELECT, INSERT, UPDATE, DELETE) on a specific, existing object (like a particular table or view).
Question 6: A database administrator wants to create a role named `APP_DEVELOPER` and grant it the ability to create tables and views. Additionally, any user granted this role should be able to grant the role to other users. Which set of SQL statements accomplishes this?
- CREATE ROLE app_developer; GRANT CREATE TABLE, CREATE VIEW TO app_developer WITH ADMIN OPTION; (Correct answer)
- CREATE ROLE app_developer; GRANT CREATE TABLE, CREATE VIEW ON app_developer;
- NEW ROLE app_developer; GRANT CREATE TABLE, CREATE VIEW TO app_developer WITH GRANT;
- CREATE ROLE app_developer WITH ADMIN OPTION; GRANT CREATE TABLE, CREATE VIEW TO app_developer;
Correct answer: CREATE ROLE app_developer; GRANT CREATE TABLE, CREATE VIEW TO app_developer WITH ADMIN OPTION;
First, the `CREATE ROLE` statement is used to create the role. Then, the `GRANT` statement is used to assign the `CREATE TABLE` and `CREATE VIEW` system privileges to the new role. The `WITH ADMIN OPTION` clause is specified to allow any grantee of this role to further grant the role to other users.
