Bài giảng Hệ quản trị CSDL Oracle - Chương 1: Giới thiệu các công cụ SQL Plus, iSQLPlus, OEM và ngôn ngữ truy vấn SQL

như DBA chứa tất cả các quyền của hệ thống. Ngôn ngữ điều khiển dữ liệuGiới thiệu Object Privileges (3) * Cú pháp CREATE ROLE NOT IDENTIFIED | IDENTIFIED BY Trong đó: - NOT IDENTIFIED: không đặt password cho role. - IDENTIFIED BY password: user phải nhập password để làm cho role có hiệu lực. Lưu ý: ký tự đầu tiên của password không được là số hoặc ký tự đặc biệt Ví dụ: tạo một role QuanTriSV với password là QT987654 CREATE ROLE QuanTriSV IDENTIFIED BY QT987654 Ngôn ngữ điều khiển dữ liệuTạo Role (nhóm quyền) (4) * Thiết lập/thay đổi/bỏ mật khẩu cho quyền Cú pháp: ALTER ROLE NOT IDENTIFIED | IDENTIFIED BY Ví dụ: đổi password mới cho role QuanTriSV là QT123456789 ALTER ROLE QuanTriSV IDENTIFIED BY QT123456789 Xóa quyền Cú pháp: DROP ROLE Ví dụ: xóa quyền QuanTriSV DROP ROLE QuanTriSV Gán quyền select/update/delete,.. đến public grant select on TênUser.TênTable to public Ngôn ngữ điều khiển dữ liệuThiết lập/thay đổi/bỏ mật khẩu cho Role + xóa Role (5) * Cú pháp CREATE USER NOT IDENTIFIED | IDENTIFIED BY default tablespace USERS Trong đó: - NOT IDENTIFIED: không đặt password cho user. - IDENTIFIED BY password: user phải nhập password mới có thể đăng nhập hệ thống. Ví dụ: tạo một user Phuong với password là P987654 CREATE USER Phuong IDENTIFIED BY P987654 CREATE USER Phuong1 IDENTIFIED BY P987654 default tablespace TEMP CREATE USER Phuong1 IDENTIFIED BY P987654 default tablespace USERS Ngôn ngữ điều khiển dữ liệuTạo người dùng (6) * Thiết lập/thay đổi/bỏ mật khẩu cho người dùng Cú pháp: ALTER USER NOT IDENTIFIED | IDENTIFIED BY Ví dụ: thay đổi password mới cho user Phuong là P123456789 ALTER USER Phuong IDENTIFIED BY P123456789 Xóa người dùng Cú pháp: DROP USER Ví dụ: xóa người dùng Phuong DROP USER Phuong Ngôn ngữ điều khiển dữ liệuThiết lập/thay đổi/bỏ mật khẩu cho người dùng + xóa người dùng (7) * Cú pháp cấp phát quyền cho người dùng GRANT privil1, privil2,…/ALL ON TO User1, User2,… [WITH GRANT OPTION] Cú pháp cấp phát quyền cho Role GRANT privil1, privil2,…/ALL ON TO Role1, Role2,… [WITH GRANT OPTION] Trong đó: : có thể là tên của một Table, View, Sequence, Synonym, Procedure, Function, Package. privil1, privil1,..là 1 trong 8 quyền hệ thống nêu trên để cấp quyền trên table hay view. Ngôn ngữ điều khiển dữ liệuCấp phát quyền (8) * Ban quyền truy xuất đến tất cả User, Role bằng lệnh GRANT privil1, privil2,…/ALL ON TO PUBLIC Ví dụ: grant select on TênUser.TênTable to public Ví dụ 1: cấp phát quyền cho user Phuong GRANT INSERT, UPDATE ON TênUserTạoTableNhanVien.NHANVIEN TO Phuong Ví dụ 2: cấp phát quyền cho role QuanTriSV GRANT ALL ON TênUserTạoTableNhanVien.NHANVIEN TO QuanTriSV WITH GRANT ADMIN Ngôn ngữ điều khiển dữ liệuCấp phát quyền (9) * Gán quyền Role cho User (User được cấp phát quyền Role) GRANT tên-Role TO tên-User [WITH GRANT OPTION] Ví dụ : cấp phát role QuanTriSV cho user Phuong GRANT QuanTriSV TO Phuong Ví dụ 2: cấp phát role QuanTriSV cho user Phuong GRANT QuanTriSV TO Phuong WITH GRANT ADMIN Ngôn ngữ điều khiển dữ liệuCấp phát quyền (10) * Cú pháp rút lại (hủy bỏ) các quyền đã cấp phát REVOKE privil1, privil2,…/Role1, Role2,… ON / FROM User1, User2,…/Role1, Role2,… Ví dụ - REVOKE UPDATE, DELETE ON NHANVIEN FROM Phuong - REVOKE ALL ON NHANVIEN FROM QuanTriSV Ngôn ngữ điều khiển dữ liệuThu hồi quyền (11) * -- This will grant read only access on all your objects to another schema begin for x in (select object_name from user_objects) loop execute immediate 'grant select on '|| x.object_name || ' to &schema'; end loop; end; Ngôn ngữ điều khiển dữ liệuVD đoạn PL/SQL cấp quyền (12) * To see which table privileges are granted by you to other users.  	SELECT * FROM USER_TAB_PRIVS_MADE To see which table privileges are granted to you by other users 	SELECT * FROM USER_TAB_PRIVS_RECD;   To see which column level privileges are granted by you to other users. 	SELECT * FROM USER_COL_PRIVS_MADE   To see which column level privileges are granted to you by other users 	SELECT * FROM USER_COL_PRIVS_RECD;   To see which privileges are granted to roles 	SELECT * FROM USER_ROLE_PRIVS; Ngôn ngữ điều khiển dữ liệuTuy vấn xem thông tin quyền (13) * GIAO TÁC : một Transaction là một giao tác trên CSDL bao gồm chuỗi các thay đổi (hành động) trên một hay nhiều table. Điều khiển Transaction bằng các lệnh sau. Lệnh COMMIT (hoàn tất giao tác) 	Cú pháp: COMMIT Lệnh ROLLBACK (phục hồi ngược lại chuỗi hành động đã thực hiện trước đó). 	Cú pháp: ROLLBACK [TO SAVEPOINT name] Lệnh SavePoint 	Cú pháp: SAVEPOINT tên-SavePoint Chế độ AutoCommit (AUTOCOMMIT ON/OFF ) 6. CÁC LỆNH GIAO TÁC (1) – TRANSACTION * Ví dụ: minh họa cách dùng các lệnh SavePoint, RollBack, Commit begin insert into student values(1,'Nguyen Van A'); savepoint A; insert into student values(2,'Nguyen Van B'); savepoint B; insert into student values(3,'Nguyen Van C'); savepoint C; insert into student values(4,'Nguyen Van D'); savepoint A; // savepoint A trước đó ko đc hiểu nữa insert into student values(5,'Nguyen Van E'); savepoint D; end; select * from student; -> kết quả 5 sinh viên rollback to savepoint A; //thải hồi lệnh ngược đến savepoint A select * from student; -> kết quả 4 sinh viên 6. CÁC LỆNH GIAO TÁC (2) – TRANSACTION * BEGIN 	 FOR IX IN 9..12 LOOP IF IX = 9 THEN INSERT INTO NUMBERS VALUES (IX); ELSIF IX = 11 THEN DELETE FROM NUMBERS; END IF; IF IX = 11 THEN ROLLBACK; ELSE COMMIT; END IF; 	END LOOP; 	 COMMIT; END; Giá trị nào được thêm vào bảng NUMBERS? Test trường hợp khác sử dụng lệnh drop table, lệnh DROP có được Rollback?? 6. CÁC LỆNH GIAO TÁC (3) – TRANSACTION * Synonym: Là tên đặt cho một đối tượng cụ thể nào đó. Thường dùng synonym để tạo ra những đối tượng dùng chung. Ví dụ: Một người dùng muốn sử dụng một bảng được sở hữu bởi người dùng khác thay vì gọi “tên_người_dùng.tên_bảng” thì user đó tạo ra một synonym cho bảng đó với một tên dễ nhớ nào đó, tên thật của đối tượng được che dấu đi. Các thao tác trên Synonym: tạo Synonym, xóa Synonym. 7. CÁC ĐỐI TƯỢNG KHÁC – Synonym (1) * Tạo Synonym: Cú pháp: CREATE SYNONYM tênSynonym FOR tênUser.tênTable|tênView Ví dụ: CREATE SYNONYM nv FOR phuong.NHANVIEN; Xóa Synonym: Cú pháp: DROP SYNONYM tên-synonym; Ví dụ: DROP SYNONYM nv; 7. CÁC ĐỐI TƯỢNG KHÁC – Synonym (2) * Index (tạo chỉ mục): sử dụng Oracle Index nhằm -	Tăng tốc độ xây dựng lại các dòng theo một khóa đặc biệt. -	Bảo đảm giá trị duy nhất trong cột, thường là giá trị primary key. Tạo Index Cú pháp: CREATE [UNIQUE] INDEX index_name ON table (column1 [, ] , …) 	 Ví dụ: 	CREATE UNIQUE INDEX i_cmnd ON NHAN_KHAU(SO_CMND); 7. CÁC ĐỐI TƯỢNG KHÁC – Index (1) * Phân loại index: UNIQUE: Bảo đảm giá trị trong các cột là duy nhất. (no two rows of a table have duplicate values in the key column (or columns) ) NONUNIQUE: (là default) kết quả truy vấn có thể nhanh nhất (do not impose this restriction on the column values) * Nếu là Single colum thì chỉ một cột tồn tại index. * Nếu là Concatenated Index: Trên 16 cột có thể chỉ ra trong index (giới hạn 30). 7. CÁC ĐỐI TƯỢNG KHÁC – Index (2) * (concatenated index also is called Composite Index - index that you create on multiple columns in a table ) Xóa index Cú pháp: DROP INDEX index_name; Ví dụ: DROP INDEX i_cmnd; CREATE INDEX VP_INDEX ON VENDOR_PARTS(VEND_ID,PART_NO); 7. CÁC ĐỐI TƯỢNG KHÁC – Index (3) * Sequence (giá trị được tạo tự động) 	Sequence là đối tượng tạo ra một dãy số liên tiếp một cách tự động, thường hay sử dụng trong câu lệnh INSERT để nhập dữ liệu cho bảng (ví dụ mã khách hàng tăng tự động). Tạo Sequence Cú pháp: 	CREATE SEQUENCE sequence_name 	[INCREMENT BY n] 	[START WITH n] 	[MAXVALUE n | NOMAXVALUE] 	[MINVALUE n | NOMINVALUE] 	[CYCLE | NOCYCLE] 	[CACHE n | NOCACHE] 	[ORDER | NOORDER] 7. CÁC ĐỐI TƯỢNG KHÁC – Sequence (1) * Ví dụ 	CREATE SEQUENCE s_nv 	MINVALUE 1 	MAXVALUE 9999999 	INCREMENT BY 1 	START WITH 100 	NOCACHE 	NOORDER 	NOCYCLE; Sử dụng Sequence 	Giá trị hiện hành và kế tiếp của dãy sequence được lưu trong hai cột: 	 - Giá trị hiện hành của sequence: tên_sequence.CURRVAL 	 - Giá trị kế tiếp của sequence: tên_sequence.NEXTVAL 7. CÁC ĐỐI TƯỢNG KHÁC – Sequence (2) * Hai cột trên trong Oracle được gọi là pseudo column (cột ảo), người dùng chỉ được truy xuất, không được cập nhật dữ liệu trong các cột này. Oracle có các cột ảo như: ROWID (mã dòng), ROWNUM (số thứ tự dòng), SYSDATE (ngày hiện hành của hệ thống),… Ví dụ 	Truy cập giá trị tiếp theo của sequence s_nv bằng lệnh: 	SELECT s_nv.nextval from DUAL; (bảng DUALlà tạm trong Oracle) 	Truy vấn giá trị hiện tại của sequence s_nv (nếu ko gọi s_nv.nextval một lần khi login vào session của mình trước, xem lỗi ở slide kế tiếp): 	SELECT s_nv.currval from DUAL; 	Sử dụng sequence trong một câu insert: 	INSERT INTO NHANVIEN (MA_NV, HO_TEN) VALUES (s_nv.nextval, ‘Nguyen van A’); /* s_nv là sequence đã tạo trước. */ 7. CÁC ĐỐI TƯỢNG KHÁC – Sequence (3) * ORA-08002: sequence string.CURRVAL is not yet defined in this session Cause: sequence CURRVAL has been selected before sequence NEXTVALAction: select NEXTVAL from the sequence before selecting CURRVAL Link xem các loại lỗi (Oracle Error Code Collections):  7. CÁC ĐỐI TƯỢNG KHÁC – Lỗi truy xuất CURRVAL nếu chưa khởi tạo giá trị Sequence (4) * Sửa Sequence (tạo lại các thông số) 	ALTER SEQUENCE tên-sequence 	[INCREMENT BY n] 	[START WITH n] 	[MAXVALUE n | NOMAXVALUE] 	[MINVALUE n | NOMINVALUE] 	[CYCLE | NOCYCLE] 	[CACHE n | NOCACHE] 	[ORDER | NOORDER] Xóa Sequence 	Cú pháp	DROP SEQUENCE tên-sequence Ví dụ: DROP SEQUENCE s_nv 7. CÁC ĐỐI TƯỢNG KHÁC – Sequence (5) * MỘT SỐ VIEW TỪ ĐIỂN THƯỜNG DÙNG USER_TABLES : xem các table người dùng đã tạo USER_VIEWS : xem các view người dùng đã tạo USER_INDEXES : xem các index người dùng tạo USER_SEQUENCES : xem các sequence người dùng tạo USER_FUNCTIONS : xem các function người dùng tạo USER_PROCEDURES : xem các procedure người dùng tạo USER_TRIGGERS : xem các trigger người dùng đã tạo user_sys_privs: xem quyền của user hiện hành ….. 8. TỪ ĐIỂN DỮ LIỆU – Oracle data dictionary * MỘT SỐ HÀM THƯỜNG DÙNG TRONG ORACLE HỌC VIÊN XEM FILE WORD ĐI KÈM