User And Their Privilege

  How to Create a User and Grant Permissions in Oracle

       Description

       The CREATE USER statement creates a database account that allows you to log into the                    Oracle database.

        Creating a User

       CREATE USER CAG

        IDENTIFIED BY xyz

         DEFAULT TABLESPACE USERS

         PROFILE DEFAULT

        ACCOUNT UNLOCK;

            NOTE --Parameters or Arguments

1             1)    user_name

The name of the database account that you wish to create

.

2               2)      TABLESPACE

Optional , It is the name of the tablespace that you wish to assign to the users.

3               3)      PROFILE profile_name

Optional. It is the name of the profile that you wish to assign to the user account to limit the amount of database resources assigned to the user account. If you omit this option, the DEFAULT profile is assigned to the user.

4               4)      PASSWORD EXPIRE

Optional. If this option is set, then the password must be reset before the user can log into the Oracle database.

           5) ACCOUNT LOCK

Optional. It disables access to the user account.

            6) ACCOUNT UNLOCK

  Optional. It enables access to the user account

    The Grant Statement

With our new CAG account created, we can now begin adding privileges to the account using the GRANT statement. GRANT is a very powerful statement with many possible options, but the core functionality is to manage the privileges of both users and roles throughout the database.

 Providing Roles

Typically, you’ll first want to assign privileges to the user through attaching the                   account to  various roles, starting with the CONNECT role:       

GRANT CONNECT TO CAG;

In some cases to create a more powerful user, you may also consider adding the RESOURCE role (allowing the user to create named types for custom schemas) or even the DBA role, which allows the user to not only create custom named types but alter and destroy them as well.

GRANT CONNECT, RESOURCE, DBA TO CAG;

Assigning Privileges

Next you’ll want to ensure the user has privileges to actually connect to the database and create a session using GRANT CREATE SESSION. We’ll also combine that with all privileges using GRANT ANY PRIVILEGES.

GRANT CREATE SESSION GRANT ANY PRIVILEGE TO CAG;

We also need to ensure our new user has disk space allocated in the system to actually create or modify tables and data, so we’ll GRANT TABLESPACE like so:

GRANT UNLIMITED TABLESPACE TO CAG;

Table Privileges

While not typically necessary in newer versions of Oracle, some older installations may require that you manually specify the access rights the new user has to a specific schema and database tables.

For example, if we want our CAG user to have the ability to perform SELECT, UPDATE, INSERT, and DELETE capabilities on the books table, we might execute the following GRANT statement:

GRANT

  SELECT,  INSERT,  UPDATE,  DELETE ON   schema.books

TO

  CAG;

This ensures that CAG can perform the four basic statements for the books table that is part of the schema schema.