Oracle usage Summary

Three default accounts:

For Oracle 9:
Sys change_on_install Network Administrator [as sysdba]
System manage local administrator
Scott tiger is locked by default and can be used only after being unlocked.

For Oracle 10:

Sys and system do not have a password, Scott does not have a password, you have to set it yourself, Scott needs to unlock.

Common Operations:

Sqlplus Lisi/Lisi User Logon
Disconn; disconnect (this can be used only when the user has successfully logged on)
Conn Lisi/Lisi; open the connection (the user can use it only if the user has successfully logged on)
Commit; submit data. Execute this command every time you add, delete, or modify the data to save it to the hard disk,

Data is consistent only when other users perform operations.

　　　　　

Create a user:

Sys is generally used for the following operations:
Create user Lisi identified by Lisi;


System permission: (DDL operation permission)

Authorization:
Grant create session to Lisi; Session permission. Each user must have this permission before logging on.
Grant create table to Lisi; Table creation permission. You must have a tablespace before creating a table.
Grant unlimited tablespace to Lisi; unlimited table space permissions. You can operate on all table spaces in the database.

With these three authorizations, you can perform crud operations on the table.

Revoke permissions:
Revoke create Session from Lisi;
Revoke create table from Lisi;
Revoke unlimited tablespace from Lisi;

Publish a permission (for example, the following two permissions of the sys account ):
Grant create session to public;
Grant create any talbe to public;

View permission table:
Set linesize 400 set row width
Select * From user_sys_privs


Object permissions (permissions for DML operations ):
Grant select on mytab to Lisi; grant the permission to query a table to Lisi
Grant all on mytab to Lisi; grant the permission to add, delete, modify, query, index, foreign key, and other table permissions to Lisi

Revoke select on mytab from list; revoke the Table query permission on Lisi
Revoke all on mytab from Lisi; Revoke all table permissions from Lisi

Select * From user_tab_privs;

Object permissions can be controlled to columns (only for adding and modifying ):
Grant Update (name) on mytab to Lisi; grant the permission to update the name field in the table to Lisi
Grant insert (ID) on mytab to Lisi; grant the permission to insert the ID field in the table to Lisi

Select * From user_col_privs;

Note: Query and deletion cannot be controlled to columns.

DDL-Data Definition Language
DML ---- data manipulation language
DCL-Data Control Language
Commit is required for addition, deletion, and modification in DML;

If you forget the commit, resource conflicts may occur when different users operate on the same table (DML operations are not allowed ),

When you operate on the same user, the latest data results are not displayed. Commit writes data to the hard disk.


Transfer permissions:
Grant alter any table to Lisi with admin option;

Note: in Oracle 10, if the Sys User grants the alter any table permission to Lisi, and Lisi grants the permission to Wang,

When sys revokes the alter any table permission of Lisi, Wang's alter any table permission still exists.

        grant select On A to lisi with grant option;

Note: in Oracle 10, assume that the Sys User grants select on a to Lisi, and Lisi grants this permission to Wang,

When sys revokes the select on a permission of Lisi, this permission of Wang is also revoked.


Role
Create role myrole;
Grant create session to myrole;

　　　　　grant create table to myrole;
drop role myrole;

You cannot grant the unlimited tablespace permission to a custom role because the permission is relatively large and is a special permission.

Note:
Create Table: create any table to create a table of any user
[Alter table] You do not have the alter any table permission to modify the table of any user.
[Drop table] You do not have the permission to drop any table to delete any user's table.
When a user has the create any table permission, the user has the other two permissions.
We do not recommend that you grant the create any table permission to the role.

A table belongs to a user.
A role does not belong to a user and is a collection of users.

Three database verification mechanisms:
Operating system verification/(current window user, system administrator privilege)
Password File verification sys
Database verification Scott


Oracle startup in Linux
LSNRCTL start
Sqlplus sys/Oracle AS sysdba
Startup


Oracle startup process under Window
LSNRCTL start listener
Oradim-startup-Sid orcl start database instance

What if the administrator password is lost?

★Assume that the user is Scott and the password is lost. Modify the password.

1. log on to sqlplus/As sysdba with sys. Note that this is an operating system verification,

Or use sqlplus sys/sys as sysdba; to log on

2. Execute the SQL statement alter user Scott identified by tiger;

I tried and passed the test.

★If the user is sys, because it belongs to sysdba, operating system authentication and password file authentication are used (sysoper also uses these two types of authentication ),

When we use sqlplus/As sysdba to log on, we use operating system verification. Because you are an administrator in the window,

By default, the current user is added to the ora_dba user group during installation, so you have the permission to log on.

This is dangerous. Choose Computer Management> local users and groups> Groups> ora_dba to delete the current window user from the table,

In this way, you cannot log on with sqlplus/As sysdba.
★Assume that all the above operations have been done, then you need to use sqlplus sys/sys as sysdba to log on, and in this case there is only one authentication,

The password file is verified. If you forget the Sys password, perform the following operations:

1. Find pwdorcl. ora.

Generally, it is located under c: \ oracle \ product \ 10.2.0 \ db_1 \ database \. Find it and delete pwdorcl. ora.

2. Use the command to regenerate pwdorcl. ora

In the command line, enter

　　　　　orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDorcl.ora password=sys entries=10

After the command is executed, the Sys password is changed to SYS.

I tried and passed the test. File indicates the path of the pwdorcl. ora file. Password indicates the password of SYS, and entries indicates the number of users.

Account Management details: create user username identified by password default tablespace temporary tablespace quota integer [k | M] | limited | unlimited on tablespace example: create user ABC identified by 123 default tablespace users temporary tablespace temp quota 50 m on users limit users: user lock -- alter User Username account lock user unlock -- alter User Username account unlock User Password instantly becomes invalid -- alter User Username Password expire (login is successful, Change Password) delete user: drop User Username [cascade] cascade indicates deleting all objects of a user. Example: Drop user ABC cascade;

A complete example:

sqlplus sys/sys as sysdba;

SQL> create user zhang identified by zhang default tablespace users 

temporary tablespace temp quota 100M on users;

SQL> create role myrole;

SQL> grant create session to myrole;

SQL> grant create table to myrole;

SQL> grant myrole to zhang;

sqlplus zhang/zhang

SQL> create table teacher(id int,name varchar(10));

SQL> show user;

　　

Loading editor...