Oracle account management

1. User Creation/Deletion

create

SQL> create user web_hgz identified by admin123;

User created.

SQL> col username format a10; --col for column abbreviation, format a10 specifies a format width of 10 characters

SQL> col account_status format a15;

SQL> select username, account_status, lock_date, expiry_date from dba_users where username = 'web_hgz';

USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE

---------- --------------- ------------------- -------------------

web_hgz OPEN

delete

SQL> drop user"name"cascade;

2. Change user password

SQL> alter user HR identified by admin123;

Before modification:

SQL> select username,account_status,lock_date, expiry_date from dba_users where username ='HR';

USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA

---------- --------------- --------- ---------

HR EXPIRED 29-NOV-15

After modification:

SQL> select username,account_status,lock_date, expiry_date from dba_users where username ='HR';

USERNAME ACCOUNT_STATUS LOCK_DATE

------------------------------ -------------------------------- ---------

EXPIRY_DA

---------

HR OPEN

3. Unlock the user

SQL> alter user hr account unlock;

User altered.

SQL> select a.username, a.account_status, a.lock_date from dba_users a where a.username = 'HR';

USERNAME ACCOUNT_STATUS LOCK_DATE

---------- -------------------------------- -------------------

HR OPEN

4. Oracle user login failure times limit modification

a. Query the maximum number of consecutive password input errors. If this value is exceeded, the account will be locked:

SQL> select * from dba_profiles s where s.profile ='DEFAULT ' and resource_name ='FAILS_LOGIN_ATTEMPTS'; //FAILS_LOGIN_ATTEMPTS indicates the maximum number of errors

b. Modify the number of failed logins

SQL>alter profile default limit failed_login_attempts unlimited; //Indicates unlimited

SQL>alter profile default limit failed_login_attempts 3; //indicates that the limit is 3 times

Display effect:

[oracle@localhost ~]$ sqlplus hr/admin123

ERROR:

ORA-28000: the account is locked

5. Give users table space permissions

SQL>alter user ittbank default tablespace ittbank;

6. Grant users administrative rights:

grant connect,resource,dba to ittbank ;

7. View the role of the current user

select * from user_role_privs;

select * from session_privs;

8. View the system permissions and table-level permissions of the current user

select * from user_sys_privs;

select * from user_tab_privs;

9. Query user table

select name from dba_users;

10. Display the current user

show user;