SQL Chap 10 - User Access and Security

This is the last chapter of the book.


Review Questions


1. Which of the following assertions most correctly describes the privileges in force after the SQL below is executed?

connect athos/musketeer
grant select,insert,update,delete on
   athos.services to porthos
   with grant option;
grant all on athos.services to aramis;
connect porthos/musketeer
grant select,delete,insert,update on
   athos.services to aramis
   with grant option;
connect athos/musketeer
revoke all on athos.services from aramis;

A. Aramis can create an index on athos.services.
B. Aramis has no privileges on athos.services.
C. Aramis can select from athos.services.
D. Aramis can select, insert, update, and delete rows from athos.services.
-----
Object privileges can be obtained from more than one grantor. To completely remove object privileges from an account, all grantors must revoke these privileges. Aramis was granted the four privileges SELECT, INSERT, UPDATE, and DELETE on athos.services from Porthos, as well as ALL (SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, and REFERENCE) from Athos. After Athos revokes the privileges that he granted, Aramis still retains the privileges that were granted from Porthos.
Ans: D.


2. Which of the following assertions most correctly describes the privileges in force after the SQL below is executed?


connect system/manager
grant select any table to jon with admin option;
grant select any table to jason;
connect jon/seekrit
grant select any table to jason;
revoke select any table from jason;

A. Jason can select from any table regardless of any individual table privileges.
B. Jason can only select from tables that he has been granted SELECT privileges on or has acquired via a role.
C. Jason can only select from his own tables.
D. Jason continues to enjoy the SELECT ANY TABLE privilege.
---
Oracle does not retain the grantor on system privileges, so if anyone revokes a system privilege, that privilege is gone, even if the grantee obtained it from more than one grantor. This behavior is the same as role privileges, but different from object privileges, such as SELECT, INSERT, or EXECUTE.

Ans: B.


3. You need to create a database-authenticated account named selena. This account should have the password welcome, and Selena should be required to change this password as soon as she connects. Which of the following SQL statements most completely meets these requirements?


A. create user selena password welcome expired;
B. create user selena identified by welcome expire;
C. create user selena identified by welcome expire password;
D. create user selena identified by welcome password expire;

----
You create a database-authenticated account with the CREATE USER statement. You assign the password with the IDENTIFIED BY clause and expire the password with the PASSWORD EXPIRE clause. When the password expires, the user will be required to change it on the next connection to the database.
Ans: D.


4. You have an account called sales that owns the tables for an application. You have created the tables and need to ensure that no one will be able to connect as this account. Which of the following SQL statements most completely meets these requirements?


A. alter user sales account lock;
B. alter user sales disable account;
C. alter user sales lock account;
D. alter account sales lock;
----
To lock an account, disabling logons for that account, you alter the account with the ACCOUNT LOCK option.
Ans: A.


5. Which of the following queries will include the privileges on another user's procedure that you have granted to a third party?


A. SELECT owner, proc_name, grantor, grantee FROM all_sql_privs;

B. SELECT owner, sql_name, grantor, grantee FROM all_sql_privs;

C. SELECT owner, table_name, grantor, grantee, privilege FROM all_tab_privs_made;

D. SELECT owner, sql_name, grantor, grantee FROM user_table_privs;
----
Ans C. All of the other data dictionary tables are fictitious.


6. You have a few developers who insist on connecting to the database as the well-known table-owning account HR, which is reserved for system testing. These developers need to periodically connect to the HR account to promote changes, but the corporate guidelines say that development should be done in each of the developer's personal accounts so they don't conflict with each other. The development manager has asked you to enable any database settings that might help discourage these developers from all connecting to the HR account at the same time. Which of the following options will best assist the development manager?


A. Give the development manager SELECT privileges on the V$SESSION table, so she can monitor her team's connection activity.
B. Lock the HR account and make the developers come to a DBA when they need to promote changes to system test.
C. Use a profile to limit the number of concurrent sessions for user HR to one.
D. Create an after logon trigger that causes the logon to fail if someone else is logged into the HR account.
----
This one is really tricky. All of the options would work technically. However, the development manager probably has better things to do than monitor who on her team is connecting as which user. Unless the corporate standards say a DBA must promote changes to system test, the DBA probably has better things to do than slow down the development efforts by getting involved in promotions to system test. The after logon trigger is a clever bit of engineering, but it actually does the same thing as the profile with added complexity, overhead, and maintenance.
Ans: C


7. Which of the following actions cannot be done with an ALTER USER statement?


A. Expire a password.
B. Enable DBA privileges.
C. Set the default tablespace for tables.
D. Set different default tablespaces for indexes and tables.
----
It would be nice, but Oracle does not (yet) let you set a default tablespace for indexes. DBA privileges can be enabled by default with an ALTER USER statement if the role was granted to the user previously and set to disabled.
Ans: D.


8. Which init.ora parameter will limit the number of concurrent session from non-DBA accounts to 16?


A. sessions=16
B. license_max_sessions =16
C. processes=16
D. max_concurrent_logons=16
-----
Option A is a hard limit that includes restricted session logons. The processes setting includes such non-logon processes as pmon, lgwr, and parallel I/O slaves. The max_concurrent_logons parameter is fictitious. When the number of logon sessions reaches license_max_sessions, only restricted session (DBA) logons are allowed.
Ans: B.


9. What cannot be done with a profile?


A. Limit the number of physical reads per session to 100,000.
B. Limit the number of logical reads per session to 1,000,000.
C. Limit passwords to expire after 90 days.
D. Limit the duration of each session to 9 hours.
----
You can limit a number of resources with a profile, but the number of physical reads can be dependent on how warm the cache is and cannot be limited via a profile.
Ans: A.


10. Which of the following assertions most correctly describes the privileges in force after the SQL below is executed?


connect system/manager
grant dba to arsal with admin option;
grant dba to gretchen;
connect arsal/troodon
grant dba to gretchen;
revoke dba from gretchen;

A. Gretchen can exercise DBA privileges.

B. Gretchen can grant DBA privileges to other accounts.

C. Arsal loses DBA privileges.

D. Gretchen loses DBA privileges.
----
Oracle does not retain the grantor on role privileges, so if anyone revokes a role privilege, that privilege is gone, even if the grantee obtained it from more than one grantor. This behavior is the same as system privileges, but different from object privileges, such as SELECT, INSERT, or EXECUTE.
Ans: D.


11. Which statement will configure the principle_user profile to lock any account after three failed logon attempts?


A. alter profile principle_user set failed_logon_attempts=3;

B. alter profile principle_user limit failed_logon_attempts 3;

C. alter principle_user profile set failed_logon_attempts=3;

D. alter profile principle_user lock account when failed_logon_attempts=3;

E. You can't limit failed logon attempts.

-----
B. Know the syntax for changing resource limits in a profile.
Ans: B.


12. Which of the following SQL statements will give user Nikki the privileges to assign SELECT authority on HR.EMPLOYEES to other user accounts?


A. grant select on hr.employees to nikki;

B. grant select on hr.employees to nikki with grant option;

C. grant select on hr.employees to nikki with admin option;

D. grant select on hr.employees to nikki cascade;
----
The WITH GRANT OPTION clause is used to give the grantee the ability to grant the privilege to other accounts. The WITH ADMIN OPTION does the same thing with system and role privileges.
Ans: B.


13. Which statement will set a five-minute limit to the maximum time that a user with the default profile can remain idle?


A. alter user default set profile max_idle_time=300;

B. alter profile default limit max_idle_time 300;

C. alter profile default limit idle_time 5;

D. alter profile default limit idle_time 300;

-----
The ALTER PROFILE statement is used to change a profile, and the idle_time parameter is set in minutes, not seconds.
Ans: C.


14. Which init.ora parameter will assist you in enforcing named user licensing, by limiting the number of user accounts that can be created in your database?


A. max_users
B. license_max_users
C. max_named_users
D. named_users_max
----
license_max_users can be used to limit the number of user accounts created. The other options are fictitious.
Ans: B.


15. Which of the following statements will give user Zachary the privilege to modify only the COMMENTS column in the CUSTOMER table?


A. grant update on customer(comments) to zachary;
B. grant update (comments) on customer to zachary;
C. grant update on customer.comments to zachary;
D. grant update on customer columns(comments) to zachary;
---
Any additional columns would appear as a comma-delimited list within the parentheses.
Ans: B.


16. Mary has granted INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION, and DELETE WITH GRANT OPTION privileges on the CHART_OF_ACCOUNTS table to Charlie. Charlie is changing jobs and should not have the grant option. How can Mary leave the INSERT, UPDATE, and DELETE privileges, but remove the WITH GRANT OPTION? Mary also wants to ensure that whomever Charlie granted the privileges to will retain the privileges.


A. Grant the privileges on CHART_OF_ACCOUNTS without the grant option, and then revoke the privileges WITH GRANT OPTION.
B. Simply revoke the grant option.
C. Revoke the privileges, so that the grant option goes away, and then grant the privileges without the grant option.
D. Extract all the grants that Charlie made from the data dictionary, revoke the privileges on CHART_OF_ACCOUNTS, grant the privileges on CHART_OF_ACCOUNTS without the grant option, and regrant all the extracted privileges.

-----
There is no simple and easy way to remove the WITH GRANT OPTION while retaining the privilege. Revoking a privilege from someone will cascade through and revoke it from all grantees, so it would be crucial to first extract these privileges before revoking them.
Ans: D.


17. You need to report on all of the column privileges that you have made on your BONUS table. You must include the name of the account receiving the privilege, which column, and which privilege. Which of the following statements will accomplish this task?


A.
select grantor, table_name, column_name, privilege
from user_col_privs_recd
where table_name ='BONUS';

B.
select * from all_col_privs_made
where table_name='BONUS';

C.
select table_name, column_name, privilege, grantee
from user_col_privs_made
where table_name ='BONUS';

D.
select grantee, table_name, column_name, privilege
from all_tab_col_privs
where owner=user and table_name='BONUS';

----
The grantee is the recipient of the privilege. Every one of the ALL_DATA dictionary views contains not only the user's own objects, but also those that the user has access to, so ALL_COL_PRIVS_MADE may contain privileges on other schemas' tables. ALL_TAB_COL_PRIVS is not a valid data dictionary view.
Ans: C.


18. EMP is a table. Mary is a user. Sales_mgr is a role. Which one of the following statements will fail?


A. grant sales_mgr to mary with admin option;
B. grant read on emp to mary;
C. grant insert,update,delete on emp to mary with grant option;
D. grant reference on emp to mary;
---
The READ privilege is valid only on directories.
Ans: B.


19. Which of the following table privileges cannot be granted to a role (can only be granted to a user)?


A. INDEX
B. ALTER
C. REFERENCE
D. TRUNCATE
-----
TRUNCATE is not a table privilege. INDEX and ALTER can be granted to either a user or a role, but REFERENCE can be granted only to a user.
Ans: C.


20. If Judy grants ALL on her table FORMAT_CODES to PUBLIC, which operation will user Jerry not be able to perform without being granted other privileges?


A. create index on judy.format_codes
B. alter table judy.format_codes
C. delete table judy.format_codes
D. truncate table judy.format_codes
----
Ans: D. TRUNCATE is not a table privilege.