Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

How To Use Windows User to Connect to the Server?

During the installation process, 10g XE will create a special Windows user group called ORA_DBA, and put your Windows user into this group. Any Windows users in this group can be connected to Oracle server with SYSDBA privilege without any Oracle server user account. This process is called connecting the server as SYSDBA with OS Authentication. Here is how to do this with a special form of the "connect" command:

(Log in with the same user you used to install 10g XE)
>cd (OracleXE home directory)
>.\bin\startdb
>.\bin\sqlplus /nolog

SQL> connect / AS SYSDBA
Connected.

SQL> quit

So if "connect" is used without user name and password, the current Windows user will be trusted if he/she is in the ORA_DBA user group on the Windows system.

How To List All User Accounts?

User accounts can be accessed through a system view called ALL_USERS. A simple SELECT statement can be used to get a list of all user accounts. Try the following script:

>.\bin\sqlplus /nolog

SQL> connect SYSTEM/fyicenter
Connected.

SQL> SELECT * FROM ALL_USERS;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
FLOWS_020100                           35 07-FEB-06
FLOWS_FILES                            34 07-FEB-06
HR                                     33 07-FEB-06
MDSYS                                  32 07-FEB-06
ANONYMOUS                              28 07-FEB-06
XDB                                    27 07-FEB-06
CTXSYS                                 25 07-FEB-06
DBSNMP                                 23 07-FEB-06
TSMSYS                                 20 07-FEB-06
DIP                                    18 07-FEB-06
OUTLN                                  11 07-FEB-06
SYSTEM                                  5 07-FEB-06
SYS                                     0 07-FEB-06

How To Create a New User Account?

If you want to create a new user account, you can log in as SYSTEM and use the CREATE USER command as shown in the following example:

>.\bin\sqlplus /nolog

SQL> connect SYSTEM/fyicenter
Connected.

SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.

Note that CREATE is a SQL statement, so you need to terminate it with ";". This command creates a user called "DEV", with a password of "developer". You can test this account by log in with DEV from SQL*Plus.

How To Change User Password?

If you want to change a user's password, you can log in as SYSTEM and use the ALTER USER command as shown in the following example:

>.\bin\sqlplus /nolog

SQL> connect SYSTEM/fyicenter
Connected.

SQL> ALTER USER DEV IDENTIFIED BY beginner;
User altered.

Note that ALTER is SQL statement, so you need to terminate it with ";". This command resets DEV's password to "beginner".

How To Delete a User Account?

If you want to delete a user account and its associated schema, you can log in as SYSTEM and use the DROP USER command as shown in the following example:

>.\bin\sqlplus /nolog

SQL> connect SYSTEM/fyicenter
Connected.

SQL> DROP USER DEV CASCADE;
User dropped.

SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.

Note that CASCADE tells the server drop the associated schema.

(Continued on next part...)

Part: 1 2 3 4 5 6

More...