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 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;
CREATE USER coin_admin: You are creating a new user named coin_admin.
IDENTIFIED BY coin_admin: The initial password for this user will be the same as the username.
DEFAULT TABLESPACE users: The default tablespace for this user will be the user's tablespace.
TEMPORARY TABLESPACE temp: The user's temporary tablespace will be temp.
PROFILE default: The user will be given the default profile. You will learn more about profiles later in this course.
QUOTA 5000K ON users: The user will be allow to use 5000 kilobytes of disk space in the user's tablespace.
QUOTA 10M ON tools: The user will be allowed to use 10 megabytes of disk space in the tools tablespace.
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
tables,
indexes,
sequences, and
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:
database authentication,
operating system authentication, and
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.
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.
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.
FOR TRIGGERS:
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.)
FOR TYPES:
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
FOR USERS:
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
FOR VIEWS:
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 `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:
Prevent Java applications from creating new database connections.
Prevent Java applications from executing SQL statements.
Prevent Java applications from accessing specific database tables or views.
Prevent Java applications from modifying database data.
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.
Start by logging into Security Manager. Enter SYSTEM and MANAGER for the username and password, COIN for the service name, then click OK.
This is Security Manager's opening screen. Right-click the folder named Users.
Select create from the pop-up menu to begin the process of creating a new user.
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.
Click the drop-down list for the default tablespace.
Select Users.
Click the drop-down list for the temporary tablespace.
Select Temp.
Click the Expire Password Now checkbox to force the user to change the password on first log in.
Now that you have completed all the fields, Click the Quota tab.
This tab allows you to assign tablespace quotas. Click the user's tablespace to highlight it.
Click the Value radio button.
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.
Click the Value radio button.
Enter 10 for the value and click the drop-down list that reads "K Bytes" to the right of the value field.
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.
Click the Unlimited radio button in order to provide the user unlimited access to the TEMP tablespace.
Click the Create button to create the user.
The user has been created. Click OK to acknowledge.
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).
