How To Create An User In Oracle

How To Create An User In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online tutorial is based on examples to make it easier to follow. To create user in Oracle the most basic syntax is

CREATE USER <username> IDENTIFIED BY <password>;

This following command will create a new user into Oracle database and will apply the current default database configuration setting on the new user. Please take a look at the following command that will create a new user named “john” and with password “john1234“.

CREATE USER john IDENTIFIED BY john1234;

dba oracle dba database management system oracle performance tuning oracle create user oracle create user in oracle how to create an user in oracle create user externally create user the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

The best practise is to let Oracle Create User command to force the new user change his/her password before getting into Oracle database for the very first logging. To do so, the additional keyword is PASSWORD EXPIRE. In the following Oracle DBA example we are showing how looks the command Oracle Create User with keyword PASSWORD EXPIRE:

CREATE USER john IDENTIFIED BY john1234 PASSWORD EXPIRE;

dba oracle dba database management system oracle performance tuning oracle create user oracle create user in oracle how to create an user in oracle create user externally create user the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Also you can set to command create user a different Oracle tablespace and quota than it is set by default. The following Oracle DBA example of Create User in Oracle will assign user “john” to Oracle data tablespace JOHN_DATA and Oracle temporary tablespace JOHN_TEMP and his JOHN_DATA quota will be 15 Mb and quota for JOHN_TEMP is 10 Mb.

CREATE USER john IDENTIFIED BY john1234
    DEFAULT TABLESPACE john_data
      QUOTA 15M ON john_data
  TEMPORARY TABLESPACE john_temp
      QUOTA 10M ON john_temp
   PASSWORD EXPIRE;

dba oracle dba database management system oracle performance tuning oracle create user oracle create user in oracle how to create an user in oracle create user externally create user the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

In addition, you can set to the Oracle Create User command a common Oracle profile where you have set all necessary restrictions for example like maximum amount of open sessions or connection times or you can set Oracle Grant commands and give commonly used permissions to the profile.

The second type of Create User in Oracle command is for external programs or tools that will only to be used to connect to the database. The advantage is that this type of users will need to be identified first times by the external tool or a program that will used this user to connect to a database. And later one the same user will be allowed get access to the Oracle database only via the same tool or a program. This way the created user cannot be used to log on database from any source or as the normal user. This way created user will give a bit more security to the system. Take a look at the next Oracle DBA example that will demonstrate how to create a new user as the external type of connection only.

CREATE USER john IDENTIFIED EXTERNALLY
    DEFAULT TABLESPACE john_data
      QUOTA 15M ON john_data;

dba oracle dba database management system oracle performance tuning oracle create user oracle create user in oracle how to create an user in oracle create user externally create user the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

See Also:
Oracle Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*