Bài giảng Hệ quản trị cơ sở dữ liệu (Oracle) - Chương 6: Quản trị người dùng - Nguyễn Thị Mỹ Dung

Bài giảng HQT Cơ sở dữ liệu (ORACLE) Số tc: 3; LT: 25; Btập: 20 GV: Nguyễn Thị Mỹ Dung Khối lớp: Đại học L2 1 NỘI DUNG MÔN HỌC 2 1 2 3 4 Chương 1: Tổng quan về Oracle (2) Chương 2: Cơ bản về Oracle (2) Chương 3: Truy vấn SQL (12) Chương 4: Lập trình PL/SQL (9) 5 6 Chương 5: Procedure, Trigger, Function (12) Chương 6: Quản trị người dùng (4) 7 Chương 7: Kết nối CSDL với Oracle (4) Ch6: Quản trị người dùng I. Các thành phần trong quản trị người dùng II. Quản lý user III. Quản lý quyền hệ thống (privileges) IV. Quản lý quyền đối tượng V. Quản lý nhóm (Role) VI. Bài tập thực hành 3 I. Các thành phần Việc phân quyền sử dụng là cần thiết trong công việc quản trị. Có hai user account được tự động tạo ra ngay từ khi cài đặt và gán quyền quản trị CSDL (DBA - DataBase Administration) là: SYS và SYSTEM. - DBA: cho phép thực hiện các thao tác quản trị đối với CSDL. - SYS: được tạo tự động và gán quyền DBA. SYS sở hữu các bảng và danh sách từ điển trong CSDL, mật khẩu khi cài đặt - SYSTEM: có quyền DBA, sở hữu table, view, mở rộng thông tin cho các tool của Oracle. Mật khẩu mặc định là manager. 4 Các thành phần (tt) - Cơ chế xác nhận (Authentication Mechanism): xác nhận quyền truy cập thực hiện bởi DB hoặc HĐH. - Hạn mức tablespace (tablespace Quotas): điều khiển số lượng không gian bảng ứng với mỗi user. - Không gian bảng mặc định (Default tablespace): để lưu trữ dữ liệu trong trường hợp user không chỉ rõ tablespace khi tạo segment. - Không gian bảng trung gian (Temporary Tablespace): chứa các extents cho việc SORT cho các câu lệnh của user. 5 Các thành phần (tt) - Khoá tài khoản (Account Locking): Ngăn chặn tài khoản thâm nhập CSDL được thực hiện tự động hoặc người quản trị CSDL. - Hạn chế tài nguyên (Resource Limits): những giới hạn đưa ra cho mỗi user như: thời gian sử dụng CPU, truy xuất I/O, số lượng giao dịch được mở, - DB schema: tập hợp các tables, views, procedures, gắn với 1 user. Mỗi user chỉ có một schema cùng tên. 6 II. Quản lý user 1. Tạo USER Các bước thực hiện khi tạo mới user: 1. Lựa chọn username và cơ chế xác nhận 2. Chỉ ra các tablespaces để lưu dữ liệu 3. Phân bổ hạn mức sử dụng trên từng tablespace 4. Gán các default tablespace và temporary tablespace 5. Phân quyền truy nhập (privileges, roles) 7 Quản lý user (tt) Cú pháp: CREATE USER username IDENTIFIED {BY password | EXTERNALLY} [DEFAULT TABLESPACE tablespace] [TEMPORARY TABLESPACE tablespace] [QUOTA {integer [K | M ] | UNLIMITED} ON tablespace [QUOTA {integer [K | M ] | UNLIMITED } ON tablespace] ...] 8 Quản lý user (tt) Trong đó:  Username: Tên truy nhập của user  BY password: Xác định cơ chế xác nhận user với mật khẩu (password).  EXTERNALLY: Xác định cơ chế xác nhận user bởi HĐH  DEFAULT | TEMPORARY TABLESPACE: Xác định default/temporary tablespace cho user.  QUOTA: Xác định không gian lưu trữ tối đa cấp cho user để lưu trữ các đối tượng trong tửng tablespace tương ứng.  Từ khoá UNLIMITED cho biết không giới hạn không gian cấp phát. 9 Quản lý user (tt) VD: Tạo người dùng có tên USERTEST, mật khẩu cùng tên, cấp 1M Quota trên tablespace USERS. CREATE USER userTest IDENTIFIED BY usertest DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 1M ON USERS; 10 Quản lý user (tt) 2. Thay đổi mật khẩu USER ALTER USER tênuser [ IDENTIFIED {BY password | EXTERNALLY}] VD: Đổi mật khẩu người dùng usertest thành 123456 ALTER USER userTest IDENTIFIED BY 123456 11 Quản lý user (tt) 3. Thay đổi hạn mức Quota Cú pháp: ALTER USER user [DEFAULT TABLESPACE tablespace] [TEMPORARY TABLESPACE tablespace] [QUOTA {integer [K | M] | UNLIMITED } ON tablespace [QUOTA {integer [K | M] | UNLIMITED } ON tablespace ] ... ] 12 Quản lý user (tt) VD: Tăng hạn mức cho người dùng userTest lên 2M. ALTER USER userTest QUOTA 2M ON USERS; 4. Xoá người dùng DROP USER TênUser [CASCADE] - CASCADE: xoá tất cả các đối tượng trong schema trước khi xoá User. - Không thể xoá được các user hiện đang kết nối với Oracle Server. 13 Quản lý user (tt) 5. Xem thông tin USER Ta có thể xem các thông tin liên quan tới user trong data dictionary DBA_USERS và DBA_TS_QUOTAS. VD1: Xem các thông tin user UserTest SELECT tablespace_name, blocks, max_blocks, bytes, max_bytes FROM dba_ts_quotas; WHERE username = 'UserTest'; VD2: Xem các thông tin về Account của user SELECT username, account_status, temporary_tablespace FROM dba_users; 14 II. Quản lý hệ thống (System Privileges) 1. Các quyền quản lý hệ thống Quản lý quyền hệ thống để thực hiện các công việc trong CSDL. Có khoảng 80 quyền, được chia là 3 loại: table, session và tablespace. - Các quyền cho phép thực hiện các thao tác truy cập, tạo dung lượng lưu trữ trên hệ thống: CREATE SESSION, CREATE TABLESPACE. - Các quyền quản lý đối tượng: CREATE TABLE hoặc quản lý bất kỳ một schema nào: CREATE ANY TABLE. 15 Quản lý hệ thống (tt) Có thể điều khiển các quyền bằng GRANT hay REVOKE 16 PHÂN LOẠI CÁC QUYỀN THÔNG DỤNG TABLE CREATE TABLE | TRIGGER | PROCEDURE CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE SESSION CREATE SESSION ALTER SESSION RESTRICTED SESSION TABLESPACE CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE UNLIMITED TABLESPACE Quản lý hệ thống (tt) 2. Cấp quyền hệ thống GRANT {system_priv|role} [, {system_priv|role} ]... TO {user|role|PUBLIC} [, {user|role|PUBLIC} ]... [WITH ADMIN OPTION] Trong đó: - system_priv: chỉ định quyền hệ thống sử dụng - role: chỉ định tên Role được gán - PUBLIC: gán quyền hệ thống cho tất cả user WITH ADMIN OPTION: cho phép user được gán quyền có thể cấp quyền hay Role đó cho user khác. 17 Quản lý hệ thống (tt) VD1: Cấp quyền cho UserTest GRANT CREATE SESSION, CREATE TABLE TO UserTest; VD2: Cấp cho userTest có quyền của mình cho user khác GRANT CREATE SESSION, CREATE TABLE TO UserTest WITH ADMIN OPTION; 18 Quản lý hệ thống (tt) 3. Thu hồi quyền hệ thống Câu lệnh REVOKE có thể lấy lại quyền đã được gán bằng lệnh GRANT. REVOKE {system_priv|role} [,{system_priv|role} ]... FROM {user|role|PUBLIC} [, {user|role|PUBLIC} ]... Ví dụ: REVOKE CREATE TABLE FROM UserTest;  Thu hồi các quyền hệ thống có thể ảnh hưởng đến các đối tượng phụ thuộc như: view, procedure, 19 Quản lý hệ thống (tt) 4. Xem quyền hệ thống Các thông tin bao gồm:  DBA_SYS_PRIVS: GRANTEE, PRIVILEGE, ADMIN OPTION  SESSION_PRIVS: PRIVILEGE VD1: Xem các quyền hệ thống được gán cho user và role SELECT * FROM DBA_SYS_PRIVS; VD2: Xem các quyền hiện có của user SELECT * FROM session_privs; 20 III. Quản lý đối tượng 1. Quyền trên đối tượng Mỗi quyền trên đối tượng được gán cho phép người dùng được thực thi một số thao tác trên đối tượng. Danh sách các quyền trên đối tượng: 21 Quyền Table View Procedure ALTER X DELETE X X EXECUTE X INSERT X X SELECT X X UPDATE X X Quản lý đối tượng (tt) 2. Gán quyền cho đối tượng Cú pháp: GRANT { object_priv [(column_list)] [, object_priv [(column_list)] ]... |ALL [PRIVILEGES]} ON [schema.]object TO {user|role|PUBLIC} [, {user|role|PUBLIC} ]... [WITH GRANT OPTION] 22 Quản lý đối tượng (tt) Trong đó:  object_priv: Chỉ định quyền đối tượng được gán  column_list: Chỉ định các cột của Table hay view (chỉ khi sử dụng INSERT hay UPDATE).  ALL: Gán tất cả quyền cho đối tượng đã được gán với WITH ADMIN OPTION.  ON object: Chỉ định các đối tượng trên đó có quyền được gán.  WITH GRANT OPTION: Cho phép người được gán quyền được gán quyền đó cho user khác. VD: GRANT select, update ON SINHVIEN TO UserTest; 23 Quản lý đối tượng (tt) 3. Thu hồi các quyền trên đối tượng REVOKE { object_priv [, object_priv ]... |ALL [PRIVILEGES] } ON [schema.]object FROM {user|role|PUBLIC} [, {user|role|PUBLIC} ]... [CASCADE CONSTRAINTS] 24 Quản lý đối tượng (tt) Trong đó:  object_priv: Chỉ định quyền trên đối tượng đã được gán.  ALL: Thu hồi tất cả các quyền trên đối tượng đã được gán cho user.  ON: Chỉ định đối tượng trên đó được thu hồi.  FROM: Chỉ định user hay role bị thu hồi quyền.  CASCADE CONSTRAINTS: Xoá tất cả các tham chiếu. VD: REVOKE update ON SINHVIEN FROM UserTest [CASCADE CONSTRAINTS]; 25 Quản lý đối tượng (tt) 4. Xem thông tin về quyền đối tượng DBA_TAB_PRIVS: GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE DBA_COL_PRIVS: GRANTEE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTOR, PRIVILEGE, GRANTABLE VD: SELECT * FROM dba_tab_privs WHERE OWNER= 'UserTest'; 26 IV. Quản lý nhóm (Role) 1. Quản lý nhóm Role là một nhóm các quyền được gán cho các user hay Role khác. 27 Quản lý nhóm (tt) Các tính chất của quản lý nhóm: - Được gán và lấy lại quyền từ user cùng 1 lệnh. - Có thể gán cho bất kỳ user hay role nào 1 role trừ chính nó. - Có thể bao gồm cả quyền hệ thống và quyền đối tượng. - Có thể enable hoặc disable các role được gán cho các user. - Có thể yêu cầu mật khẩu khi cần enable các role. - Tên các role là duy nhất. - Không phụ thuộc bất kỳ user hay schema nào. 28 Quản lý nhóm (tt) 2. Tạo nhóm CREATE ROLE role_name [NOT IDENTIFIED | IDENTIFIED {BY password | EXTERNALLY }] VD: Tạo nhóm có tên CNTTGroup CREATE ROLE CNTTGroup; 3. Sửa nhóm ALTER ROLE role_name {NOT IDENTIFIED | IDENTIFIED {BY password | EXTERNALLY }}; VD: Cấp thêm mật khẩu cho nhóm CNTTGroup ALTER ROLE CNTTGroup IDENTIFIED BY 123456 29 Quản lý nhóm (tt) 4. Gán Role cho user GRANT role_name [, role_name ]... TO {user|role|PUBLIC} [, {user|role|PUBLIC} ]... [WITH ADMIN OPTION] VD: GRANT CNTTGroup TO UserTest; 5. Thiết lập Role mặc định ALTER USER user DEFAULT ROLE {role [,role]... | ALL [EXCEPT role [,role]... ] } VD: ALTER USER UserTest DEFAULT ROLE CNTTGroup; 30 Quản lý nhóm (tt) 6.Thu hồi Role REVOKE role_name [, role_name ]... FROM {user|role|PUBLIC} [,{user|role|PUBLIC} ]... VD: REVOKE CNTTGroup FROM UserTest; 7. Xoá Role DROP ROLE role_name; 8. Xem thông tin Role SELECT Role, password_required FROM dba_Roles; 31 V. Bài tập thực hành 1. Tạo người dùng tương ứng với mật khẩu giống tên. Sau đó thực hiện cấp quyền Session cho User này. 2. Thay đổi mật khẩu cho user này thành DH13L2 3. Tạo người dùng khác tên KT1234 với Quota 2M cho phép người dùng tạo bảng Sinhvien (Masv, TenSV) trong schema này. 4. Cấp quyền cho user ở câu 1 truy cập vào bảng Sinhvien (câu 3). 5. Tạo nhiều hơn 1 người dùng, sau đó tạo nhóm THOracle, cấp quyền: kết nối CSDL, tạo bảng, tạo thủ tục, tạo trigger cho nhóm này. Thực hiện cấp quyền cho các user vào nhóm THOracle. 6. Thu hồi quyền UPDATE người dùng trên bảng Sinhvien ở câu 4. 7. Bỏ một người dùng ra khỏi nhóm THOracle. 32 Tổng kết chương - Tìm hiểu các thành phần trong quản trị người dùng. - Các thao tác thực hiện trên quản lý user (user) - Các thao tác thực hiện trên quản lý quyền hệ thống (privileges) - Các thao tác thực hiện trên quản lý quyền trên đối tượng (Insert, Update,) - Các thao tác quản lý trên nhóm (Role). 33