User Management on Oracle Database

User management is a mechanism for managing user on database. In Oracle Database, user management is used to perform management of user objects in order to perform transactions on the database objects that exist within the Oracle Database. The management can be in the form of additional users, deletion of users or changes in user attributes. The user attributes in Oracle Database include user name, user password and user permissions. By default on Oracle Database XE 11g has a user name attribute that is not case sensitive while the password attribute is case sensitive. Case sensitive means the uppercase (capital letter) is distinguished by a lowercase letter on the same word, as USER1 is different from user1.

The user permissions on Oracle Database are known as user privileges. User privileges can be provided individually or by grouping first. The grouping of user privileges in Oracle Database is known as user roles. The user roles may consist of one or more privilege users, while 1 user privilege can be assigned to more than one user role. A user can have one or more user roles whose grants can be combined with user privileges. Here are some examples of user privileges available in Oracle Database:

DBA

CONNECT

RESOURCE

SELECT ANY TABLE

INSERT ANY TABLE

UPDATE ANY TABLE

DELETE ANY TABLE

Privileges are given to the user by using the GRANT command. The granting of privileges or roles can only be granted by users who have CREATE USER privileges. Users who already have CREATE USER privileges are SYS or SYSTEM users. The GRANT command is followed by the name of the privilege to be given and the user name to be assigned a privilege. GRANT format is GRANT privilege_name TO user _name;. Here's an example of a command to grant privileges connect to a user with my username

GRANT connect TO userku;

User privileges may be revoked or withdrawn by using the REVOKE command. The REVOKE writing format is REVOKE privilege_name FROM user_name ;. Here is an example of revocation of the connect privilege of the user with my username

REVOKE connect FROM userku;

A new user must be able to login as in the existing Oracle Database, the user can then perform transactions on the objects that exist in the Oracle database. At a minimum a user has 2 user privileges to perform operations on an Oracle database. The minimum user privileges assigned to a user are the privilege connect and privilege resource. Privilege connect is given so that user can login to Oracle Database while privilege resource is given so that user can do management of objects owned by that user.

Here are the steps that need to be done to perform user management in creating a user with a learning name that has connect and resource privileges:

1. Open a command prompt (C: \>), can be done by typing cmd in the Start - Run menu.

2. Login by using SYSTEM user by typing sqlplus command SYSTEM / <your password>

3. Create a user with learning name and password learning001 by typing the command

CREATE USER study IDENTIFIED BY belajar001;

if successful will appear User created message.

4. Give the privileges required for the user to learn, the minimum a user has 2 privileges CONNECT and RESOURCE. Privilege CONNECT is granted for the user to log into the Oracle Database. Privilege RESOURCE is provided for user to manage (DDL-DML) against tables owned by the user. Here's the command to grant the user access rights to learn:

GRANT CONNECT, RESOURCE TO exercise;

if successful will appear Grant message succeeded.