Oracle tablespace and user Rights Management "go"

One, table space

The Oracle database contains logical and physical structures.

The physical structure of a database refers to a set of operating system files that make up a database.

The logical structure of a database refers to a set of logical concepts that describe how the data is organized and the relationships between them.

Tablespace is an important component of the logical structure of a database.

Table spaces can hold a variety of application objects, such as tables, indexes, and so on.

Each table space consists of one or more data files.

1. Classification of Table spaces

Table spaces can be divided into 3 categories:

• Permanent table space: stores data such as tables, heavens, processes, and indexes in general. The system, Sysaux, users, and example table spaces are installed by default.
• Temporary table space: Data that is used only to hold short-term activities in the system, such as sorting data.
• Undo tablespace: Used to help roll back uncommitted transaction data, the submitted data is not recoverable here. It is generally not necessary to create temporary and undo tablespace unless you transfer them to another disk to improve performance.

2. The purpose of the tablespace

(1) Assigning different table spaces to different users, assigning different table spaces to different schema objects, facilitating the operation of user data and management of schema objects.

(2) Different data files can be created on different disks, which facilitates the management of disk space, improves I/O performance, facilitates backup and recovery of data, and so on.

(3) The Oracle system automatically establishes multiple table spaces after the installation of the Oracle system and the creation of an Oracle instance.

3. Create a table space

Create a table space with the following syntax:

1 CREATE tablespace tablespacename 2 datafile ' filename ' 3 [SIZE integer [K | M]] 4 [autoextend [OFF | On]];

In the syntax:

• Tablespacename is the name of the tablespace to be created;
• DATAFILE specifies one or more data files that make up the tablespace, separated by commas when multiple data files are available;
• FileName is the path and name of the data file;
• Size Specifies the file sizes, using K to specify the size of the kilobytes, and M to specify the megabyte size;
• The Autoextend clause is used to enable or disable automatic extension of the data file, set to on to automatically expand when the space is used, and set to off it is easy to have a table space remaining capacity of 0, so that data can not be stored in the database.

Example: Creating an autogrow tablespace GEEKSSS SQL statements are as follows:

1 CREATE tablespace geeksss 2 datafile ' D:\ORACLE\DATA\GEEKSSS. DBF ' 3 SIZE 10M 4 autoextend on;

4. Delete Table spaces

You can delete a user-defined tablespace from a drop statement (drop tablespace plus the name of the tablespace).

Grammar:

1 DROP tablespace tablespacename;

It is a good idea to back up the database before deleting the tablespace.

Second, custom user management

When a new database is created, Oracle will create some default database users, such as SYS, System, and Scott. SYS and system users are users of Oracle systems, and the Scott user is a demo account of the Oracle database with some test sample tables.

The following is a brief introduction to the schema of the SYS, system, and Scott users.

1. sys

SYS user is a super user in Oracle.

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

SYS users are primarily used to maintain system information and manage instances. SYS users can only log on to the system in Sysoper or SYSDBA roles.

2. System

The system user is the default sysadmin in Oracle, and it has DBA authority.

The user has internal tables and views used by Oracle management tools, typically managing users, permissions, and storage for Oracle databases through system users.

It is not recommended to create a user table in system mode, and the system user cannot log in to Sysoper or sysdba roles, only by default.

3. Scott

The Scott user is a model user of the Oracle database, which is typically created when the database is installed.

The Scott user mode contains 4 demonstration tables, one of which is an EMP table that uses the users table space to store schema objects.

Typically, for security reasons, you need to set different access permissions for different data tables.

At this point, you need to create a different user. The Create user command in Oracle is used for creating new users.

Each user has a default tablespace and a temporary table space. If not specified, Oracle sets the users as the default tablespace, and temp as the temp table space.

The Create user syntax is as follows:

1 CREATE USER username 2 identified by password 3 [DEFAULT tablespace tablespacename] 4 [Temporary tablespace Tablespacena Me

In the syntax:

• Username is the user name and the user name must be an identifier;
• Password is the user password, the password must be an identifier, and is not case-sensitive;
• The default or temporary tablespace determines the user or temporary table space for the users.

Example: The code demonstrates how to create a user named Martin:

1 CREATE USER Martin 2 identified by Martinpwd 3 DEFAULT tablespace geeksss 4 temporary tablespace temp;

The above command will create a user named Martin with a password of martinpwd, the default tablespace is Geeksss, and the temp table space is temp.

Example: Change Martin's password to mpwd:

1 ALTER USER Martin 2 identified by MPWD;

The drop user command for Oracle can be used to delete users, but users cannot be deleted when they have schema objects. Instead, you must use the CASCADE option to delete the user and user mode objects.

Example: How to delete a user martin:

1 DROP USER Martin CASCADE;

Third, database Rights management

Permissions are the user's right to execute a feature. In Oracle, depending on how the system is managed, the privilege atmosphere system can be new and object permissions.

1. System permissions

System permissions refer to whether the authorized user can connect to the database and what system operations can be performed in the database.

System permissions are the right to perform certain system-level operations in a database, or to perform some kind of action on a class of objects.

For example, the right to create a tablespace in a database, or to create a table in a database, belongs to system permissions.

Common system permissions are as follows:

• CREATE SESSION: Linking to a database
• CREATE TABLE: Creating tables
• CREATE VIEW: Creating views
• Create SEQUENCE: Creating a sequence

2. Object permissions

Object permissions are the permissions that a user has on a specific object in the database.

Object permissions are the right to perform operations on a particular pattern object.

Object permissions can only be set and managed for schema objects, such as tables, views, sequences, stored procedures, stored functions, and so on, in the database.

Oracle database users have access to two ways:

(1) The administrator grants permissions directly to the user.

(2) The administrator grants permissions to the role and then grants the role to one or more users.

Using roles makes it easier and more efficient to manage permissions, so database administrators typically use roles to grant permissions to users rather than directly to users.

A number of roles have been predefined in the Oracle database system, the most common of which are the connect role, the resource role, the DBA role, and so on.

Users of the general program should only grant connect and resource two characters.

The DBA role has all of the system permissions and can be delegated to other users and roles. Because the DBA role has more permissions, it is not listed here.

The system predefined roles commonly used in Oracle are as follows:

• Connect: Users who need to connect to the database, especially those who do not need to create a table, are typically granted the role.
• RESOURCE: A more reliable and formal database user can grant the role to create tables, triggers, procedures, and so on.
• DBA: The database Administrator role, with the highest permissions to administer the database, a user with a DBA role can revoke any other user or even DBA authority, which is dangerous, so do not grant the role easily.

(1) The syntax for granting permissions is as follows:

1 GRANT Permissions | role to user name;

(2) The syntax for revoking permissions is as follows:

1 REVOKE Permissions | role from user name;

Example: How to grant and revoke the two roles of Connect and resource for Martin users:

1 GRANT Connection,resource to Martin; --Grant connection and resource two characters 2 REVOKE connection, resource from Martin; --Revoke connection and resource two characters 3 GRANT SELECT on Scott.emp to Martin; --Allows the user to view the records in the EMP table 4 GRANT UPDATE on Scott.emp to Martin; --Allow users to update records in the EMP table

Database user Security Design principles:

• Database user rights are authorized according to the minimum allocation principle;
• Database users to be divided into management, application, maintenance, backup four categories of users;
• The use of SYS and system to establish database application objects is not allowed;
• Grant DBA to user is forbidden.

Oracle tablespace and user Rights Management "go"