Fundamental I Ch9 - Managing Users, Security, and Globalization Support

Chapter 9 - Managing Users, Security, and Globalization Support

OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla

This is the last chapter


Review Questions


1. Profiles cannot be used to restrict which of the following?


A. CPU time used
B. Total time connected to the database
C. Maximum time a session can be inactive
D. Time spent reading blocks
----
There is no resource parameter in the profile definition to monitor the time spent reading blocks, but you can restrict the number of blocks read per SQL statement or per session.
Ans: D.


2. Which command is used to assign a profile to an existing user?


A. ALTER PROFILE.
B. ALTER USER.
C. SET PROFILE.
D. The profile should be specified when creating the user; it cannot be changed.
----
You use the PROFILE clause in the ALTER USER command to set the profile for an existing user. You must have the ALTER USER privilege to do this.
Ans: B.


How does one enforce strict password control?



3. Which resource is not used to calculate the COMPOSITE_LIMIT?


A. PRIVATE_SGA
B. CPU_PER_SESSION
C. CONNECT_TIME
D. LOGICAL_READS_PER_CALL
----
Call-level resources are not used to calculate the COMPOSITE_LIMIT. You can set the resource cost of the four resources (the fourth is LOGICAL_READS_PER_SESSION) using the ALTER RESOURCE COST command.
Ans: D


4. Choose the option that is not true.


A. Oracle creates a profile named DEFAULT when the database is created.
B. Profiles cannot be renamed.
C. DEFAULT is a valid name for a profile resource.
D. The SESSIONS_PER_USER resource in the DEFAULT profile initially has a value of 5.
---
All resources in the default profile have a value of UNLIMITED when the database is created. You can change these values.
Ans: D.


5. What is the maximum number of profiles that can be assigned to a user?


A. 1
B. 2
C. 32
D. Unlimited
----
A user can have only one profile assigned. You can query the profile assigned to a user from the DBA_USERS view.
Ans: A.


6. What happens when you create a new user and do not specify a profile?


A. Oracle prompts you for a profile name.
B. No profile is assigned to the user.
C. The DEFAULT profile is assigned.
D. The SYSTEM profile is assigned.
---
The DEFAULT profile is created when the database is created and is assigned to users if you do not specify a profile for the new user. Before you can assign a profile, you must create the user in the database.
Ans: C.


7. Which resource specifies the value in minutes?


A. CPU_PER_SESSION
B. CONNECT_TIME
C. PASSWORD_LOCK_TIME
D. All the above
---
CONNECT_TIME is specified in minutes, CPU_PER_SESSION is specified in hundredths of a second, and PASSWORD_LOCK_TIME is specified in days.
Ans: B.


8. Which password parameter in the profile definitions can restrict the user from using the old password for 90 days?


A. PASSWORD_REUSE_TIME
B. PASSWORD_REUSE_MAX
C. PASSWORD_LIFE_TIME
D. PASSWORD_REUSE_DAYS
---
PASSWORD_REUSE_TIME specifies the number of days required before the old password can be reused; PASSWORD_REUSE_MAX specifies the number of password changes required before a password can be reused. At least one of these parameters must be set to UNLIMITED.
Ans: A.


9. Which dictionary view shows the password expiration date for a user?


A. DBA_PROFILES
B. DBA_USERS
C. DBA_PASSWORDS
D. V$SESSION
---
The DBA_USERS view shows the password expiration date, account status, and locking date along with the user’s tablespace assignments, profile, creation date, and so on.
Ans: B.


10. Which clause in the CREATE USER command can be used to specify no limits on the space allowed in tablespace APP_DATA?

A. DEFAULT TABLESPACE
B. UNLIMITED TABLESPACE
C. QUOTA
D. PROFILE
---
You use the QUOTA clause to specify the amount of space allowed on a tablespace; you can specify a size or UNLIMITED. The user will have unlimited space if the system privilege UNLIMITED TABLESPACE is granted.
Ans: C.


11. User JAMES has a table named JOBS created on the tablespace USERS. When you issue the following statement, what effect it will 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 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.
----
When the 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.
Ans: C


12. Which view would you query to see whether John has the CREATE TABLE privilege?


A. DBA_SYS_PRIVS
B. DBA_USER_PRIVS
C. DBA_ROLE_PRIVS
D. DBA_TAB_PRIVS
---
CREATE TABLE is a system privilege. You can query system privileges from DBA_SYS_PRIVS or USER_SYS_PRIVS.
Ans: A.


13. Which clause should you specify to enable the grantee to grant the system privilege to other users?


A. WITH GRANT OPTION
B. WITH ADMIN OPTION
C. CASCADE
D. WITH MANAGE OPTION
---
The WITH ADMIN OPTION specified with system privileges enables the grantee to grant the privileges to others, and the WITH GRANT OPTION specified with object privileges enables the grantee to grant the privilege to others.
Ans: B.


14. Which of the following is not a system privilege?


A. SELECT
B. UPDATE ANY
C. EXECUTE ANY
D. CREATE TABLE
----
SELECT, INSERT, UPDATE, DELETE, EXECUTE, and REFERENCES are object privileges. SELECT ANY, UPDATE ANY, and so on are system privileges.
Ans: A.


15. Which data dictionary view can you query to see whether a user has the EXECUTE privilege on a procedure?


A. DBA_SYS_PRIVS
B. DBA_TAB_PRIVS
C. DBA_PROC_PRIVS
D. SESSION_PRIVS
---
The DBA_TAB_PRIVS, USER_TAB_PRIVS, and ALL_TAB_PRIVS views show information about the object privileges.
Ans: B


16. To grant the SELECT privilege on the table CUSTOMER to all users in the database, which statement would you use?


A. GRANT SELECT ON CUSTOMER TO ALL USERS;
B. GRANT ALL ON CUSTOMER TO ALL;
C. GRANT SELECT ON CUSTOMER TO ALL;
D. GRANT SELECT ON CUSTOMER TO PUBLIC;
----
PUBLIC is the group or class of database users to which all users of the database belong.
Ans: D.


17. Which role in the following list is not a predefined role from Oracle?


A. SYSDBA
B. CONNECT
C. IMP_FULL_DATABASE
D. RESOURCE
---
SYSDBA and SYSOPER are not roles; they are system privileges.
Ans: A.


18. How do you enable a role?


A. ALTER ROLE
B. ALTER USER
C. SET ROLE
D. ALTER SESSION
----
You use the SET ROLE command to enable or disable granted roles for the user. The view SESSION_ROLES shows the roles that are enabled in the session. All default roles are enabled when the user connects to the database.
Ans: C.


19. What is accomplished when you issue the following statement?


ALTER USER JOHN DEFAULT ROLE ALL;

A. John is assigned all the roles created in the database.
B. Future roles granted to John will not be default roles.
C. All of John’s roles are enabled, except the roles with passwords.
D. All of John’s roles are enabled when he connects to the database.
---
Ans D. Default roles are enabled when a user connects to the database even if the roles are password authorized.



20. Which command defines CONNECT and RESOURCE as the default roles for user JAMES?


A. ALTER USER
B. ALTER ROLE
C. SET ROLE
D. SET PRIVILEGE
---
Ans: A. The ALTER USER command defines the default role(s) for a user.


21. Which data dictionary view shows the database character set?


A. V$DATABASE
B. NLS_DATABASE_PARAMETERS
C. NLS_INSTANCE_PARAMETERS
D. NLS_SESSION_PARAMETERS
-----
Ans: B. The NLS_DATABASE_PARAMETERS view shows the database character set and all the NLS parameter settings. The character set cannot be changed at the instance or session level, so the character set information does not show up in the NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS views.


22. Choose two NLS parameters that cannot be modified using the ALTER SESSION statement.


A. NLS_CHARACTERSET
B. NLS_SORT
C. NLS_NCHAR_CHARACTERSET
D. NLS_TERRITORY
----
You cannot change the character set after creating the database. The CHARACTER SET and NATIONAL CHARACTER SET clauses are used in the CREATE DATABASE command.
Ans: A and C.



Manage Users