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
