Manager Users - Oracle

CRITICAL SKILL 3.9 --  Manage Users

Before you can do anything in Oracle, you need to have a user ID created to enable you to log in to Oracle. As a DBA, you will begin with the SYS or SYSTEM accounts since these accounts both have the DBA role and exist in all Oracle databases. They are often used to perform database administration tasks. The SYS account is also granted the sysdba privilege and is the schema that the Oracle catalog is stored in. You should only use the SYS account when you need to perform a task as SYS or need the sysdba privilege. If your database was created using the Database Configuration Assistant (dbca), then you will also automatically get the SYSMAN and DBSNMP accounts. SYSMAN is used to administer Oracle Enterprise Manager (OEM) and DBSNMP is used by the agent that OEM employs to monitor Oracle databases. Several other accounts will also be set up for the “example” schemas, such as the Sales History (‘SH’) user that we will utilize throughout this book. The OUTLN schema will be created to allow you to use plan stability through the stored outline feature. Depending on the options you choose when creating your database, other accounts may be set up for you. For example, if you install the OLAP option, the OLAPSYS account will be created.

When you create a user, you can either use the create user syntax or the OEM, which is an easier approach. In order to create a user, you will need to decide the following things:

• The default tablespace where segments created by this user will be placed unless a tablespace name is used in the DDL to override this.
  
• Whether to expire the password so that the user needs to change it the first time they log in to Oracle.
  
• A temporary tablespace to store internal data used by Oracle while queries are running. Sort operations make use of the temporary tablespace if there is not enough room in the SGA to perform the sort operation.
  
• Whether to employ user quotas on tablespaces, which put a limit on the amount of space that a user’s objects can take up in that tablespace.
  
• The authentication type, which allows you to specify whether you want Oracle to force the user to specify a password when they log in, or you can trust the operating system to do this for you.
  
• The initial password that the user will log in with. The user must change this during the first log in if you chose to expire the password in advance of the user’s first logon.
  
• Privileges to grant to the user. These can be direct privileges or roles. We will discuss them in the next section.
  
• A profile for the user, which can be employed to manage the user’s session by limiting resources that sessions can use, and that help implement corporate password policies. We will also see this in the next section.
  
• Whether to lock or unlock the user.

The OEM console in Figure 3-9 shows the options available to you to create and edit a user. For each user, there are eight separate tabs that allow you to easily enter a user’s information. You can see the SQL that will be generated by selecting the Show Sql button at the bottom of the panel. Another great option allows you to model a user and create another user like one that already exists. To do this, click the user that you want to model, select Object from the top of the panel, and then select the Create Like option.

Here is a sample CREATE USER statement:

CREATE USER "NEWUSER" PROFILE "DEFAULT" IDENTIFIED BY "newpassword"
PASSWORD EXPIRE DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"
QUOTA UNLIMITED ON TEMP QUOTA UNLIMITED ON USERS
ACCOUNT UNLOCK;
GRANT "CONNECT" TO "NEWUSER";

Figure 3-9. User Management view

Edit Users

Once a user has been created, you will be asked at different times to edit users to change quotas or reset passwords or unlock an account. This can be easily performed through OEM by selecting the User, choosing the option you want to change through the Gui, and then applying the change.

Editing users can also be performed using the ALTER USER statement, as shown next, where a user account is unlocked, the password is changed, and a tablespace quota is increased.

ALTER USER "username" IDENTIFIED BY "newpwd " QUOTA UNLIMITED ON TOOLS ACCOUNT UNLOCK;

We’ve now created a user and it’s time to grant them some privileges. Let’s see how we do this in the next section.