OCA Oracle 10g Ch6 User Administration and Security
Review Questions
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;
----
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.
Ans: B
2. Which of the following types of statements can use a temporary tablespace?
A. An index creation
B. SQL statements with a GROUP BY clause
C. A hash join operation
D. All of the above
---
Any operation that requires a large sort or other creation of temporary segments will create,
alter, and drop those temporary segments in the TEMPORARY tablespace.
ANs: D.
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;
---
Altering a table in another user’s schema requires either the object privilege ALTER on that
object or the system privilege ALTER ANY TABLE. Option A 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.
Ans: A.
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;
--
Either the ALTER ANY TABLE system privilege or the ALTER object privilege is required. To
confer 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.
Ans: D.
5. The following SQL statement will allow user
oe.orders_seq?
GRANT ALL ON oe.orders_seq TO
A. Select the next value from oe.orders_seq.
B. Alter sequence oe.orders_seq to change the next value.
C. Change the number of sequence numbers that will be cached in memory.
D. Both A and C.
E. All of the above.
---
The ALL option for a sequence includes the SELECT and ALTER privileges. The SELECT privilege
lets
the cache but not the next value.
Ans: D.
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 is
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.
--
When object privileges are granted through an intermediary, they are implicitly dropped when
the intermediary is dropped. There are no CASCADE REVOKE or NOCASCADE REVOKE options.
Ans: A.
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 is
dropped. What happens to Stuart’s privileges?
A. Stuart loses his privileges.
B. Stuart retains his privileges.
C. Stuart loses his privileges if Ian was dropped with the CASCADE REVOKE option.
D. Stuart retains his privileges if Ian was dropped with the NOCASCADE REVOKE option.
--
When system privileges are granted through an intermediary, they are not affected when the
intermediary is dropped. There are no CASCADE REVOKE or NOCASCADE REVOKE options.
Ans: B.
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
----
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.
Ans: D
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;
---
Ans: D. The SET ROLE statement enables or disables roles in the current session.
10. Which of the following SQL statements allows user augustin to use the privileges associated
with the password-protected role info_czar, which 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
----
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.
Ans: B.
11. By default, how much tablespace 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
---
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 unlimited quota (including autoextends) in
all tablespaces.
Ans: A
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 limit idle_time 30;
D. alter profile set idle_timout 30;
---
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.
Ans: C
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 compare the new passwords
to those recorded previously
---
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_RESUSE_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.
Ans B
14. How can you prevent someone from using an all-numeric password?
A. Set the initialization parameter PASSWORD_COMPLEXITY to ALPHANUM.
B. Alter that user’s profile setting PASSWORD_COMPLEXITY to ALPHNANUM.
C. Alter the user’s profile to use a password verify function that performs REGEX comparisons
to validate the password.
D. There is no mechanism that lets you prevent an all-numeric password.
---
There are no standard password complexity settings in either the initialization parameters or
profiles. A password verify function can validate new passwords against any rules that you can
code in PL/SQL, including regular expression comparisons.
Ans: C.
15. Which of the following is not an object privilege on a table?
A. SELECT
B. DEBUG
C. REFERENCES
D. READ
---
The object privileges on a table include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX,
REFERENCES, and DEBUG, but not READ. READ is a valid object privilege, but only on a directory—
a database object that is outside the scope of the OCA exam.
Ans: D
16. Which of the following statements about user administration and security is the most true?
Select the best answer.
A. Password-protected roles require a password before they can become enabled.
B. You can disable any role that you find in your session_roles view.
C. If you execute alter profile student limit idle_time 10; and then execute alter
user scott profile student;, then user scott will be disconnected from future sessions
after 10 minutes of idle time.
D. You can limit a table to a maximum size on disk.
---
This question is tricky. All the options look correct and in fact are mostly true. But option D
is the most correct option. Password-protected roles that are included in a user’s default role list
are enabled by default and do not need a password. Your session_roles view contains both
roles granted directly to you and those you inherit through another role. You cannot disable roles
that you inherit by way of another role without disabling the role granted directly to you. For
example, you cannot disable SCHEDULER_ADMIN without disabling DBA. Limiting a profile to 10
minutes of idle time will cause future sessions to timeout after 10 idle minutes, but only if the initialization
parameter RESOURCE_LIMIT is set to TRUE (the default is FALSE). Because each schema
owner can be assigned tablespace quotas, you can effectively limit all of a user’s segments to a maximum
size, thus setting an upper limit on the size of any single table.
Ans: D
17. Which of the following SQL statements limit 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;
---
Ans: A. You limit the number of failed logon attempts with a profile.
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
---
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 causes the audit trail to be written to an operating system file.
Ans: D.
19. Which of the following activities can be audited?
A. Unsuccessful deletions from the audit_trail table
B. Unsuccessful selects from the employee_salary table
C. All GRANT and REVOKE statements on procedures executed by user system
D. All of the above
---
Audit unsuccessful deletions from the audit table with the following SQL:
AUDIT DELETE ON sys.aud$ WHENEVER NOT SUCCESSFUL;
Audit unsuccessful selects from all tables with the following:
AUDIT NOT EXISTS;
Audit all grant and revoke statements on procedures executed by user SYSTEM with the following:
AUDIT grant procedure BY system;
Ans: D
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
----
ANs: B
Fine-grained auditing is managed using the DBMS_FGA package. The AUDIT and NOAUDT statements
are used to manage statement, privilege, or 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.
No comments:
Post a Comment