Managing Users Connectivity   «Prev  Next»

Lesson 7Creating a User
ObjectiveCreate a Database User

Creating a User in Oracle

Once you have decided on the
  1. default and temporary tablespaces,
  2. the password, and,
  3. the quotas
you are ready to create the new user. The CREATE USER command is used for this. The CREATE USER command is shown in the following diagram and illustrates commonly used syntax.
Create Users
CREATE USER coin_admin
  IDENTIFIED BY coin_admin
  PROFILE default
  QUOTA 5000K ON users
  QUOTA 10M on tools

  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 such as
  1. tables,
  2. indexes,
  3. sequences, and
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 (PKI)Public Key Infrastructure [1]. 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 in Oracle

There are two approaches you can take when you don't want to restrict the amount of disk space assigned to a user.
  1. One approach is to assign that user unlimited quota on all the tablespaces in the database. That approach requires a lot of typing and, as you add tablespaces, you need to remember to assign the user quotas on those tablespaces.
  2. An easier approach is to grant the user the UNLIMITED TABLESPACE system privilege. This is done with the GRANT command and it allows a user to use any amount of disk space in any tablespace.

Question: How can I use an "unlimited amount" of any tablespace?
This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, the user's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles.
Privilege Description
CREATE TRIGGER Create a database trigger in grantee's schema
CREATE ANY TRIGGER Create database triggers in any schema except SYS 
ALTER ANY TRIGGER Enable, disable, or compile database triggers in any schema except SYS 
DROP ANY TRIGGER Drop database triggers in any schema except SYS 
ADMINISTER DATABASE TRIGGER Create a trigger on DATABASE. (You must also have the CREATE TRIGGER or CREATE ANY TRIGGER privilege.) 

Privilege Description
CREATE TYPE Create object types and object type bodies in grantee's schema 
CREATE ANY TYPE Create object types and object type bodies in any schema except SYS 
ALTER ANY TYPE Alter object types in any schema except SYS 
DROP ANY TYPE Drop object types and object type bodies in any schema except SYS 
EXECUTE ANY TYPE Use and reference object types and collection types in any schema except SYS, and invoke methods of an object type in any schema if you make the grant to a specific user. If you grant EXECUTE ANY TYPE to a role, users holding the enabled role will not be able to invoke methods of an object type in any schema

Privilege Description
CREATE USER Create users. This privilege also allows the creator to assign quotas on any tablespace, set default and temporary tablespaces, assign a profile as part of a CREATE USER statement
ALTER USER Alter any user. This privilege authorizes the grantee to Change another user's password or authentication method,  Assign quotas on any tablespace, Set default and temporary tablespaces, and  Assign a profile and default roles
BECOME USER Become another user. (Required by any user performing a full database import.)
DROP USER Drop users

Privilege Description
CREATE VIEW Create views in grantee's schema
CREATE ANY VIEW Create views in any schema except SYS 
DROP ANY VIEW Drop views in any schema except SYS 

QUOTA Clauses in the CREATE USER command

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 `` 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
[1]Public Key Infrastructure (PKI):Public Key Infrastructure (PKI) is the framework that underpins secure online communication and identity verification. 1) Creates and manages digital certificates, which essentially verify the identity of individuals, devices, or websites. 2) Uses public-key cryptography, a complex math system that allows secure communication even on insecure networks. 3) Ensures data confidentiality (only intended recipients can access it) and integrity (it hasn't been tampered with).

SEMrush Software