Creating users
Use the following syntax to employ the CREATE USER statement:

CREATE USER 'username', 'password' ;

Eg: CREATE USER 'GPS', 'star';

 

A user's password can be changed easily by using the ALTER USER statement:

ALTER USER 'username', 'old_password', 'new_password';

 

ALTER USER 'GPS', 'star', 'star1';

Granting privileges

Privilege basics
There are two types of privileges

1. those granted on databases
2. those granted on tables, views, and procedures.

 

Privileges for databases:

•  Granting or restricting system administration privileges (DBA).
• Granting or restricting general creation privileges on a database (RESOURCE).

 

Privileges granted on tables, views, and procedures grant or restrict operations on specific operations, such as:

• Altering an object definition.
• Deleting, inserting, selecting and updating records.
• Executing stored procedures.
• Granting privileges.
• Defining constraints to an existing table.

 

GRANT statement
The GRANT statement can be used to provide the user with two different types of privileges:

The GRANT statement syntax for granting RESOURCE or DBA privileges is:

GRANT {RESOURCE, DBA } TO username [, username ], ... ;

The following statement provides resource privileges to user 'GSP'.

GRANT RESOURCE TO 'GSP';

Table-specific privileges can be granted to users so they can view, add, delete, or create indexes for data within a table. Privileges can also be granted to allow users to refer to a table from another table's constraint definitions.
The GRANT statement syntax for granting table-specific privileges is:
GRANT {privilege [, privilege], ... |ALL}
ON table_name
TO {username [, username], ... | PUBLIC}
[ WITH GRANT OPTION ] ;