ORACLE step-by-step explanation (Lecture 1) and oracle lecture 1

ORACLE step-by-step explanation (Lecture 1) and oracle lecture 1

Oracle was not used for a long period of time due to work reasons. Recently, it took some time to learn and record what you learned so that you can consolidate it in the future (taking 10 Gb as an example ).

1. ORACLE Installation and uninstallation (1) ORACLE installation can be found on Baidu as long as the installation is successful by following steps. There are also a lot of ways to delete the Registry when uninstalling. (2) After successful ORACLE Installation, two users, sys and system, will be automatically created. 1. The sys user is a Super User and has the highest permissions. It has the dba role and sysdba and sysoper database management permissions. All the base tables and views of oracle data dictionaries are stored in the sys user, these base tables and views are crucial for oracle operation. They are maintained by the database and cannot be changed manually by any user. sys has the permission to create the create database. 2. The system user is the management operator and has the same permissions as the administrator and has the dba role. He does not have the create database permission. (Detailed differences between users and permissions are described later) II. logon to ORACLE (1) log On (user name, password, host string (database instance) using the SQL plus method provided by ORALCE )). (2) log on to Windows through cmd (sqlplus user name/password ). (3) log on to http: // localhost: 5560/isqlplus/(4) via oracle enterprise manager through isqlplus ). (5) use a third-party tool PL/SQL DEVELOPER. Iii. ORACLE user management 1. Create a user

SQL> conn system/orclConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as systemSQL> create user test identified by test;User createdSQL> 

2. Change the password for the user

SQL> alter user system identified by scott1;User altered

3. delete a user

SQL> drop user test;User dropped

Note: If you want to delete a user's data object (such as a table), you need to add cascade, such as drop user test cascade.
4. Create a user and log on to the database (Authorize the test logon role connect ).

SQL> create user test identified by test;User createdSQL> grant connect to test;Grant succeededSQL> conn test/test;Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as testSQL> show userUser is "test"

5. Grant user object permissions (use the system user to authorize test to operate the scott user's emp table using select, update, delete, or all)

SQL> conn system/orcl;Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as systemSQL> grant select on scott.emp to test;Grant succeededSQL> conn test/test;Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as testSQL> select * from scott.emp;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH      CLERK      7902 1980-12-17    1800.00               20 7499 ALLEN      SALESMAN   7698 1981-02-20    1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981-02-22    1250.00    500.00     30 7566 JONES      MANAGER    7839 1981-04-02    2975.00               20 7654 MARTIN     SALESMAN   7698 1981-09-28    1250.00   1400.00     30 7698 BLAKE      MANAGER    7839 1981-05-01    2850.00               30 7782 CLARK      MANAGER    7839 1981-06-09    2450.00               10 7788 SCOTT      ANALYST    7566 1987-04-19    3000.00               20 7839 KING       PRESIDENT       1981-11-17    5000.00               10 7844 TURNER     SALESMAN   7698 1981-09-08    1500.00      0.00     30 7876 ADAMS      CLERK      7788 1987-05-23    1100.00               20 7900 JAMES      CLERK      7698 1981-12-03     950.00               30 7902 FORD       ANALYST    7566 1981-12-03    3000.00               20 7934 MILLER     CLERK      7782 1982-01-23    1300.00     34.56     1014 rows selected

Note: (1) grant select can be used for insert, update, delete, and all. (2) You can also authorize a column value of a Data Object (table or view) to a specified user, if select is used to authorize a column value to the specified notebook, create a view and authorize the view to the specified user ( Note: grant select on emp (ename, sal) to test; can be used for authorization on the Internet. This operation can be successful under 9i, but the permission for a select Column cannot be granted after 9i, however, you can grant insert and update permissions for a column.)

SQL> conn scott/scottConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scottSQL> create view v_test as 2 select ename, sal from emp; create view v_test asselect ename, sal from empORA-01031: lack of permission SQL> conn system/orcl; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as systemSQL> grant create view to scott; grant succeededSQL> conn scott/scottConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scottSQL> create view v_test as 2 select ename, sal from emp; View createdSQL> grant v_test to test; grant v_test to testORA-01919: Role 'v _ test' does not exist SQL> grant select on v_test to TEST; Grant succeededSQL> select * from v_test; ename sal ---------- --------- SMITH 1800.00 ALLEN 1600.00 WARD 1250.00 JONES 2975.00 MARTIN 1250.00 BLAKE 2850.00 CLARK 2450.00 SCOTT 3000.00 KING 5000.00 TURNER 1500.00 ADAMS 1100.00 JAMES 950.00 FORD 3000.00 MILLER 1300.0014 rows selected

Authorize the test user to modify only the sal field.

SQL> grant update(sal) on emp to test;Grant succeeded

Authorize the test user to insert only the empno and empname fields.

SQL> grant insert(empno,ename) on emp to test;Grant succeeded

6. revoke the user's permissions. (Log on to scott to revoke the permissions queried by v_test, modify sal field permissions, and insert empno, ename permissions)

SQL> CONN SCOTT/SCOTTConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scottSQL> REVOKE SELECT ON V_TEST FROM TEST; Revoke succeededSQL> REVOKE UPDATE (SAL) ON EMP FROM TEST; revoke update (SAL) on emp from test <span style = "color: # ff0000;"> ORA-01750: UPDATE/REFERENCES can only be FROM the entire table, not by column REVOKE </span>

SQL> REVOKE UPDATE ON EMP FROM TEST;Revoke succeededSQL> REVOKE INSERT ON EMP FROM TEST;Revoke succeeded

Note: The error message above indicates that the revoke of a table column can only be used for the entire table revoke.

7. Transfer permissions. (Use SCOTT to log on and authorize test user select to query v_test and allow test users to grant this permission to test1 users)

SQL> CONN SCOTT/SCOTTConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scottSQL> GRANT SELECT ON V_TEST TO TEST WITH GRANT OPTIONS; grant select on V_TEST to test with grant options <span style = "color: # ff0000;"> ORA-00994: missing OPTION keyword </span> SQL> GRANT SELECT ON V_TEST TO TEST WITH GRANT OPTION; grant succeededSQL> conn test/TESTConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as testSQL> grant select on V_TEST TO TEST1; grant select on V_TEST TO TEST1 <span style = "color: # ff0000; "> ORA-00942: The table or view does not exist </span> SQL> GRANT SELECT ON SCOTT. v_TEST TO TEST1; Grant succeeded

Note: (1) You only need to add with grant option during authorization to allow authorized users to grant some permissions to other users. (2) If you authorize system permissions (object permissions are used in the preceding operations) to others, you only need to add the with admin option. (3) In this case, if scott revokes the query v_test permission of test, the query v_test permission of test1 is also revoked.
8. Use profile to manage user passwords. Profile is a command set for password restrictions and data restrictions. When a database is created, ORACLE automatically creates a profile named default. When the profile is created, the profile option is not specified, oracle will allocate default to users. (Set the test user to try at most two logon attempts, and the lock time is 3 days)

SQL> conn system/orclConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as systemSQL> create profile lock_account limit failed_login_attempts 2 password_lock_time 3;Profile createdSQL> alter user test profile lock_account;User altered

<Span style = "color: # ff0000;"> use the following method to forcibly unlock a user </span>

SQL> alter user test account unlock;User altered

(Set the user to change the password on a regular basis, and change the password's grace period to three days every 10 days)

SQL> create profile remind_account limit password_life_time 10 password_grace_time 3;Profile createdSQL> alter user test profile remind_account;User altered

Delete directly drop profile remind_account;