Oracle basic user management and oracle basic user management

Oracle basic user management and oracle basic user management

I. Users

When you create a data instance, Oracle creates some default database users, such as SYS, SYSTEM, and SCOTT. Both SYS and SYSTEM users are oracle system users, while Scott users are a test account of the Oracle database, which contains some test sample tables.

　1. SYS

SYS is a Super User in Oracle. All data dictionaries and views in the database are stored in SYS mode. The data dictionary stores all the information used to manage database objects. It is a very important system information in Oracle databases.

SYS user shares are mainly used to maintain system information and manage instances. SYS users can only log on to the system as SYSOPER or SYSDBA roles.

2. SYSTEM

The SYSTEM user is the default SYSTEM administrator in Oracle and has the DBA permission. This User owns the internal tables and attempts used by the Oracle management tool. You can use the SYSTEM user to manage users, permissions, and storage in Oracle.

We do not recommend creating user tables in SYSTEM mode. SYSTEM cannot be logged in as YSOPER or SYSDBA. It can only be logged in as the default mode.

3. SCOTT

SCOTT is a demonstration user of ORACLE databases. It is generally created during database installation.

The SCOTT mode includes four model tables: EMP (employee table), DEPT (Department table), SALGRADE (payroll table), and BONUS (BONUS table ).

　　

4. All system users and permissions in Oracle

User Name	Password	Description
CTXSYS	CTXSYS	CTXSYSYesInterMedia TextUser, WithConnect, resource, dbaPermission
DBSNMP	DBSNMP	DBSNMPIncludingCONNECT, RESOURCEHE SNMPAGENTDatabase role. RunCatnsmp. SQLThe script can delete this role and this user.
INTERNAL	ORACLE	INTERNALUsed to execute database management tasks. Including starting and disabling. Note1:INTERNALIt is not a real user name, it isSYSUsername andSYSDBAAn alias of the permission. Note2: When Not InstalledORACLE8iThe user needs a password for the database; otherwise, the password is not required.
MDSYS	MDSYS	MDSYSYesORACLESpatial data (Spatial) And media (InterMedia), Audio (Audio), Video (Vidio) And image administrator username.
MTSSYS	MTSSYS	MTSSYSYesORACLEOfMTS (Microsoft Transaction Server-Microsoft conversion Server)User name under.SeeUsing Microsoft Transaction Server With Oracle9i
LBACSYS (ORACLE9i)	LBACSYS	LBACSYSYesORACLEIdentifies a secure administrator user
ODM (ORACLE9i)	ODM	ODMIs a Data Mining user, ODMPerform data mining. SeeOracle9i Data Mining Administrator's GuideAnd Oracle9i Data Mining concepts
ODM_MTR (ORACLE9i)	MTRPW	ODM_MTRIs a user related to the data mining sample program. SeeOracle9i Data Mining Administrator's GuideAnd Oracle9i Data Mining concepts
OLAPSYS (ORACLE9i)	MANAGER	OLAPSYSUsed to createOLAPMetadata user. IncludingOLAP_DBA, RESOURCE,CONNECTRole. Oracle9i OLAP User's Guide
ORDPLUGINS	ORDPLUGINS	ORDPLUGINSYesORACLE InterMediaAndVideoUser name. It hasCONNECTAndRESOURCERole.
ORDSYS	ORDSYS	ORDSYSYesORACLE InterMedia Audio Video LocatorAndImageUser name. It hasCONNECTAndRESOURCERole.
OUTLN	OUTLN	OUTLNYesCONNECTAndRESOURCERole and support plan stability. Plan stability isORACLE. Used for optimization.
SYS	CHANGE_ON_INSTALL	SYSUsed to execute database management tasks. It includes the following roles: AQ_ADMINISTRATOR_ROLE AQ_USER_ROLE, CONNECT CTXAPP, DBA, DELETE_CATALOG_ROLE EXECUTE_CATALOG_ROLE EXP_FULL_DATABASE HS_ADMIN_ROLE, IMP_FULL_DATABASE JAVA_ADMIN, JAVADEBUGPRIV JAVAIDPRIV, JAVAUSERPRIV OEM_MONITOR RECOVERY_CATALOG_OWNER RESOURCE, SELECT_CATALOG_ROLE SNMPAGENT, TIMESERIES_DBA TIMESERIES_DEVELOPER
SYSTEM	MANAGER	SYSUsed to execute database management tasks. It includes the following roles: AQ_ADMINISTRATOR_ROLEAndDBARole.
SCOTT	TIGER	SCOTTUsers includeCONNECTAndRESOURCEDatabase role.

 

The above is the default ORACLE user and password introduction.

 

　　5. Custom Users

In addition to the functions provided by the system, Oracle also allows users to create their own users to access and manage databases. Each table has a default tablespace and a temporary tablespace. If not specified, Oralce sets Users as the default tablespace and TEMP as the temporary tablespace.

(1)Create user

Syntax:

Create user username

Identified by password

[Default tablespace tablespace]

[Temporary tablespace tablespace]

Syntax:

Username: indicates the created user name.

Password: indicates the created password.

Default tablespace tablespace: Specify the default tablespace. If not specified, it is USERS.

Temporary tablespace tablespace: Specifies the temporary tablespace. If not specified, it is TEMP.

Example:

Create user shop_user identified by shop -- create a user named shop_user with the password shopdefault tablespace shop_dev_data -- specify the default tablespace as shop_dev_data
Temporary tablespace temp; -- specify the temporary tablespace as temp;

　　After a new user is added, the user does not have any permissions and must be authorized to access the database.

　　Query the default and temporary tablespaces of all users:　

-- Query the user's default tablespace and temporary tablespace select username, default_tablespace, temporary_tablespace from dba_users;

　　

(2) Change the password

Syntax:

Alter user username

Identified by password

Syntax:

Username: indicates the user name to be modified.

Password: indicates the new password to be modified.

Alter user shop_userIDENTIFIED BY "123123" -- change shop_user to 123123. The password must be enclosed in double quotation marks.

 

(3) Delete a user

Syntax:

Drop user shop_user CASCADE

Drop user shop_user Cascade -- delete the shop_user. If the USER contains data, the cascade keyword must be used.