User Accounts

TOC

19c Acct Info

Change

ALTER USER <UserName> DEFAULT   TABLESPACE <tablespace_name>;
ALTER USER <UserName> TEMPORARY TABLESPACE <tablespace_name>;

ALTER USER scott DEFAULT TABLESPACE scott_data;
ALTER USER scott TEMPORARY TABLESPACE temp;

Create

CREATE USER <UserName> IDENTIFIED <BY "Password"|EXTERNALLY>
DEFAULT   TABLESPACE TablespaceName
TEMPORARY TABLESPACE TablespaceName;

CREATE USER scott IDENTIFIED BY "tiger"
DEFAULT   TABLESPACE users
TEMPORARY TABLESPACE temp;

Note double quotes used above.

-- Generate Random 16 Character Password

 SELECT translate(dbms_random.string('a', 15),dbms_random.string('a',6), 
        trunc(dbms_random.value(100000,999999))) ||
        (select substr('!@#$%^&*()+<>?{}|[]',trunc(dbms_random.value(1,19)), 1) from dual)
 FROM dual;

-- Grant Privs

 GRANT connect,resource TO scott;
 GRANT UNLIMITED TABLESPACE TO scott;

-- Test

 connect scott/tiger@MyInstance

Display

 -- All Oracle User Accounts
 SELECT user_id,username,password,account_status,
        default_tablespace,temporary_tablespace
 FROM dba_users
 ORDER BY username;

 -- Last Login
 SELECT username,last_login FROM dba_users WHERE username='SCOTT';

Drop

DROP USER <UserName> [CASCADE];

DROP USER scott CASCADE;

Expire

ALTER USER <UserName> [PASSWORD EXPIRE];

ALTER USER scott ACCOUNT LOCK PASSWORD EXPIRE;

Lock/Unlock

ALTER USER <UserName> ACCOUNT <LOCK|UNLOCK> [PASSWORD EXPIRE];

ALTER USER scott ACCOUNT LOCK;

Password

ALTER USER <UserName> IDENTIFIED BY "NewPassword";

ALTER USER scott IDENTIFIED BY "tiger";

-- Generate Random 16 Character Password

 SELECT translate(dbms_random.string('a', 15),dbms_random.string('a',6), 
        trunc(dbms_random.value(100000,999999))) ||
        (select substr('!@#$%^&*()+<>?{}|[]',trunc(dbms_random.value(1,19)), 1) from dual)
 FROM dual;

Tablespace Quota

ALTER USER <UserName> QUOTA <nnnM|nnnG|UNLIMITED> ON <TablespaceName>;

ALTER USER scott QUOTA unlimited ON users;

<- Security