/ * Data concurrency and consistency * /
Data concurrency means that multiple users can access the same data at the same time. Data consistency refers to each user to see a consistent data view while ensuring concurrency, including users to see themselves or other users. The transaction makes the changes made by the data.
Three readings that can be prevented: * Dirty reading: A transaction reads the data that has been modified by other uncommitted transactions. * Do not read: After a transaction reads a data, other transactions have modified these data and Submitted, so when the transaction re-reads these data, it will get the result of inconsistency with the previous reading. * Symptom read: Get a result set that meets the query conditions when performing the first query in a transaction. Subsequently, other transactions are inserted or deleted recorded records that meet the query conditions, so when the transaction reresses the same query, it will get the result set that is inconsistent with the previous query.
Various isolation levels to prevention of read phenomena:
Isolation rating dirty reading can not be repeatedly read out ----------------------------------------- --Read uncommitted may happen possible if read committed does not happen possible if the REPEATABLE READ does not happen without happens, SERIALIZABLE does not happen to happen.
Oracle provides two isolation grades of Read Committed and Serializable. By default, Oracle uses the Read Committed transaction isolation level.
Using different isolation levels, Oracle enables two levels of read-consistency: * Stramon-level read-consistency: The data read by the query operation is data that has been submitted before the statement begins with the statement without being submitted. Data or data submitted after the query statement starts to execute. * Transaction grade read consistency: Data that has been submitted before the transaction is started during the full process of transaction, without containing any no The data submitted or submitted after the transaction begins.
/ * Data Integrity * /
The data integrity of the relational database mainly includes three aspects: entity integrity, reference integrity, and user-defined integrity. These integrity can be implemented in Oracle through objects such as constraints, triggers.
Oracle can establish the following five types of integrity constraints: * Not null constraint * Unique constraint * primary keyl constraint * Foreign Key constraint * check constraint
/ * Summary file * /
A database user can only specify a profile
1. By profile, you can limit the following database and system resources: CPU time logic reads users' concurrent session number idle time connection time private SGA area
2. The profile can also control the user's password policies
Create profile create profile accounting_user limitsessions_per_user 6connect_time 1440idle_time 120logical_reads_per_session unlimitedcpu_per_session unlimitedpassword_grace_time 30failed_login_attempts 6;
Specify profile ALTER SCOTT Profile Accounting_User; Modify Profile Accounting_User Limitcpu_Per_Call Default DEFAULT
Delete profile Drop Profile Accounting_User Cascade;
Summary File Data Dictionary DBA_Profiles Basic information about all profiles
/ * User * /
Create User Create User Zhao Identified by Password Default TableSpace Accounting_TBS Quota 100m / Unlimited ON Accounting_TBS Quota 500k on Indx Temporary TableSpace Temptbs Profile Accounting_Users
Grant Connect to zhao,
By default, new users do not have any quotas in any table space.
Lock / unlock User ALTER User Zhao Account Lock / Unlock
Modify User Alter User Zhao Identified by Aaaaaa Default TableSpace Uses
Delete Users (Cascade Keywords Specify all objects in user mode) DROP USER ZHAO Cascade
User Information Dictionary ALL_USERS / DBA_USERS / User_Users database user information DBA_TS_QUOTAS / USER_TS_QUOTAS user's tablespace quota information
/ * Permissions and roles * / oracle, permission is divided into two categories:
Object-authority: Object Permissions are the right to perform various operations for a particular mode object. Both of these model objects can set objects: tables, views, sequences, stored procedures, functions, packages.
System Permissions: System Permissions Execute some operation at the database level, or to perform some of the action for a certain type or a non-mode object. System permissions are not for a particular object, but for the entire database range.
System permissions can also be divided into two categories 1. Database objects for a type in the database Select Any Table, Drop any Index, ... 2. Action ALTER SYSTEM, ..... .
If the WITH Admin Option clause is used when granted user system permissions, the authorized user can also grant this system privilege to other users.
Object privileges have nine categories: SELECT (unable to grant fields in the table, can only grant the entire table) UpdateDeleteInsertexecute (for stored procedures, functions, and packages) Read (for directory) Indexreferencealter
If you use the with grant option when granted user object permissions, the authorized users can also grant this object authority to other users.
Create a role (can simplify permission management and dynamic permission management)
Predefined role: connectionResourcecedBaExp_full_databaseImp_full_database .....
Create Role Accts_Role; Grant SELECT ON SAL_HISTORY TO Accts_Role; Grant INSERT, UPDATE 安 on en;;
Grant or role
1. Grant System Permissions Grant Create Any Table To Hanzo;
2. Grant the object authority to the user Grant Select, Update on Customer To Felix;
Where INSERT, UPDATE, and REFERENCES can grant the fields in the table or view Grant Update (Name, Email) on Customer To Felix;
You can use the keyword all to grant all object permissions of an object to the specified user Grant All On Customer To Felix; 3. Grant the role to the user Grant Accts_Role to Hanzo with Admin Option;
Cannot use a GRANT statement to grant object authority and role for users at the same time
Recycling authority and role Revoke Select, Update on Customer from Felix; Revoke Accts_Role from Hanzo; Revoke All On Customer from Hanzo
Permissions and Role Data Dictionary All / DBA / USER_TAB_PRIVS contains object permission information that all objects has been granted, all / user_col_privs_made contains object permission information granted by the current user DBA / user_sys_privs contains all granted system permission information session_privs contains the current session Permission information all / dba / user_col_privs contains all fields have been granted object permission information DBA_RoLES contains all roles in the database DBA / user_role_privs contains role information to be granted to the user or role Role_Role_privs contains role information role information role_sys_privs contains role grants System Permissions Information Role_TAB_PRIVS contains object permission information for role session_roles contains the role information of the current session.
