Oracle Create user

Source: Internet
Author: 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.