Oracle Create user

Introduction

This article focuses on how to create users in Oracle using statements, and if you are a database operator then this is a must, by the way, the concept of a database in Oracle it's not the same as other database systems like MySQL and SQL Server, In Oracle, users can be understood as the concept of a database in other database systems, there is only one global database in Oracle and no other database is created, and creating a database is equivalent to reinstalling a set of database services.

Database version: ORACLE11GR2

Basic Syntax:

CREATE User user identified {by password| Externally [AS'certificate_dn' ]              | Globally [AS'[DIRECTORY_DN]']} [DEFAULT tablespace tablespace|temporary tablespace {tablespace|Tablespace_group_name}| {QUOTA {size_clause |UNLIMITED} On Tablespace} ... EDO| Profile Profiles|PASSWORD EXPIRE| Account {LOCK |UNLOCK} [DEFAULT tablespace tablespace|temporary tablespace {tablespace|Tablespace_group_name}| {QUOTA {size_clause |UNLIMITED} On tablespace} ...| Profile Profiles|PASSWORD EXPIRE| Account {LOCK |UNLOCK}] ...  ] ;

Create a Delete user

1. Create a user base

Base     base     DEFAULT tablespace userspace     temporary tablespace temp;

2. Delete a user

Base

If the user exists the object cannot be deleted can be added cascade at the same time delete the object under the user, for example:

base Cascade;

Authorized 1. Standard roles

Connect Role (connect roles)

Temporary users, especially those who do not need to build a table, usually give them only connectrole. Connect is a simple permission to use Oracle that only makes sense if you have access to other users ' tables, including SELECT, INSERT, UPDATE, and delete. Users with Connect role can also create tables, views, sequences (sequence), clusters (cluster), synonyms (synonym), sessions (session), and links to other databases.

Resource Role (resource roles)

More reliable and formal database users can grant resource role. Resource provides users with additional permissions to create their own tables, sequences, procedures (procedure), triggers (trigger), indexes, and clusters (cluster).

DBA Role (Database administrator roles)

DBA role has all of the system permissions----including unlimited space limits and the ability to grant various permissions to other users. System is owned by the DBA user. Here are some typical permissions that DBAs often use.

2. Authorization

Base

3. Reclaim Permissions

 from base;

Permissions that Connect,resource has

Create user

--table space path based on actual modificationCREATESmallfile tablespace "CHENMH" datafile'D:\ORACLE\ORADATA\ORCL\CHENMH'SIZE 100M Autoextend on NEXT10M MAXSIZE UNLIMITED;CREATE USERCHENMH identified byCHENMHDEFAULTtablespace chenmhTemporaryTablespaceTemp;/*Grant user session connections, create tables, create views, create sequences, create stored procedures, create views, tablespace Unlimited*/Grant CreateSessionCreate Table,Create View,CreateSequenceCreate procedure,Create Trigger, Unlimited tablespace tochenmh;--Grant Select on V_$session to CHENMH;--Grant Select on V_$sesstat to CHENMH;--Grant Select on V_$statname to CHENMH;

Summary

If you want to create a new database with other database systems in Oracle, then you can only create users instead.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

Oracle Create user