Aco 12

Front 1

1. Which of the following statements creates an Oracle account but lets the operating system authenticate logons?
A.create user ops$admin identified by os;
B.create user ops$admin identified externally;
C.create user ops$admin nopassword;
D.create user ops$admin authenticated by os;

Back 1

1. B. Authentication by the operating system is called external authentication, and the Oracle account name must match the operating-system
account name prefixed with the OS_AUTHENT_PREFIX string.

Front 2

2. If you want to capture the SQL statement and bind variables when performing statement auditing, which value should the AUDIT_TRAIL parameter have?
A. NONE
B. DB
C. DB,EXTENDED
D. OS
E. OS,EXTENDED

Back 2

2. C. The AUDIT_TRAIL parameter with the value DB,EXTENDED enables capturing SQL statements and bind variables in auditing. OS,EXTENDED is not a valid
value for AUDIT_TRAIL.

Front 3

3. Which of the following statements gives user desmond the ability to alter table gl.accounts?
A.grant alter on gl.accounts to desmond;
B.grant alter to desmond on gl.accounts;
C.grant alter table to desmond;
D.allow desmond to alter table gl.accounts;

Back 3

3.A.Alteringatableinanotheruser’sschemarequireseithertheobjectprivilegeALTERonthatobjectorthesystemprivilegeALTER ANY TABLE.OptionA has the correct syntax for granting the object privilege on ALTER gl.accounts to user desmond. Although option C would allow user desmond to alter his own tables, he would need the ALTER ANY TABLE privilege to alter another user’s table.

Front 4

4. Which of the following statements gives user desmond the ability to alter table gl.accounts as well as give this ability to other accounts?
A.grant alter any table with grant option to desmond;
B.grant alter on gl.accounts to desmond with admin option;
C.grant alter any table to desmond with grant option;
D.grant alter any table to desmond with admin option;

Back 4

4. D. Either the ALTER ANY TABLE system privilege or the ALTER object privilege is required. Conferring the ability to further grant the privilege requires the keywords WITH ADMIN OPTION for system or role privileges or the keywords WITH GRANT OPTION for object privileges. Only option D has both the correct
syntax and the correct keywords.

Front 5

5. Examine the CREATE USER statement, and choose which of the following options best applies. CREATE USER JOHN IDENTIFIED BY JOHNNY
DEFAULT TABLESPACE INDEX01
PASSWORD EXPIRE
QUOTA UNLIMITED ON DATA01 QUOTA UNLIMITED ON INDEX01; GRANT CONNECT TO JOHN;
A. JOHN will not be able to log in to the database using SQL*Plus until the DBA changes his password.
B. JOHN is authenticated by the database.
C. When creating tables, if JOHN did not specify the TABLESPACE clause, the table will be created on the DATA01 tablespace.
D. Specifying unlimited space quota on INDEX01 is a redundant step since INDEX01 is JOHN’s default tablespace.

Back 5

5. B. JOHN will be able to log in to the database using SQL*Plus, and Oracle will prompt for new password when John logs in the first time. Since John’s default tablespace is INDEX01, the tables and indexes created will be on the INDEX01 tablespace if the TABLESPACE clause is omitted. Though INDEX01 is the default tablespace, to create objects on INDEX01 or any other tablespace, a specific space quota needs to be defined, or the user should have the UNLIMITED TABLESPACE system privilege.

Front 6

6. User system granted SELECT on sh.products to user ian using WITH GRANT OPTION. Ian then granted SELECT on sh.products to user stuart. Ian has left the company, and his account has been dropped. What happens to Stuart’s privileges on sh.products?
A. Stuart loses his SELECT privilege on sh.products.
B. Stuart retains his SELECT privilege on sh.products.
C. Stuart loses his SELECT privilege if Ian was dropped with the CASCADE REVOKE option.
D. Stuart retains his SELECT privilege if Ian was dropped with the NOCASCADE REVOKE option.

Back 6

6. A. When object privileges are granted through an intermediary, they are implicitly dropped when the intermediary is dropped. CASCADE REVOKE and NOCASCADE REVOKE are not part of the GRANT statement syntax.

Front 7

7. User system granted SELECT ANY TABLE to user ian using WITH ADMIN OPTION. Ian then granted SELECT ANY TABLE to user stuart. Ian has left the company, and his account has been dropped. What happens to Stuart’s privileges?
A. Stuart loses his privileges.
B. Stuart retains his privileges.
C.StuartloseshisprivilegesifIanwasdroppedwiththeCASCADE REVOKEoption.
D.StuartretainshisprivilegesifIanwasdroppedwiththeNOCASCADE REVOKEoption.

Back 7

7. B. When system privileges are granted through an intermediary, they are not affected when the intermediary is dropped. CASCADE REVOKE and NOCASCADE REVOKE are not part of the GRANT statement syntax.

Front 8

8. Which of the following system privileges can allow the grantee to masquerade as another user and therefore should be granted judiciously?
A.CREATE ANY JOB
B.ALTER USER
C.CREATE ANY PROCEDURE
D. All of the above

Back 8

8. D. The CREATE ANY JOB and CREATE ANY PROCEDURE system privileges allow the grantee to create and run programs with the privileges of another user. The ALTER USER privilege allows the grantee to change a user’s password, connect as that user, and then change the password back. These are all powerful system privileges and should be restricted to as few administrative users as practical.

Front 9

9. Which of the following statements enables the role user_admin in the current session?
A.alter session enable role user_admin;
B.alter session set role user_admin;
C.alter role user_admin enable;
D.set role user_admin;

Back 9

9.D.TheSET ROLEstatementenablesordisablesrolesinthecurrentsession.


Front 10

10. Which of the following SQL statements allows user augustin to use the privileges associated with the password-protected role info_czar that has been granted to him?
A.set role all;
B.alter user augustin default role all;
C.alter session enable role info_czar;
D.alter session enable info_czar identified by brozo

Back 10

10. B. To enable a password-protected role, you need to either execute a SET ROLE statement specifying the password or alter the user to make the
role a default role. Default roles do not require a set role statement or a password to become enabled.

Front 11

11. By default, how much space can any account use for a new table?
A.None
B. Up to the current free space in the tablespace
C. Unlimited space, including autoextends
D. Up to the default quota established at tablespace creation time

Back 11

11. A. By default, user accounts have no quota in any tablespace. Before a user can create a table or an index, you need to either give the user a quota in one or more specific tablespaces or grant the UNLIMITED TABLESPACE system privilege to give an unlimited quota (including autoextends) in all tablespaces.

Front 12

12. Which of the following SQL statements results in a disconnection after a session is idle for 30 minutes?
A.alter session set idle_timeout=30;
B.alter session set idle_timeout=1800;
C.alter profile default limit idle_time 30;
D.alter profile default set idle_timeout 30;

Back 12

12. C. Profiles limit the amount of idle time, CPU time, logical reads, or other resource-oriented session limits. Option C uses the correct syntax to limit idle time for a session to 30 minutes.

Front 13

13. Which of the following prevents a user from reusing a password when they change their password?
A. Setting the initialization parameter NO_PASSWORD_REUSE to TRUE
B. Altering that user’s profile to UNLIMITED for PASSWORD_REUSE_TIME and 1 for PASSWORD_REUSE_MAX
C. Altering that user’s profile to UNLIMITED for both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX
D. Using a password verify function to record the new password and comparing the new passwords to those recorded previously

Back 13

13. B. Although option D could also work, it involves storing the passwords in a table in the database, which could be a security concern. It also takes a lot more effort to configure and maintain. The better technique is to use the standard database profile features PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX. Setting one of these profile parameters to UNLIMITED and the other to a specific value prevents passwords from being reused. If both of these profile parameters are set to UNLIMITED, these parameters are essentially disabled. There is no initialization parameter called NO_PASSWORD_REUSE.

Front 14

14. Examine the code, and choose the best option that describes the reason for error. CREATE USER JOHN IDENTIFIED BY JOHN1;
CREATE ROLE HR_QUERY;
GRANT CONNECT, OEQUERY, SELECT ANY TABLE TO HR_QUERY;
ALTER USER JOHN DEFAULT ROLE ALL EXCEPT HR_QUERY; GRANT HR_QUERY TO JOHN;
CONNECT JOHN/JOHN1
SELECT COUNT(*) FROM HR.EMPLOYEES;
Error: ORA-01031: insufficient privileges
A. John needs the SELECT_CATALOG_ROLE privilege.
B. HR_QUERY is not a default role for John.
C. The SELECT privilege on the HR.EMPLOYEES table is not granted to JOHN or HR_QUERY.
D. John should enable the role using the SET ROLE statement and a password.

Back 14

14. B. Since HR_QUERY has the SELECT ANY TABLE privilege, no other privilege is required to query user tables in the database. To avoid the error, HR_QUERY must be defined as a default role for John, or John should use the SET ROLE statement. A password is not needed for SET ROLE because the role is not password protected.

Front 15

15. You created a database user using the following statement. Which option will connect the user successfully to the database? CREATE USER JOHN IDENTIFIED BY John1;
GRANT CONNECT TO JOHN;
A.CONNECT JOHN/JOHN1
B.CONNECT JOHN/john1
C.CONNECT john/John1
D. All of the above

Back 15

15. C. In Oracle 11g user passwords are case sensitive. The username is not case sensitive if you did not enclose it in double quotes.

Front 16

16. What is the default value for the AUDIT_TRAIL parameter?
A. NONE
B. DB
C. DB,EXTENDED
D. XML

Back 16

16. B. In Oracle 11g, the default value for AUDIT_TRAIL parameter is DB. By default, Oracle 11g enables several key database-auditing features.

Front 17

17. Which of the following SQL statements limits attempts to guess passwords by locking an account after three failed logon attempts?
A.alter profile default limit failed_login_attempts 3;
B.alter system set max_logon_failures = 3 scope=both;
C.alter user set failed_login_attempts = 3;
D.alter system set failed_login_attempts = 3 scope=both;

Back 17

17. A. You limit the number of failed logon attempts with a profile.

Front 18

18. Where can the database write audit_trail records?
A. In a database table
B. In a file outside the database
C. Both in the database and in an operating-system file
D. Either in the database or in an operating-system file

Back 18

18. D. The destination of audit_trail records is controlled by the initialization parameter audit_trail. Setting this parameter to DB or DB,EXTENDED causes the audit trail
to be written to a database table. Setting the parameter to OS or XML causes the audit trail to be written to an operating-system file.

Front 19

19. User JAMES has a table named JOBS created on the tablespace USERS. When you issue the following statement, what effect will it have on the JOBS table?
ALTER USER JAMES QUOTA 0 ON USERS;
A. No more rows can be added to the JOBS table.
B. No new blocks can be allocated to the JOBS table.
C. No new extents can be allocated to the JOBS table.
D. The table JOBS cannot be accessed.
E. The table is truncated.

Back 19

19. C. When a space quota is exceeded or quota is removed from a user on a tablespace, the tables remain in the tablespace, but no new extents can be allocated. New rows can be inserted into the table as long as the table does not require Oracle to allocate a new extent in the table.

Front 20

20. How do you manage fine-grained auditing?
A. With the AUDIT and NOAUDIT statements
B. With the DBMS_FGA package
C. With the GRANT and REVOKE statements
D. With the CREATE, ALTER, and DROP statements

Back 20

20. B. Fine-grained auditing is managed using the DBMS_FGA package. The AUDIT and NOAUDIT statements are used to manage statement, privilege, and object auditing. The GRANT and REVOKE statements are used to manage system, object, and role privileges. The CREATE, ALTER, and DROP statements are used to manage several types of database objects and settings.