Oracle: Adding Users To Oracle
by Curtis SmithOverview
This document describes how to add a new user account to Oracle.
Instructions
The minimal Oracle account will need the following SQL commands run to create the account, assign a password and grant basic rights to the account:
CREATE USER user IDENTIFIED BY 'password' DEFAULT TABLESPACE USERS QUOTA 10M ON USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; GRANT CONNECT TO user; GRANT RESOURCE TO user; ALTER USER user DEFAULT ROLE CONNECT,RESOURCE;
Create a random password, using genrpass as an example. The password will need to be upper-case letters or numbers. Symbols are generally reserved or troublesome.
Additionally, the user may be granted some more permissions. Here are some typical examples:
Unlimited tablespace
Grant the user an unlimited amount of database space.
Execute the following SQL command:
GRANT UNLIMITED TABLESPACE TO user;
Access to EGAD
Grant the user access to the EGAD database. (ECN staff and student workers are generally given access).
Execute the following SQL command:
GRANT EGAD_USER TO user;
Also need to add this role to the ALTER USER command (see below).
Access to CEE
Grant the user access to the CEE database. These requests usually come from Hafiz Md Abdur Rahman (login:rahmanha).
Execute the following SQL command:
GRANT CEE_USER TO user;
Also need to add this role to the ALTER USER command (see below).
Updating Default Roles
Should one or more of the above roles is granted to the account, an updated default role setting must be invoked. Do all of the roles at one time. For example, if the additional role of EGAD_USER is granted, do the following SQL command:
ALTER USER user DEFAULT ROLE CONNECT,RESOURCE,EGAD_USER
Mailing the User
Next, mail the user the information about the newly created Oracle account. Be sure to include a message to have them change the initial password. The document Changing Passwords On Database Servers describes this process. An example message to the user might something like this:
ECN has created an account in the ECN2 Oracle database. Here is your account information:
User "STIMPSON"
Password "YZ0NIIZ2"A quick overview of the ECN database server is described the following document:
https://engineering.purdue.edu/ECN/Resources/KnowledgeBase/Docs/20050121155032
Be sure to review that document before connecting to the database. Please change your password when you log on. See the document:
https://engineering.purdue.edu/ECN/Resources/KnowledgeBase/Docs/20041206103240
for more information.
Documenting The New User
Complete the operation by documenting the new user in the EGAD database. The EGAD database has a table named DBUSER with the following fields:
| ColumnName | Description |
|---|---|
| DBUSER_ORACLE_ACCOUNT | The Oracle account name (usually upper case) |
| DBUSER_SID | The Oracle SID (always set to "ECN2") |
| DBUSER_OS_ACCOUNT | The UNIX account name (eight characters or less) |
| DBUSER_DEPARTMENT | The site requesting the account |
| DBUSER_NOTES | Any notes about the account |
| DBUSER_EXPIRATION | The expected expiration date |
Last modified: 2014/01/23 10:56:28.844924 US/Eastern by
joshua.g.davis.1
Created: 2007/10/08 09:17:30.108000 GMT-4 by brian.r.brinegar.1.