Objectives:
·To become familiar with basic tasks of user management and how to execute them in Oracle 11g.
·Related to Objectives #3 and #4 as listed in the Syllabus Addendum.
Scenario:
Before coming to CKDS the management of Team Retail, Inc. hired Fly By Night, LLC to build and manage the database. The staff at Fly By
Night designed the database but did not complete the implementation of the design. CKDS has agreed to complete the implementation of the database so Team Retail can use the applications that depend on the database. Redesign of the database will be limited to the tasks required to enable it to function efficiently with a complete redesign following once the needs of Team Retail are more clearly identified.
Now that you have explored Oracle 11g and the Team Retail environment, the CEO of CKDS has some initialization tasks for you. The first task is to identify the user groups and create appropriate sample users.
User management is considered an aspect of security for an Oracle database. The Concepts manual has overview information with detailed instructions for creating users in the Security Guide and 2 Day + Security tutorial.
The CEO of CKDS has observed that user management is simpler and more efficient with the definition and enforcement of a good naming convention and the use of roles and profiles when creating user accounts. Complete each of the following to begin building the user management strategy for Team Retail.
1. Roles and Profiles are used by Oracle to define and control access and privileges of groups of users. Compare and contrast the concepts of role and profile.
a.What is the purpose of each?
b.What does each allow or restrict?
c.How can they be assigned to a user account?
2. Team Retail has decided to have three user groups in addition to the DBAs at CKDS. Those groups are developers, staff, and end-users. The developers will create and manage the applications while the staff will use the applications to accomplish their job duties. The end-users are the customers who will purchase items from Team Retail. Describe/define the access needs of each user group. Consider the following in your answer.
a.What tables will be accessed by each group?
b.What operations will the members need to perform on each table accessed: insert, delete, update, read?
c.What other restrictions or privileges will each group need?
4. Because security and compliance are important considerations for any company, we must include them as we build our database systems. One important and useful contribution of the DBA is to define a naming convention for all users. This convention must provide a unique name for each user and include identification of the user group to which each individual user belongs. Define the naming convention you will use for developers and end-users.