Create Oracle User and Grant Permissions to User Tablespace

Notes for setting up new users/tablespaces in oracle 10g on windows 2003 server.

• Install Oracle 10g (no example/starter database, use simple install option). No services/databases are installed by default
• Configure/Install new database
• Run oracle database configuration wizard
• Create a new general purpose Database (use all default options)
• Configure/Install default listener
• Run the Oracle Net Configuration Assistant
• Add a new listener
• Select all defaults
• Create new tablespace in newly crated database
• From the oracle home start menu folder, launch the database control (loads the configuration web site in a browser, add the site to the "trusted sites" list in IE).
• Login as the SYS account using the password you set in the database configuration wizard. Connect as SYSDBA.
• Click on the "Administration" tab
• Click "Tablespaces" then the "Create" button.
• Typically, you'll want to add a tablespace specifically for a new oracle use account. I tend to make the name of the tablespace match the oracle user I intend to use with it.
• The database datafile name needs to end in .DBF. If you use a tablespace name of "Bryan", a database file name of "Bryan.DBF" would make sense.
• Create new user, assign to newly created tablespace
• Return to the database "Administration" screen and select "Users", then click "Create"
• Set the name (again, matching the tablespace is a good idea) and set the default tablespace to what we created in the previous step. Set the temp tablespace to TEMP.
• Grant new user permissions to user tablespace. Run the following command as SYS as the SYSDBA role after editing the user acount name. I find it best to use sqldeveloper to run SQL commands rather than SQLPlus, because you can connect directly to the database via the SID. That way you dont need to configure a tnsnames file for your database (if you use JDBC, you wont ever need to use it).
  
  
  GRANT create procedure, create session,create table,create type,create view,create synonym,resource TO myuser;
  
• Finally, to prove your new oracle user account works properly, use sqldeveloper again to connect to the database as your new oracle user account using role "Default". Run the following commands to make sure you have create and drop rights.
  
  
  --- create a test table
  CREATE TABLE test (
  id INT
  );
  
  --- drop the test table
  DROP TABLE test;