Oracle User Management: First, create a profile file.
SQL> Create profile file name limit FAILED_LOGIN_ATTEMPTS: Specifies the logged-on user to lock failures PASSWORD_LOCK_TIME: specified user is locked days PASSWORD_LIFE_TIME: Specifies the password of available days PASSWORD_REUSE_TIME: Specifies how long the password can not be reused PASSWORD_REUSE_MAX: specify a password needs to be changed before reuse passwords The number of times sessions_per_user: Limit the user concurrent session number CPU_PER_SESSION: Limit session The CPU time (one percent) CPU_PER_CALL: Limits the CPU time (one percent) of the CPU time occupied by the call) Connect_time: Limit the total session Connection time (minute) IDLE_TIME: Limit the idle time (minutes) of the session); Second, create a user (including creating a role):
SQL> CREATE User User Name Identified By Password DEFAULT TABLESPACE Table Space Name Temporary TableSpace Temporary Table Space Name PROFILE PROFILE File Name Quota Limit Space Size ON Table Space Name; Authorization: SQL> Grant Connect, Resource To User Name;
Example: - Create a role Drop role zelew;
Create role icdyw not ide
Grant "dba" to "iCDYW"; grant "exp_full_database" to "iCDYW"; GRANT "IMP_FULL_DATABASE" to "iCDYW";
- Create a user
Drop user icdmain cascade;
Create User Icdmain Identified by ICD DEFAULT TABLESPACE_RPT_DAT TEMPORARY TABLESPACE_TEMP Profile Default DEFAULT
Grant "IcDyw" to "Icdmain" with admin option;
Grant Unlimited TableSpace to "Icdmain" with admin/
Grant Select ON Sys.v_ $ MyStat to Icdmain; Grant SELECT ON SYS.V_ $ session to icdmain; grant select on sys.dba_jobs to icdmain
Alter User Icdmain Default Role All;
three. Change user:
One. Modify the user parameters: (In addition to the other items outside the username) SQL> ALTER USER Username Identified By Password DEFAULT TABLESPACE Table Space Name Temporary TableSpace Temporary Table Space Name Profile File Name Quota Limit Space Size ON Table Space Name ;
Packed users: SQL> ALTER USER Username Account Lock; - - Packed Account UNLOCK; - - Unlock Force User Modify Password: SQL> ALTER USER User Name Password Expire;
four. Delete users: There is no entity under the user: SQL> DROP USER username;
Users have created entities such as tables and data: SQL> DROP USER User Name Cascade; Note: The user currently connected should not be deleted. When using the Cascade option, the user and entity will be deleted immediately and should enter the data file for physical deletion.
Fives. Monitor users:
Query user session information: (if there are two users log in with the same username, you can dynamically query
SQL> Select Username Machine, SID, Serial # from v $ session; Delete User Session: SQL> ALTER SYSTEM KILL Session 'SID, SERIAL';
Display the user's SQL statement: SQL> SELECT USER _NAME, SQL _Text from V $ OPEN _CURSOR;
