Oracle Database Add user

1. Differences between temporal and tablespace spaces

Tablespace: This space is used for data storage (tables, function, stored procedures, etc.), so is the actual physical storage area.

Temporal tablespace: The primary purpose is to sort operations on the database [such as creating indexes, order by and group BY, DISTINCT, union/intersect/minus/, Sort-merge and joins, analyze commands],

Manage indexes (such as creating an index, IMP for data import), accessing views, and so on provide temporary computing space, and the system will automatically clean up when the operation is complete.

Because of the different purposes, there is a distinction, in fact, the database has a default temporary space, but the actual application is difficult to meet the requirements, so you need to create a temporary space.

2. Create a temporary table space

CREATE Temporary  ' D:\Oracle_Database\oradata\orcl\test_temp01.dbf' on  NEXT  32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

Size: Initial Size

Autoextend on NEXT: Automatically grows when the initial size is low and then allocates much more space.

MAXSIZE: Max space, maximum space size.

3. Create a table space

CREATE' D:\Oracle_Database\oradata\orcl\test_data01. DBF'onNEXT  32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

Size: Initial Size

Autoextend on NEXT: Automatically grows when the initial size is low and then allocates much more space.

MAXSIZE: Max space, maximum space size.

4. Create a user and assign a tablespace

CREATE USER  by Password DEFAULT tablespace test_data Temporary Tablespace test_temp;

Another note: Change the tablespace for a built-in user

ALTER USER DEFAULT Tablespace tablespacename;

5. Granting permissions to users

In an Oracle database, permissions fall into two categories: System permissions, Entity permissions (object permissions).

System permissions: The system specifies that the user has permission to use the database (System permissions are for the user).

Entity permissions: A permission user's access to another user's table or view (for a table or view).

In the case of production, we have created a user, directly to the system permissions, after ordinary users are common, generally use the following statements to give permission to

To meet the requirements:

GRANT  to USER;

Common system Administration permissions:

DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.
RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.
Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.

For normal users: Grant Connect, resource permissions.
For DBA administration users: Grant Connect,resource, dba authority.

System Permission Authorization command:
System permissions can only be granted by the DBA User: SYS, System (only two users at the beginning)
Authorization command: Grant Connect, resource, dba to User name 1 [, user name 2] ...;

GrANT  to [ user Name 2]...;

Note: An ordinary user can have the same user rights as the system through authorization, but never the same permissions as the SYS user, and the permissions of the system user can also be reclaimed.

Related permission query statements:

Query users have those permissions:

SELECT *  from Dba_role_privs; SELECT *  from Dba_sys_privs; SELECT *  from Dba_sys_privs;

Find out what system permissions you have

SELECT *  from Session_privs;

Common Entity permissions:

 　　 Grant CreateSession toZhangsan;//Grant Zhangsan user permission to create session, that is, login permissionGrantUnlimited tablespace toZhangsan;//granting Zhangsan users permission to use tablespacesGrant Create Table  toZhangsan;//granting permissions to create tables GranteDrop Table  toZhangsan;//granting permission to delete a tableGrant Insert Table  toZhangsan;//permissions to insert tablesGrant Update Table  toZhangsan;//permissions to modify tablesGrant  All  to  Public;//This one is more important, giving all permissions ( All) to all Users ( Public)

Oralce more strict rights management, ordinary users are also the default can not access each other, need to authorize each other:

 　　 Grant Select  onTableName toZhangsan;//Grant Zhangsan permission to view the specified tableGrant Drop  onTableName toZhangsan;//Grant Zhangsan permission to delete the specified table tableGrant Insert  onTableName toZhangsan;//Grant Zhangsan the right to insert the specified tableGrant Update  onTableName toZhangsan;//granting permissions to modify tablesGrant Insert(ID) onTableName toZhangsan; Grant Update(ID) onTableName toZhangsan;//Grant Zhangsan Insert and Modify permissions on specific fields of the specified table, note that only the INSERT and updateGrantAlert All Table  toZhangsan;//Grant Zhangsan user alert permission to any table

In general, use the following sentence to satisfy:

GRANT  to USER;

6. Revoke Permissions

Basic syntax with GRANT, keyword revoke

Reference:

Oracle user, object permissions, System permissions

Oracle Rights Management Detailed (original)

Summary of Oracle Temp table spaces

Oracle creates table spaces, creates users, and authorizes, views permissions

Oracle user creation and permissions settings

Oracle Database Add user