0507-0508
Page 507
Designing User Data Tables
Because each company has its own specific needs and internal procedures, Oracle does not provide tables to manage user data, such as the user's name . This means that you must take on the responsibility of creating one or more tables to manage this data, unless the user is using a third-party tool. You need to create user data tables to assign specific user accounts to real individuals, as well as to trace these usernames to individuals if necessary. These tables may be especially helpful when auditing user activity or if you need to contact a particular user to tell him or her to log off.
You should create at least one table to hold user data. Data maintained should consist of information such as the user's name, assigned username, office location, office phone number, pager number, and any other information required by the company. In a company with multiple applications or platforms, it would be ideal to have at least two user tables. You might choose to use this table to keep track of platforms accessible to the user. Of course, you would have a column for the username, which you would use to join the two tables. You also can join the user table(s) you create to tables in the data dictionary (by username) to obtain further information. You should define the username column in at least one of the tables as a primary key so that duplicate usernames will not be allowed. Although each database will refuse duplicate usernames, you want to maintain consistency in the company so that you do not have the same usernames in two different databases (such as JSMITHone belonging to John Smith and the other to Jack Smith).
Along the same lines as ensuring that usernames are unique across the board, you should set and enforce standard naming conventions for your company. One common convention for usernames is the user's first and middle initials and last name; other companies prefer to tag each user with a number. With network computing, some companies prefer to assign Oracle users the same username as their network username. Regardless of the method selected, username assignments always must be consistent.
User Table Maintenance
When creating, modifying, and deleting Oracle usernames from databases, the additional user table(s) you have created should be updated accordingly . By keeping these tables up-to-date, you always maintain control over user management.
Again, user table maintenance might seem like a simple task, but in a large company with numerous departments, this might not be the case. Suppose that you are a DBA for a large financial institution. You know that last week you created 20 temporary user IDs to be used in a demonstration for one of the departments. You know that the demonstration was only one day, but you haven't heard a word. You call the head of the department, only to find out that the manager has taken it on himself to assign the temporary user IDs to new employees employees who have not yet been approved for system access.
Page 508
Communication is critical to successful user management. Within the company's internal policies, procedures should be outlined for requesting database user access and notification of changes or removal of user IDs. Make sure that all designated approving authorities are aware of notification procedures, and do not hesitate to make them aware.
Summary
Creating a user account for the Oracle RDBMS is one of the most common activities a DBA undertakes. During most business days, you'll receive a user request of some type, such as creating an account, modifying an account, deleting an account, or resetting a password. Managing users requires more than simply logging on as the DBA and entering a command, though.
First and foremost, you must understand the requirements of the users. If you don't, you may fail to properly set up the user account. You also must consider issues such as authentication methods (password versus operating system) and user configuration methods (profiles and quotas), as well as basic user creation options (profiles, default tablespace, and temporary tablespace).
Only after you understand all the elements surrounding the creation of a user should you proceed with the user creation. To err on the side of prudence when creating users is to err on the proper side.
EAN: 2147483647
Pages: 391