Oracle User Administration

Published 04 January 2009

Users

To create a new user:

create user <username> 
identified by <password>;

This is the most basic statement for creating a user. There are also options for designating tablespaces, etc. [1] To see the users currently setup:

select *
from dba_users;

To remove a user [2]:

drop user <username>;

This will only work if the user's schema does not contain any
objects. If it does contain objects, they must all be dropped in order
for the user to be dropped. This is accomplished by adding the cascade
keyword:

drop user <username> cascade;

Roles and Privileges

Once a user has been created, they must be granted privileges in
order to connect to the database and do anything. Privileges can be
granted in several ways. Each required system privilege can be granted
to the user individually. For example:

grant create session to <username>;

Or the privileges can be granted to a role and then the role can be
granted to the user. This is a quicker way to give users all the

privileges they need. For instance, many times the seeded roles connect
and resource are granted to a user:

grant connect, resource to <username>;

This is a quick way to get a new user up and going on the database.
As these roles can contain numerous privileges, this will not be
appropriate for all users. Another quick way to give a user privileges
is:

grant ALL PRIVILEGES to <username>;

This would also not be appropriate in most circumstances.

To see all the possible system privileges:

select *
from system_privilege_map;

To check system privilege assignments:

select *
from dba_sys_privs;

To see all the roles:

select *
from dba_roles;

To see the roles assigned:

select * 
from dba_role_privs;
where grantee = '<user or role name>';

Profiles

To see the profiles and their settings:

select *
from dba_profiles;

Documentation

Oracle