Managing Users Connectivity   «Prev  Next»

Lesson 7Creating a User
ObjectiveCreate a Database User

Creating a User in Oracle

Once you have decided on the default and temporary tablespaces, the password, and the quotas, you are ready to create the new user.
The CREATE USER command is used for this. The CREATE USER command in the following MouseOver illustrates commonly used syntax elements.

Creating a User

Create Users 
CREATE USER coin_admin
  IDENTIFIED BY coin_admin
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  PROFILE default
  PASSWORD EXPIRE
  QUOTA 5000K ON users
  QUOTA 10M on tools
  QUOTA UNLIMITED ON temp;

  1. CREATE USER coin_admin: You are creating a new user named coin_admin.
  2. IDENTIFIED BY coin_admin: The initial password for this user will be the same as the username.
  3. DEFAULT TABLESPACE users: The default tablespace for this user will be the user's tablespace.
  4. TEMPORARY TABLESPACE temp: The user's temporary tablespace will be temp.
  5. PROFILE default: The user will be given the default profile. You will learn more about profiles later in this course.
  6. QUOTA 5000K ON users: The user will be allow to use 5000 kilobytes of disk space in the user's tablespace.
  7. QUOTA 10M ON tools: The user will be allowed to use 10 megabytes of disk space in the tools tablespace.
  8. QUOTA UNLIMITED ON temp: The keyword UNLIMITED allows the user to use any amount of disk in the temp tablespace.

Users and Schemas

Access to the database is granted to a database account known as a user. A user may exist in the database without owning any objects. However, if the user creates and owns objects in the database, those objects are part of a schema that has the same name as the database user. A schema can own any type of object in the database:
  1. tables,
  2. indexes,
  3. sequences, and
  4. views

Schema Owner

The schema owner or DBA can grant access to these objects to other database users. The user always has full privileges and control over the objects in the user's schema. When a user is created by the DBA (or by any other user with the CREATE USER system privilege), a number of other characteristics can be assigned to the user, such as which tablespaces are available to the user for creating objects, and whether the password is preexpired. You can authenticate users in the database with three methods:
  1. database authentication,
  2. operating system authentication, and
  3. network authentication.

With database authentication, the encrypted password for the user is stored in the database. In contrast, operating system authentication makes an assumption that a user who is already authenticated by an operating system connection has the same privileges as a user with the same or similar name (depending on the value of the OS_AUTHENT_PREFIX initialization parameter). Network authentication uses solutions based on Public Key Infrastructure (PKI). These network authentication methods require Oracle 11g or 12c Enterprise Edition with the Oracle Advanced Security option.

Always assign users a default tablespace, even if you do not plan to allow them to create objects. That way you do not have to remember to change their default tablespace settings later if you do allow them to create objects.

Unlimited -Tablespace
When it comes to quotas, you can place as many QUOTA clauses in the CREATE USER command as you need. Users who do not need to be restricted in terms of space can be assigned an unlimited quota. Alternatively, you can assign them no quota at all, and give them
UNLIMITED TABLESPACE privileges instead. If you do not want to type in commands, you can use Security Manager to create a user. Security Manager, as you will recall, is one of Enterprise Manager's management pack applications.

Oracle Security Manager in Oracle 12c

The Oracle Security Manager still exists as a component for Oracle 12c. It is a Java class that provides a framework for restricting the actions that can be performed by Java applications. The Security Manager can be used to protect against a variety of security threats, such as malicious code, unauthorized access to resources, and denial-of-service attacks.
To use the Security Manager, you must first create a security policy. A security policy is a list of permissions that are granted to the Java application. Once you have created a security policy, you must install the Security Manager in the Java Virtual Machine (JVM). This can be done by setting the `java.security.manager` system property to the name of your security policy class.
Once the Security Manager is installed, it will intercept all attempts by the Java application to perform privileged actions. If the application does not have the required permission for the action, the Security Manager will throw a SecurityException.
The Oracle Security Manager is a powerful tool that can be used to improve the security of Java applications. However, it is important to note that it can also be complex to configure and use. If you are considering using the Security Manager, it is important to carefully read the documentation and understand how it works.
Here are some examples of how the Oracle Security Manager can be used to protect Oracle 12c databases:
  1. Prevent Java applications from creating new database connections.
  2. Prevent Java applications from executing SQL statements.
  3. Prevent Java applications from accessing specific database tables or views.
  4. Prevent Java applications from modifying database data.
  5. Prevent Java applications from exporting database data to files.
If you are using Oracle 12c, I recommend that you evaluate the Oracle Security Manager to see if it can help you to improve the security of your Java applications.

Create a user with the Security Manager

This Simulation walks you through the process of creating the COIN_ADMIN user, shown earlier, with the help of the Security Manager.
  1. Start by logging into Security Manager. Enter SYSTEM and MANAGER for the username and password, COIN for the service name, then click OK.
  2. This is Security Manager's opening screen. Right-click the folder named Users.
  3. Select create from the pop-up menu to begin the process of creating a new user.
  4. This is the Create User dialog. You need to fill in the fields on this screen. Use COIN_ADMIN for the username password, and confirm password.
  5. Click the drop-down list for the default tablespace.
  6. Select Users.
  7. Click the drop-down list for the temporary tablespace.
  8. Select Temp.
  9. Click the Expire Password Now checkbox to force the user to change the password on first log in.
  10. Now that you have completed all the fields, Click the Quota tab.
  11. This tab allows you to assign tablespace quotas. Click the user's tablespace to highlight it.
  12. Click the Value radio button.
  13. Enter 5000 for the value in the cell to the right of the value radio button. This assigns the user 5000 kilobytes of quota on the User's tablespace. When you are done, click the Tools tablespace.
  14. Click the Value radio button.
  15. Enter 10 for the value and click the drop-down list that reads "K Bytes" to the right of the value field.
  16. Select "M Bytes" from the drop-down list. This assigns the user 10 megabytes of quota on the Tools tablespace. When you are done, click the Temp tablespace.
  17. Click the Unlimited radio button in order to provide the user unlimited access to the TEMP tablespace.
  18. Click the Create button to create the user.
  19. The user has been created. Click OK to acknowledge.
  20. You are returned to the Security Manager screen, where you can see your newly created user listed in the right pane of the window.

Creating Users - Quiz

Click the Quiz link below to answer a few questions about creating users.
Creating Users - Quiz