Oracle PL/SQL Tutorial/User Privilege/Create User

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>

Creates a user named henry and specifies a default and temporary tablespace

SQL>
SQL> CREATE USER henry IDENTIFIED BY pass
  2  DEFAULT TABLESPACE users
  3  TEMPORARY TABLESPACE temp;
User created.
SQL>
SQL> DROP USER henry;
User dropped.

Creates a user named jason with a password of pass

SQL>
SQL> --CONNECT system/manager
SQL> CREATE USER jason IDENTIFIED BY pass;
User created.
SQL>
SQL> DROP USER jason;
User dropped.

Create user and assign password

SQL> create user user1 identified by VALUES "1A2DD3CCEE354DFA";
SQL>
SQL>
SQL> drop user user1;

Create User Utility

/*
 * CreateUser.sql
 * Chapter 3, Oracle10g PL/SQL Programming
 * by Ron Hardman, Mike McLaughlin, and Scott Urman
 *
 * This script creates the plsql user for chapter 3
 *   examples.  You must run this script as SYS or
 *   SYSTEM as SYSDBA.  The script can be rerun.
 *
 * The plsql user is created using the USERS and TEMP tablespace.
 *   If these tablespaces do not exist in your environment, change
 *   the script to use an appropriate tablespace.
 *
 * Modify the conn_string value below, providing your Net Service
 *   Name if not using your default.
 */
DEF conn_string = plsql/oracle
DEF username = plsql
DEF default_ts = USERS
DEF temp_ts = TEMP
SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
SPOOL CreateUser.log
DECLARE
   v_count       INTEGER        := 0;
   v_statement   VARCHAR2 (200);
BEGIN
   SELECT COUNT (1)
     INTO v_count
     FROM dba_users
    WHERE username = UPPER ("&username");
   IF v_count != 0
   THEN
      EXECUTE IMMEDIATE ("DROP USER &username CASCADE");
   END IF;
   v_statement :=
         "CREATE USER &username IDENTIFIED BY oracle"
      || " DEFAULT TABLESPACE &default_ts"
      || " TEMPORARY TABLESPACE &temp_ts"
      || " QUOTA UNLIMITED ON &default_ts"
      || " ACCOUNT UNLOCK";
   EXECUTE IMMEDIATE (v_statement);
   -- Grant permissions
   EXECUTE IMMEDIATE ("GRANT create session TO &username");
   EXECUTE IMMEDIATE ("GRANT connect TO &username");
   EXECUTE IMMEDIATE ("GRANT create procedure TO &username");
   EXECUTE IMMEDIATE ("GRANT execute ON utl_ref TO &username");
   EXECUTE IMMEDIATE ("GRANT create trigger TO &username");
   EXECUTE IMMEDIATE ("GRANT resource TO &username");
   DBMS_OUTPUT.put_line ("     ");
   DBMS_OUTPUT.put_line ("User &username created successfully");
   DBMS_OUTPUT.put_line ("     ");
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      DBMS_OUTPUT.put_line ("     ");
END;
/
SET FEEDBACK ON TERMOUT ON
PROMPT
PROMPT ******** Connecting to user &username!! ********
PROMPT
CONN &conn_string
CREATE OR REPLACE PACKAGE clean_schema IS
   PROCEDURE trigs;
   PROCEDURE tables;
   PROCEDURE procs;
END;
/

CREATE OR REPLACE PACKAGE BODY clean_schema AS
PROCEDURE trigs
IS
   v_string VARCHAR2(50);
   v_tab_error EXCEPTION;
   PRAGMA EXCEPTION_INIT(v_tab_error, -4080);
BEGIN
   BEGIN
   v_string := "DROP TRIGGER author_trig";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_tab_error
      THEN
      NULL;
   END;
END trigs;

PROCEDURE tables
IS
   v_string VARCHAR2(50);
   v_tab_error EXCEPTION;
   PRAGMA EXCEPTION_INIT(v_tab_error, -942);
BEGIN
   BEGIN
   v_string := "DROP TABLE precision";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_tab_error
      THEN
      NULL;
   END;
   BEGIN
   v_string := "DROP TABLE inventory";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_tab_error
      THEN
      NULL;
   END;
   BEGIN
   v_string := "DROP TABLE books";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_tab_error
      THEN
      NULL;
   END;
   BEGIN
   v_string := "DROP TABLE authors";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_tab_error
      THEN
      NULL;
   END;
END TABLES;
PROCEDURE procs
IS
   v_string VARCHAR2(50);
   v_proc_error EXCEPTION;
   PRAGMA EXCEPTION_INIT(v_proc_error, -4043);
BEGIN
   BEGIN
   v_string := "DROP PROCEDURE compile_error";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_proc_error
      THEN
      NULL;
   END;
   BEGIN
   v_string := "DROP PROCEDURE compile_warning";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_proc_error
      THEN
      NULL;
   END;
   BEGIN
   v_string := "DROP PROCEDURE named_block";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_proc_error
      THEN
      NULL;
   END;
   BEGIN
   v_string := "DROP PROCEDURE book_ins";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_proc_error
      THEN
      NULL;
   END;
   BEGIN
   v_string := "DROP PROCEDURE authors_sel";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_proc_error
      THEN
      NULL;
   END;
   BEGIN
   v_string := "DROP PROCEDURE bind_test";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_proc_error
      THEN
      NULL;
   END;
   BEGIN
   v_string := "DROP PROCEDURE author_book_count";
   EXECUTE IMMEDIATE (v_string);
   EXCEPTION
   WHEN v_proc_error
      THEN
      NULL;
   END;
END procs;
END;
/

spool off

Create user with storage quota

create user hr_audit
identified by hr_audit
/
grant create session, resource
to hr_audit
/
alter user hr_audit
identified by oracle;
alter user hr_audit
    quota unlimited on users
    quota 10M on temp
    quota 0M on system;

create user with temporary tablespace, quota and default tablespace

SQL> create user dropme
  2      identified by pass
  3      default tablespace users
  4      temporary tablespace temp
  5      quota unlimited on users;
User created.
SQL>
SQL> grant create session, create table
  2      to dropme;
Grant succeeded.
SQL>
SQL>
SQL>
SQL> --drop user dropme;
SQL> drop user dropme cascade;
User dropped.
SQL>
SQL>

Creating a User

To create a user in the database, you use the CREATE USER statement.

The simplified syntax for the CREATE USER statement is as follows:

CREATE USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE default_tablespace]
[TEMPORARY TABLESPACE temp_tablespace];

where

1. If you omit a default tablespace, the default SYSTEM tablespace is used.
2. Tablespaces are used by the database to separate objects.
3. temp_tablespace specifies the default tablespace where temporary objects are stored.
4. If you omit a temporary tablespace, the default SYSTEM tablespace is used.

Display current user

SQL>
SQL>
SQL> show user
USER is "sqle"
SQL>

User space

create user hr_audit
identified by hr_audit
/
grant create session, resource
to hr_audit
/
alter user hr_audit
identified by oracle;
alter user hr_audit
default tablespace users
temporary tablespace temp;

SQL>