Oracle PL/SQL/User Previliege/User Password

Alter user to change the password

   

SQL>
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> ALTER USER Alice IDENTIFIED BY complicatedpassword;
User altered.
SQL>
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL>

Change User password

 
SQL>
SQL> create user oracle_admin  identified by oracle_admin;
User created.
SQL>
SQL> grant create session, dba to oracle_admin;
Grant succeeded.
SQL>
SQL> alter user oracle_admin identified by oracle;
User altered.
SQL>
SQL> -- connect oracle_admin/oracle;
SQL>
SQL> DROP USER oracle_admin;
User dropped.
SQL>

create user chris identified by chris;

  
 grant connect, resource to chris;
  
 create user sean identified by sean;
  
 grant connect, resource to sean;
  
 create user mark identified by mark;
  
 grant connect, resource to mark;

Drop the user if it exists

   
SQL>
SQL> DEF username = plsql
SQL> DEF default_ts = USERS
SQL> DEF temp_ts = TEMP
SQL>
SQL> SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
SQL>
SQL> SPOOL create_user.log
SQL>
SQL> DECLARE
  2     v_count       INTEGER        := 0;
  3     v_statement   VARCHAR2 (500);
  4  BEGIN
  5
  6    
  7
  8     SELECT COUNT (1) INTO v_count FROM dba_users WHERE username = UPPER ("&username");
  9
 10     IF v_count != 0
 11     THEN
 12        EXECUTE IMMEDIATE ("DROP USER &username CASCADE");
 13     END IF;
 14
 15     v_count := 0;
 16
 17  EXCEPTION
 18     WHEN OTHERS
 19     THEN
 20        DBMS_OUTPUT.put_line (SQLERRM);
 21        DBMS_OUTPUT.put_line ("   ");
 22  END;
 23  /
SQL>
SQL> SET FEEDBACK ON TERMOUT ON
SQL>
SQL> SET FEEDBACK ON
SQL>
SQL> SPOOL OFF
SQL>
SQL>

Get user name from all_users

   
SQL>
SQL> select substr( username, 1, 1 )
  2    from all_users au1
  3   where rownum = 1;
S
-
S
1 row selected.

make a password expire with the ALTER USER command

  
ALTER USER hr IDENTIFIED BY hr PASSWORD EXPIRE;
--

Remove user

   
SQL>
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> ALTER USER Alice IDENTIFIED BY complicatedpassword;
User altered.
SQL>
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL>
SQL>

Unassign Role from User

   
SQL>
SQL> REVOKE TestRole FROM Alice;