A user in Oracle is the same as a schema. The word synonymous should not be used perhaps in order to avoid confusion with synonyms. A schema by definition contains a schematic or table structure definition for an application or applications. A user is by definition a schema or a user which can connect to a schema user, allowing access to application table structures in an Oracle database.

Types of Users

In any database environment there are different types of users, with some variation depending on the installation type and culture of the company owning the Oracle database.

Database administrators.
The SYS and SYSTEM users contain metadata and direct administrator database access privileges. Any users, in addition to SYS and SYSTEM can be granted the SYSOPER and SYSDBA privileges, where access permission is stored in a password file external to the database. The password file allows access to the database from the operating system when the database is shut down.
Security administrators.
Network administrators.
Systems administrators.
Application developers.
Testing users.
Power users.
End users.

Database, security, network and systems administrators have full access or a specific version of full access to an Oracle database, depending on their specific functions, and once again company culture. Application developers are generally allowed to create tables and other database objects, within specific schemas, exluding access to Oracle system level maintenance functionality. Quite often developers and designers are responsible for design and creation of entity relationship designs and thus table creation. Testing users have less access than developers without the need to create objects in the database such as tables and indexes. Power users are generally allowed functionality such as creation and execution of ad-hoc SQL, sometimes through the use of views. Power users might be allowed direct user logon capability into the database using a reporting tool such as SQL*Plus. End users are usually allowed only indirect access to the database through application embedded SQL or PL/SQL calls, allowing retrieval of and changes to data in the database. End users would not be allowed direct access using a tool such as SQL*Plus.

Creating, Changing and Dropping Users

The CREATE USER Command

CREATE USER user
	IDENTIFIED
	{
		  BY password
		| EXTERNALLY
		| GLOBALLY AS 'external name'
	}
	[ DEFAULT TABLESPACE tablespace ]
	[ TEMPORARY TABLESPACE tablespace ]
	[ QUOTA { n [ K | M ] | UNLIMITED } ON tablespace ]
	[ PROFILE profile ]
	[ PASSWORD EXPIRE ]
	[ ACCOUNT [UN]LOCK ];

The following example script creates the BOOKS user as an application development user of the Books schema.

spool createUser.log;
drop user books cascade;
create user books identified by books
default tablespace data
temporary tablespace temp
quota unlimited on data
quota unlimited on INDX
quota unlimited on objects
quota unlimited on temp;
grant connect,resource to books;
grant unlimited tablespace to books;
grant query rewrite to books;
spool off;

The ALTER USER Command

ALTER USER user IDENTIFIED
	{
		  BY password [ REPLACE password ]
		| EXTERNALLY
		| GLOBALLY AS 'external name‘
	}
	[ DEFAULT TABLESPACE tablespace ]
	[ TEMPORARY TABLESPACE tablespace ]
	[ QUOTA { n [ K | M ] | UNLIMITED } ON tablespace ]
	[ DEFAULT ROLE { role [, role … ] | NONE | ALL [ EXCEPT role [, role ] ] } ]
	[ PROFILE profile ]
	[ PASSWORD EXPIRE ]
	[ ACCOUNT [UN]LOCK ];

The DROP USER Command

DROP USER user [ CASCADE ];

Controlling Security

Privileges

Privileges can be set at the system level, individual object level and assigned to both objects and the contents of objects, such as columns in tables.

System Level Privileges

Object Level Privileges

Grouping Privileges Using Roles

Roles already assigned to users can be enabled or disabled using the SET ROLE command. The ALL option enables everything, EXCEPT removes specified roles, NONE disables everything and individual roles can be assigned using a password.

The SET ROLE Command Syntax

SET ROLE
{
	  ALL [ EXCEPT role [, role ... ] ]
	| NONE
	| role [ IDENTIFIED BY password [, IDENTIFIED BY password ... ] ]
};

Granting and Revoking Privileges

Privileges are granted to and revoked from users using the GRANT and REVOKE commands.

GRANT and REVOKE Command Syntax

GRANT { system privilege | role [, system privilege | role ... ] | ALL PRIVILEGES }
	TO user | role | PUBLIC [, user | role | PUBLIC ... ]
	[ IDENTIFIED BY password ] [ WITH ADMIN OPTION ];

GRANT { { object privilege | ALL [ PRIVILEGES ] } [ ( column [, column ... ] ) ] } [, ... ] }
	{ ON schema.object
		| DIRECTORY directory | JAVA [RE]SOURCE [schema.]object }
	TO user | role | PUBLIC [, user | role | PUBLIC ... ]
	[ WITH GRANT OPTION ] [ WITH HIERARCHY OPTION ];

REVOKE { system privilege | role [, system privilege | role ... ] | ALL PRIVILEGES }
	FROM user | role | PUBLIC [, user | role | PUBLIC ... ];

REVOKE { { object privilege | ALL [ PRIVILEGES ] } [ ( column [, column ... ] ) ] } [, ... ] }
	{ ON schema.object
		| DIRECTORY directory | JAVA [RE]SOURCE [schema.]object }
	FROM user | role | PUBLIC [, user | role | PUBLIC ... ]
		[ CASCADE CONSTRAINTS ] [ FORCE ];

*Security for Oracle10g

10 Oracle Database Security
·	Apply the principal of least privilege 
·	Manage default user accounts  
·	Implement standard password security features 
·	Audit database activity 
·	Register for security updates