I.QUẢN LÝ USER
1. Check
SELECT * FROM DBA_USERS;
select sid, serial#, username, osuser, machine from v$session where username is not NULL;
2. Create User
CREATE USER BINHTV IDENTIFIED BY binhtv DEFAULT TABLESPACE USERS;
GRANT CONNECT,RESOURCE, DBA TO BINHTV
--Detail
CREATE USER sidney
IDENTIFIED BY out_standing1
DEFAULT TABLESPACE example
QUOTA 10M ON example
TEMPORARY TABLESPACE temp
QUOTA 5M ON system
PROFILE app_user
PASSWORD EXPIRE;
3. Alter User
• Thay đổi mật khẩu
SQL> connect ecartman/southpark
Connected.
SQL> alter user ecartman identified by newpassword123#;
alter user ecartman identified by newpassword
*
ERROR at line 1:
ORA-28221: REPLACE not specified
SQL> alter user ecartman identified by newpassword123# replace southpark;
• Lock/Unlock
ALTER USER sidney ACCOUNT LOCK;
ALTER USER sidney ACCOUNT UNLOCK
(Khi lock user, các user khác vẫn truy cập được vào các object của user đó, khi mở lại user user đó không cần thay đổi mật khẩu)
• Set Quota
alter user phongnt quota 1G on users;
• Script reset pass dùng pass cũ (theo hash value)
Below is a little SQL*Plus script that a privileged user (e.g. user 'SYS') can use to reset a user's password to the current existing hashed value stored in the database.
REM Tell SQL*Plus to show before and after versions of variable substitutions
SET VERIFY ON
SHOW VERIFY
REM Tell SQL*Plus to use the ampersand '&' to indicate variable substitution expansion
SET DEFINE '&'
SHOW DEFINE
REM Specify in a SQL*Plus variable the account to 'reset'
DEFINE USER_NAME = 'xyz_user'
REM Show the status of the account before reset
SELECT
ACCOUNT_STATUS,
TO_CHAR(LOCK_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LOCK_DATE
FROM
DBA_USERS
WHERE
USERNAME = '&USER_NAME';
REM Create a SQL*Plus variable to hold the hash of existing password
DEFINE OLD_PASSWORD = ""
REM Tell SQL*Plus where to store the value to be selected with SQL
COLUMN PWORDHASH NEW_VALUE OLD_PASSWORD
REM Select the old hash password as a delimited string
SELECT
'''' || PASSWORD || '''' AS PWORDHASH
FROM
SYS.USER$
WHERE
NAME = '&USER_NAME';
REM Show the contents of the SQL*Plus variable
DEFINE OLD_PASSWORD
REM Reset the password
ALTER USER &USER_NAME IDENTIFIED BY VALUES &OLD_PASSWORD;
REM Show the status of the account after reset
SELECT
ACCOUNT_STATUS,
TO_CHAR(LOCK_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LOCK_DATE
FROM
DBA_USERS
WHERE
USERNAME = '&USER_NAME';
From <http://stackoverflow.com/questions/1095871/how-do-i-turn-off-oracle-password-expiration>
4. Drop user
DROP USER sidney;
• Xóa các object của user trước khi xóa user
DROP USER sidney CASCADE;
Khi lock user A thì các user khác có quyền vẫn truy cập được vào các object của user A
OS_AUTHENT_PREFIX = OPS$ You have a local operating system user SKD. You create a database user OPS$SKD, and then assign external
OS_ROLES = FALSE authentication. The user OPS$SKD has the CREATE SESSION privilege.
REMOTE_OS_AUTHENT = FALSE What would you achieve by the above process?
REMOTE_OS_ROLES = FALSE The local operating system user SKD will be able to access the database instance without specifying the
TIMED_OS_STATISTICS = 0 username and password.
5. User SYS
- Đổi mật khẩu SYS (hoặc user có quyền sysdba) trên node 1 thì phải đổi trên cả 2 node (hoặc copy file $ORACLE_HOME/dbs/orapwdbavietdb1 sang node 2 và đổi tên thành orapwdbavietdb2)
{dbavietdb1:oracle}/oracle/db11g/dbs -> csum -h MD5 orapwdbavietdb1
{dbavietdb2:oracle}/oracle/db11g/dbs -> csum -h MD5 orapwdbavietdb2
select * from v$pwfile_users;
- Với HT DataGuard phải copy file orapwdbavietdb1 sang Standby của Node 1 và file orapwdbavietdb2 sang Standby của Node 2
II. Quản lý Quyền trong Oracle Database
1. Check
select * from dictionary ORDER BY TABLE_NAME;
---SYSTEM PRIVILEGES
-- liệt kê quyền tương ứng cua DBA
-- User co quyen DBA
select * from DBA_ROLE_PRIVS where granted_role='DBA';
---OBJECT PRIVILEGES : Quyền trên object: table, view, package..
select * from dba_tab_privs where gratee like 'SYSTEM';
--Quyền trên SESSION
SELECT * FROM SESSION_PRIVS; // chi tiết quyền của session hiện tại
select * from SESSION_ROLES;
2. Gán quyền
NHÓM QUYỀN SYSTEM PRIVILEGES
Grant Select any table to <user>
Truy vấn trên bất cứ table nào
Grant insert any table to <user>
Thêm mới trên bất cứ table nào
Grant update any table to <user>
Cập nhật trên bất cứ table nào
Grant delete any table to <user>
Xóa trên bất cứ table nào
FLASHBACK
grant flashback on test_owner.emp to binhtv
select required clause timestamp (select /*flashback*/* from test_owner.tab1 as of timestamp(to_timestamp('2014/09/209 10:05:00','YYYY/MM/DD HH24:MI')) where config_id is null)
---PROCEDURES, FUNTIONS
GRANT CREATE ANY PROCEDURE, ALTER ANY PROCEDURE, EXECUTE ANY PROCEDURE, DROP ANY PROCEDURE TO USER1
GRANT EXECUTE on LF_PORTAL.<function_name||procedure_name> TO USER1;
--Gán quyền cho tạo procedure trên schema của user1 - PACKAGES
GRANT CREATE PROCEDURE, ALTER PROCEDURE, EXECUTE PROCEDURE, DROP PROCEDURE TO USER1
GRANT ALTER, EXECUTE ON TEST_OWNER.PCK__TEST TO TEST_OWNER
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO BINHTV
-Gán FULL quyền trên package TEST_OWNER.PCK_TEST cho user TEST_OWNER
GRANT CREATE session, CREATE table, CREATE view, GRANT ALL ON TEST_OWNER.PCK_TEST TO TEST_OWNER
CREATE procedure,CREATE synonym,
ALTER table, ALTER view, ALTER procedure,ALTER synonym,
GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY,
AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER
TO security_admin WITH ADMIN OPTION;
- Quyền view function, procedure, package
grant SELECT on DBA_SOURCE to binhtv; // phai co quyen SYSDBA moi gan duoc
GRANT DEBUG ON A.PACKAGE_NAME TO binhtv
- Quyen view moi package:
grant SELECT ANY DICTIONARY to test_owner
grant debug any procedure to test_owner
--Sequence
GRANT CREATE SEQUENCE, ALTER ANY SEQUENCE, SELECT ANY SEQUENCE TO test_owner
GRANT SELECT ANY SEQUENCE TO test_owner
NHÓM QUYỀN OBJECT PRIVILEGES
GRANT SELECT, INSERT, UPDATE, DELETE ON TEST_OWNER.TAB1 TO test_owner
- Trên cột
GRANT INSERT (ename, job) ON emp TO swilliams, jward;
grant update (ename),insert (empno, ename) on emp to sami;
ROLE
/* User chỉ có quyền trên schema hiện tại cần Role: Connect, Resource, ko tạo được object trên schma khác */
GRANT CONNECT, RESOURCE TO BINHTV
GRANT SYSDBA TO BINHTV
- Không được gán quyền ROLE cho role khác
3. REVOKE
-- System privileges
REVOKE DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE FROM USER1
REVOKE ALL ON dept FROM human_resources;
-- Object privileges
REVOKE SELECT, INSERT, UPDATE insert ON HR.emp FROM jfee, tsmith;
-- Script thu hồi toàn bộ quyền hệ thống của user user1
select 'revoke ' || privilege || ' from ' ||GRANTEE||';'
from DBA_SYS_PRIVS where grantee like 'USER1';
-- Script thu hồi toàn bộ quyền table của user user1
select 'revoke ' || privilege || ' on ' || owner || '.' || table_name ||' from ' ||GRANTEE||';'
from dba_tab_privs where gratee like 'USER1';
4. Others
Chú ý ADMIN OPTION, GRANT OPTION
Danh sách quyền hệ thống:
PRIVILEGE ---------------------------------------- ADMINISTER ANY SQL TUNING SET ADMINISTER DATABASE TRIGGER ADMINISTER RESOURCE MANAGER ADMINISTER SQL MANAGEMENT OBJECT ADMINISTER SQL TUNING SET ADVISOR ALTER ANY ANALYTIC VIEW ALTER ANY ASSEMBLY ALTER ANY ATTRIBUTE DIMENSION ALTER ANY CLUSTER ALTER ANY CUBE PRIVILEGE ---------------------------------------- ALTER ANY CUBE BUILD PROCESS ALTER ANY CUBE DIMENSION ALTER ANY DIMENSION ALTER ANY EDITION ALTER ANY EVALUATION CONTEXT ALTER ANY HIERARCHY ALTER ANY INDEX ALTER ANY INDEXTYPE ALTER ANY LIBRARY ALTER ANY MATERIALIZED VIEW ALTER ANY MEASURE FOLDER PRIVILEGE ---------------------------------------- ALTER ANY MINING MODEL ALTER ANY OPERATOR ALTER ANY OUTLINE ALTER ANY PROCEDURE ALTER ANY ROLE ALTER ANY RULE ALTER ANY RULE SET ALTER ANY SEQUENCE ALTER ANY SQL PROFILE ALTER ANY SQL TRANSLATION PROFILE ALTER ANY TABLE PRIVILEGE ---------------------------------------- ALTER ANY TRIGGER ALTER ANY TYPE ALTER DATABASE ALTER LOCKDOWN PROFILE ALTER PROFILE ALTER RESOURCE COST ALTER ROLLBACK SEGMENT ALTER SESSION ALTER SYSTEM ALTER TABLESPACE ALTER USER PRIVILEGE ---------------------------------------- ANALYZE ANY AUDIT ANY AUDIT SYSTEM BACKUP ANY TABLE BECOME USER CHANGE NOTIFICATION COMMENT ANY MINING MODEL COMMENT ANY TABLE CREATE ANALYTIC VIEW CREATE ANY ANALYTIC VIEW CREATE ANY ASSEMBLY PRIVILEGE ---------------------------------------- CREATE ANY ATTRIBUTE DIMENSION CREATE ANY CLUSTER CREATE ANY CONTEXT CREATE ANY CREDENTIAL CREATE ANY CUBE CREATE ANY CUBE BUILD PROCESS CREATE ANY CUBE DIMENSION CREATE ANY DIMENSION CREATE ANY DIRECTORY CREATE ANY EDITION CREATE ANY EVALUATION CONTEXT PRIVILEGE ---------------------------------------- CREATE ANY HIERARCHY CREATE ANY INDEX CREATE ANY INDEXTYPE CREATE ANY JOB CREATE ANY LIBRARY CREATE ANY MATERIALIZED VIEW CREATE ANY MEASURE FOLDER CREATE ANY MINING MODEL CREATE ANY OPERATOR CREATE ANY OUTLINE CREATE ANY PROCEDURE PRIVILEGE ---------------------------------------- CREATE ANY RULE CREATE ANY RULE SET CREATE ANY SEQUENCE CREATE ANY SQL PROFILE CREATE ANY SQL TRANSLATION PROFILE CREATE ANY SYNONYM CREATE ANY TABLE CREATE ANY TRIGGER CREATE ANY TYPE CREATE ANY VIEW CREATE ASSEMBLY PRIVILEGE ---------------------------------------- CREATE ATTRIBUTE DIMENSION CREATE CLUSTER CREATE CREDENTIAL CREATE CUBE CREATE CUBE BUILD PROCESS CREATE CUBE DIMENSION CREATE DATABASE LINK CREATE DIMENSION CREATE EVALUATION CONTEXT CREATE EXTERNAL JOB CREATE HIERARCHY PRIVILEGE ---------------------------------------- CREATE INDEXTYPE CREATE JOB CREATE LIBRARY CREATE LOCKDOWN PROFILE CREATE MATERIALIZED VIEW CREATE MEASURE FOLDER CREATE MINING MODEL CREATE OPERATOR CREATE PLUGGABLE DATABASE CREATE PROCEDURE CREATE PROFILE PRIVILEGE ---------------------------------------- CREATE PUBLIC DATABASE LINK CREATE PUBLIC SYNONYM CREATE ROLE CREATE ROLLBACK SEGMENT CREATE RULE CREATE RULE SET CREATE SEQUENCE CREATE SESSION CREATE SQL TRANSLATION PROFILE CREATE SYNONYM CREATE TABLE PRIVILEGE ---------------------------------------- CREATE TABLESPACE CREATE TRIGGER CREATE TYPE CREATE USER CREATE VIEW DEBUG ANY PROCEDURE DEBUG CONNECT ANY DEBUG CONNECT SESSION DELETE ANY CUBE DIMENSION DELETE ANY MEASURE FOLDER DELETE ANY TABLE PRIVILEGE ---------------------------------------- DEQUEUE ANY QUEUE DROP ANY ANALYTIC VIEW DROP ANY ASSEMBLY DROP ANY ATTRIBUTE DIMENSION DROP ANY CLUSTER DROP ANY CONTEXT DROP ANY CUBE DROP ANY CUBE BUILD PROCESS DROP ANY CUBE DIMENSION DROP ANY DIMENSION DROP ANY DIRECTORY PRIVILEGE ---------------------------------------- DROP ANY EDITION DROP ANY EVALUATION CONTEXT DROP ANY HIERARCHY DROP ANY INDEX DROP ANY INDEXTYPE DROP ANY LIBRARY DROP ANY MATERIALIZED VIEW DROP ANY MEASURE FOLDER DROP ANY MINING MODEL DROP ANY OPERATOR DROP ANY OUTLINE PRIVILEGE ---------------------------------------- DROP ANY PROCEDURE DROP ANY ROLE DROP ANY RULE DROP ANY RULE SET DROP ANY SEQUENCE DROP ANY SQL PROFILE DROP ANY SQL TRANSLATION PROFILE DROP ANY SYNONYM DROP ANY TABLE DROP ANY TRIGGER DROP ANY TYPE PRIVILEGE ---------------------------------------- DROP ANY VIEW DROP LOCKDOWN PROFILE DROP PROFILE DROP PUBLIC DATABASE LINK DROP PUBLIC SYNONYM DROP ROLLBACK SEGMENT DROP TABLESPACE DROP USER EM EXPRESS CONNECT ENQUEUE ANY QUEUE EXECUTE ANY ASSEMBLY PRIVILEGE ---------------------------------------- EXECUTE ANY CLASS EXECUTE ANY EVALUATION CONTEXT EXECUTE ANY INDEXTYPE EXECUTE ANY LIBRARY EXECUTE ANY OPERATOR EXECUTE ANY PROCEDURE EXECUTE ANY PROGRAM EXECUTE ANY RULE EXECUTE ANY RULE SET EXECUTE ANY TYPE EXECUTE ASSEMBLY PRIVILEGE ---------------------------------------- EXEMPT DDL REDACTION POLICY EXEMPT DML REDACTION POLICY EXPORT FULL DATABASE FLASHBACK ANY TABLE FLASHBACK ARCHIVE ADMINISTER FORCE ANY TRANSACTION FORCE TRANSACTION GLOBAL QUERY REWRITE GRANT ANY OBJECT PRIVILEGE GRANT ANY PRIVILEGE GRANT ANY ROLE PRIVILEGE ---------------------------------------- IMPORT FULL DATABASE INSERT ANY CUBE DIMENSION INSERT ANY MEASURE FOLDER INSERT ANY TABLE LOCK ANY TABLE LOGMINING MANAGE ANY FILE GROUP MANAGE ANY QUEUE MANAGE FILE GROUP MANAGE SCHEDULER MANAGE TABLESPACE PRIVILEGE ---------------------------------------- MERGE ANY VIEW ON COMMIT REFRESH QUERY REWRITE READ ANY FILE GROUP READ ANY TABLE REDEFINE ANY TABLE RESTRICTED SESSION RESUMABLE SELECT ANY CUBE SELECT ANY CUBE BUILD PROCESS SELECT ANY CUBE DIMENSION PRIVILEGE ---------------------------------------- SELECT ANY MEASURE FOLDER SELECT ANY MINING MODEL SELECT ANY SEQUENCE SELECT ANY TABLE SELECT ANY TRANSACTION SET CONTAINER UNDER ANY TABLE UNDER ANY TYPE UNDER ANY VIEW UNLIMITED TABLESPACE UPDATE ANY CUBE PRIVILEGE ---------------------------------------- UPDATE ANY CUBE BUILD PROCESS UPDATE ANY CUBE DIMENSION UPDATE ANY TABLE USE ANY JOB RESOURCE
USE ANY SQL TRANSLATION PROFILE
III. Quản lý Profile trong Oracle Database
Thuộc tính session per user được đặt cho mỗi node
CREATE PROFILE
CREATE PROFILE BINH_OWNER LIMIT
SESSIONS_PER_USER 200
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME DEFAULT
IDLE_TIME DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
COMPOSITE_LIMIT DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS DEFAULT
PASSWORD_LIFE_TIME DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT;
ALTER PROFILE
ALTER PROFILE new_profile
LIMIT PASSWORD_REUSE_TIME 90
PASSWORD_REUSE_MAX UNLIMITED;
ALTER PROFILE PASSWORD_LIFE_TIME UNLIMITED;
Select * from dba_profiles where profile='DEFAULT';
Alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited;
DROP PROFILE
DROP PROFILE BINH_OWNER CASCADE;
(Không drop được profile khi user đang kết nối vào DB sử dụng profile đó, nếu dùng câu lệnh sẽ báo lỗi)
IV.Quản lý Role trong Oracle Database
ĐN: là nhóm quyền (privilege)
DBA_ROLES DS role
DBA_ROLE_PRIVS - Role gán cho user
USER_ROLE_PRIVS - Role gán cho user hiện tại
ROLE: PRIVILEGES:
CONNECT ALTER SESSION
CREATE CLUSTER
CREATE DATABASE LINK
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE VIEW
RESOURCE CREATE CLUSTER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
DBA ALL PRIVILEGES WITH ADMIN OPTION
(allows grants to other users or roles)
EXP_FULL_DATABASE SELECT ANY TABLE, BACKUP ANY TABLE.
INSERT, DELETE, and UPDATE on the tables
SYS.INCVID, SYS.INCFIL, and SYS.INCEXP
IMP_FULL_DATABASE BECOME USER
WRITEDOWN (trusted Oracle only)
• Select * from dba_roles; // list all role
• SELECT * FROM SESSION_ROLES; //Listing the Current Privilege Domain of Your Session
• Select * from DBA_ROLE_PRIVS; // Listing All Role Grants for User// 33 rows
• select * from USER_ROLE_PRIVS; // role đã gán cho user hiện tại
• select * from ROLE_ROLE_PRIVS
CREATE ROLE
CREATE ROLE dw_manager;
CREATE ROLE dw_manager IDENTIFIED BY warehouse;
ALTER ROLE
• Changing Role Identification:
ALTER ROLE warehouse_user NOT IDENTIFIED;
• Changing a Role Password
ALTER ROLE dw_manager IDENTIFIED BY data;
• Application Roles:
ALTER ROLE dw_manager IDENTIFIED USING hr.admin;
DROP ROLE
DROP ROLE dw_manager;
SET ROLE
• Setting Roles:
To enable the role dw_manager identified by the password warehouse for your current session, issue the following statement:
SET ROLE dw_manager IDENTIFIED BY warehouse;
• To enable all roles granted to you for the current session, issue the following statement:
SET ROLE ALL;
• To enable all roles granted to you except dw_manager, issue the following statement:
SET ROLE ALL EXCEPT dw_manager;
• To disable all roles granted to you for the current session, issue the following statement:
SET ROLE NONE;
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, securecrt, xshell, mobaxterm, putty
