Administrating USER Account in Oracle

1. Types of USER

As per Oracle documentation, Oracle categories user according to usages.

• Database Administrator
• Network Administrator
• System Administrator
• Application Developer
• Database USER

2. Built-in Account

Some Important Built-in account in oracle are- Sys, System, Sysman 

Sys User account -

• DBA Role - pre-built role, 
• Own Data Dictionary, 
• Superuser in Oracle, 
• Similar to SA in Microsoft SQL Server, or Administrator in Window or root in *inx. 

Relationship between SYS user account SYSDBA privilege- 

Sys is a superuser account in Oracle while sysdba is a privilege that is added to a user account who have a member of the dba group of OS. When a user login by using Sysdba privilege that the user becomes a superuser like SYS user. 

SQLPLUS / as sysdba or connect / as sysdba or connect <someuser> as sysdba

System User Account - 

• Less Power User than SYS.  
• Along with the DBA Role System user also has MGMT_USER and AQ_Administrator role. 
• The system is a safer account for day to day DBA task. 
• Can't do DATABASE Upgrade, BACKUP and Recovery.

Sysman User Account - 

• Lower level Default System account having the least power but have more power than a normal user account. 
• Have MGMT_USER, RESOURCE, and SELECT_CATALOG_ROLE role.

3. How to create a new USER

Component of USER Account

Mandatory Field

Username - Unique Username <= 30 char , Alphanumeric , allowed special characters are $ and _

authentication - Password, or Bimetric, or Certificate, or OS, or external.

Optional Field

tablespace - specify default tablespace and temporary tablespace for the user.

Profile - Password policy, account Security

Schema - Collection of Object owned by USER. username = schema