Oracle Walkthrough (First lecture)

for a long time without Oracle for work reasons, it has taken some time to learn and record what you have learned in order to consolidate in the future (take 10g for example).

I. ORACLE installation and uninstallation(1) Oracle installation can go to Baidu on the search as long as the steps are basically able to install the success. Uninstall a little trouble dot need to delete the registry method there are also many online. (2)after successful installation of Oracle, two user sys and system will be created automatically. 1. SYS user is superuser, has the highest privileges, has DBA role and SYSDBA, sysoper Database Administrative rights, all Oracle The base tables and views of the data dictionary are stored in the SYS user, these base tables and views are critical to the operation of Oracle, maintained by the database itself, no user can change manually, SYS has the permission to create database. 2, the system user is the management operator, the permissions are as large as the DBA role, there is no permission to create database. (The detailed differences between users and permissions are mentioned later)Ii. how to log in to Oracle(1) Login (username, password, host string (db instance)) via Oralce's SQL plus method. (2) Login via window cmd (sqlplus username/password). (3) login via Isqlplus http://localhost:5560/isqlplus/(4) through Oracle's Enterprise Manager (OEM Oracle Enterprise Manager). (5) Through third-party tools PL/SQL DEVELOPER. Third, Oracle User management1. Create User

2. Change the password to the user

sql> alter user system identified by SCOTT1; User Altered

3. Delete users

sql> drop user test; User dropped

Note If you want to delete a user's corresponding data object (such as a table), you need to add cascade such as Drop user test cascade
4. Create a user and log in to the database (grant Test login 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. Authorize user object permissions (use the system user as test to authorize select or update or delete or all operations for the EMP table of the Scott user)

Sql> Conn SYSTEM/ORCL; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as systemsql> grant Select on Scott.em p 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 7499 ALLEN salesman 769       8 1981-02-20 1600.00 300.00 7521 WARD salesman 7698 1981-02-22 1250.00 500.00-7566 JONES MANAGER 7839 1981-04-02 2975.00 7654 MARTIN salesman 7698 1981-09-28 1250.00 1400. 7698 BLAKE Manager 7839 1981-05-01 2850.00 7782 CLARK manager 7839 1981-06-0   9 2450.00 7788 SCOTT ANALYST 7566 1987-04-19 3000.00 7839 KING President 1981-11-17 5000.00 7844 TURNER salesman 7698 1981-09-08 1500.00 0.00 7876 ADAMS Clerk 7788 1987-05-23 1100.00 20 7900               JAMES Clerk 7698 1981-12-03 950.00 7902 FORD ANALYST 7566 1981-12-03 3000.00 7934 MILLER Clerk 7782 1982-01-23 1300.00 34.56 1014 rows selected

Note: (1) grant Select can be swapped for INSERT, UPDATE, delete, all. (2) A column value of a data object (table or view) can also be granted to a specified user if the select authorizes a column value to the specified use to create the view and then authorizes the view to the specified user( Note: There are a lot of online use of Grant Select on EMP (ename,sal) to test, authorization, this can be successful under 9i, but after 9i is not allowed to grant Select a column of permissions, but can be granted insert, u Pdate permissions for a column )

Sql> Conn scott/scottconnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scottsql> C Reate View V_test as 2 Select Ename,sal from Emp;create view v_test asselect ename,sal from empORA-01031: Insufficient permissions sql> Co NN system/orcl; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as systemsql> Grant CREATE view to SCO tt  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 T Est Grant succeededsql> SELECT * from V_test; ename SAL-------------------SMITH 1800.00ALLEN 1600.00WARD 1250.00JONES 2975.00MA Rtin 1250.00BLAKE 2850.00CLARK 2450.00SCOTT 3000.00KING 5000.00TURNER 1500.00ADAM S 1100.00JAMES 950.00FORD 3000.00MILLER 1300.0014 rows selected 

authorized test users can only modify SAL fields.

sql> Grant Update (SAL) on the EMP to test; Grant succeeded

authorized test users can only insert Empno and EmpName fields.

Sql> Grant Insert (empno,ename) on the EMP to test; Grant succeeded

6, the user to recover the corresponding permissions. (Use the Scott user login to reclaim the permissions of the V_test query above and to modify the 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> R Evoke SELECT on v_test from TEST; Revoke succeededsql> Revoke UPDATE (SAL) on the EMP from TEST; REVOKE UPDATE (SAL) on EMP from Test<span style= "color: #ff0000;" >ora-01750:update/references can only be revoke</span> from the entire table and not by column

Sql> REVOKE UPDATE on the EMP from TEST; Revoke succeededsql> Revoke INSERT on EMP from TEST; Revoke succeeded

Note : Note that the above error indicates that a column of a table cannot be revoke only to the entire table revoke.

7, the transfer of permissions. (Use the Scott User Login authorization test user Select to query the V_test permissions and allow test users to have this permission passed to the Test1 user)

Sql> CONN scott/scottconnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scottsql> G RANT 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 T Estsql> GRANT SELECT on v_test to TEST1; GRANT SELECT on V_test to Test1<span style= "color: #ff0000;" >ora-00942: Table or view does not exist </span>SQL> GRANT SELECT on SCOTT. V_test to TEST1; Grant succeeded

Note: (1) The pass-through permission only needs to be granted with GRANT option to allow the authorized user to grant some permissions to other users. (2) If you are authorizing system permissions (which are object permissions above), you only need to add the WITH admin option if you are passing them to someone else. (3) at this point, if the Scott user to test the query V_test permission to recover will be test1 query v_test permission to recover.
8. Use profile to manage user passwords. profile is a collection of commands that are restricted by the password, and when the database is established, Oracle automatically establishes the name called default, and when the set-up does not specify the Profiles option, the Seek Oracle assigns default to the user. (set test user can only try to log in at most 2 times, lockout 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;" > force the user to unlock through the following </span>

sql> alter user test account unlock; User Altered

(set user to change password regularly, change their password every 10 days grace period is 3 days)

Sql> Create profile Remind_account limit password_life_time Password_grace_time 3; Profile createdsql> alter user test profile Remind_account; User Altered

Delete Direct drop profile Remind_account;

Oracle Walkthrough (First lecture)