1. Oracle Introduction

Oracle Study Notes

1. Oracle Introduction

1. Select database standards
2. Project Scale
3. What is the load and the number of users?
4. Cost
5. Security

 

Oracle Certification

Beginner: OCA: Oracle certificated associate

Intermediate: OCP: Oracle certificated professional

Advanced: OCM: Oracle certificated master

 

1. Oracle Installation, startup, and uninstallation

After oracle is installed, sys and system users are automatically generated,

1. A sys user is a super user with the highest permissions, The sysdba role, and the create database permission. The default password for this user is change_on_install.
2. The system user is a management operator with a high level of permissions. He has the sysoper role and does not have the create database permission. The default password is Manager.
3. Generally, you can log on to the database by using the System user for database maintenance.

 

1. Start 2 Services manually

My computer --- manage ---- service --- oracleorahome90myora1 ---- right-click to start

--- Oracleorahome90tnslistener ---- right-click to start

Log on to SQL-plus

Username: Scott

Password: Tiger

String: Default instance myora1

 

Common SQL-plus commands:

Show user; -- display the current user

Exit; -- exit sqlplus

Set linesize 120; -- set the number of Line Characters

Set pagesize 8; -- set pagination

Spool -- input content to a specified file

Switch User: conn system/Manager

 

Change Password: passw press ENTER-Change Password for yourself

Change Password for others: passw Xiaoming (user name)

-- If you want to change the password for someone else, you must have the DBA permission or the alter user permission.

Edit file: Edit

 

 

Create a user: You must have sys or system user permissions. Otherwise, the system prompts that the user has insufficient permissions.

Create user Xiaoming identified by m123;

 

Note: The created user does not have the permission or even the database logon permission. You must specify the corresponding permission for the user. The command to grant permissions to a user is:Grant, Revoke permissions using commandsRevoke

 

 

Delete user: Generally, a user is deleted as a DBA. If a user is deleted by another user, the user must have the drop user permission;

When deleting a user, if the user already has a table, the cascade parameter must be included to specify the cascading deletion of the table created by the user.

Command: Drop User Username [cascade]

 

Permission:

In Oracle, permissions are divided into database permissions and object permissions.

System permissions include logging on to the database, creating databases, creating tables, creating stored procedures, and creating indexes. (There are more than 140 permissions)

Object permission: the user's permission to access/operate Data Objects of other users; (about 25)

For example, the user's permission to access tables and views of other users;

 

Data Objects: tables, stored procedures, triggers, views, sequences, synonyms, etc.

 

Role:

In Oracle, It is very tiring to assign too many permissions one by one. Therefore, a set of built-in basic permissions is proposed, which is called a role;

For example, connect is a role that contains permissions;

Roles include custom roles and predefined roles.

Predefined roles: built-in

Custom roles: User-defined roles

Authorization example:GrantConnect to Xiaoming; -- authorization successful

Common roles:

Connect: database connection permission

DBA: the permission is high and cannot be granted easily

Resource: You can create tables in any tablespace.

 

To grant Xiaoming the permission to create a table, you must switch to the System user:

Conn system/manager;

Grant resourec to Xiaoming;

-- Authorization successful

Create Table user (userid varchar (30), username varchar2 (30 ))

-- Created successfully

Select * from test; -- null

Run the DESC command to view the table structure:

DescTest;

List the table structure, field name, whether it is null, Type

 

How can I grant Xiaoming the permission to query the scoot EMP table?

Grant select on EMP to Xiaoming;

-- Authorization successful

 

Conn Xiaoming/m1234;

Select * From scoot. EMP;

Scott. EMP is calledSolution.

Solution: each user has a logical space, called a solution.

 

How can I grant Xiaoming the permission to modify the scoot EMP table?

Grant update on EMP to Xiaoming;

-- Authorization successful;

Select: Select, update, insert, and delete. These permissions are collectively referred to as all.

For convenience, you can directly grant: grant all an EMP to Xiaoming;

That is, all operation permissions for the table EMP are granted to Xiaoming.

 

How to revoke permissions?

Scott wants to revoke the query permission of Xiaoming on the EMP table;

Revoke select on EMP from Xiaoming;

 

Permission

If the permission is granted, you also want XiaomingObject permissionTo grant permissions to other users, add the following command after the grant command:With grant option

Grant select on EMP to Xiaoming with grant option

 

If you want to grant system permissions to other users, add the following command after the grant command:With admin Option

Grant select on EMP to Xiaohong with Grant admin option;

 

Q: The Scott user has granted Xiaoming permissions, and James has granted the select permission to Xiaohong. If Scott revokes Xiaoming permissions, how about Xiaohong?

Conn Scott/m123;

Create user Xiaoming identified by m123;

Grant connect to Xiaoming;

Grant all on EMP to Xiaoming;

Conn Xiaoming/m123;

Create user Xiaohong identified by m123;

Grant connect to Xiaohong;

Grant selecet on EMP to Xiaohong;

Conn Scott/m123;

Revoke select on EMP from Xiaoming;

Conn Xiaohong/m123;

Select * from Scott. EMP;

---- Failed to execute because the permission cascade is revoked.

Cascade and revoke management permissions. Aliyun.com.