Roles in Oracle - Managing Users

An ORACLE role is a set of privileges (or the type of access that each user needs depending on his or her status and responsibilities)

Roles :

There are 3 Standard Roles

- CONNECT

- RESOURCE and

- DBA

The CONNECT Role

This has Privileges as follows :

 CREATE SESSION, ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, and CREATE VIEW.

Note: Users do not have the ability to create tables or clusters unless you grant them a quota on a tablespace.

You can also create your own role and grant privileges to that role :

Let s see this..

SQL > Create role APPLICATION_USER;

SQL > Grant CREATE SESSION to APPLICATION_USER;   - - - Grant privileges to Role

SQL > Grant APPLICATION_USER to username;  - - - Grant Role to a user

The RESOURCE Role

The RESOURCE role has the following system privileges:

CREATE CLUSTER, CREATE INDEX, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE.

Users who have the RESOURCE role do not have the ability to create tables, indexes and clusters unless you first grant them a space quota in a tablespace.

Grant RESOURCE role to developers who will be creating PL/SQL objects such as procedures and triggers.

The DBA Role

The DBA role has all system privileges – including unlimited space quotas – and the ability to grant all privileges to other users

SYSTEM is for use by a DBA user

In ORACLE, the DBA is granted the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles, which in turn have privileges necessary for exporting and importing the full Oracle database

Note:

An individual with the DBA role can revoke CONNECT, RESOURCE, DBA, or any other privilege or role from anyone, including another DBA.

How to add password to a role ?

To enable security for a role, use the identified keyword in the alter role command.

alter role APPLICATION_USERS  identified by password123 ;

User Management - II

This is the very basic task a DBA do in his initial stage.

Creating a User

SYSTEM and SYS are the  users that are created by default , and has the the passwords MANAGER and CHANGE_ON_INSTALL.

Example:

SQL >create user username identified (by password | externally);

SQL> create user Nikhil identified by Pass123 ;

To change a password of a user, use the alter user command as a dba user:

SQL > alter user Nikhil identified by newpassword ;

Granting Limited Resources

SQL > alter user Nikhil quota 100M on USERS;

A user’s space quota may be set when the user is created , via the create user command.

If there is no limit on the user’s space quota, then you can grant that user the UNLIMITED TABLESPACE system privilege.

SQL > alter user Nikhil quota unlimited on USERS;

                         

Password Management

Passwords can expire, and accounts may be locked due to repeated failed attempts to connect. When you change your password, a password history may be maintained in order to prevent reuse of previous passwords.

The expiration characteristics of your account’s password are determined by the profile assigned to your account. Profiles, which are created by the create profile command, are managed by the dba.

• The “lifetime” of your password, which determines how frequently you must change it

• The grace period following your password’s “expiration date” during which you can change the password

• The number of consecutive failed connect attempts allowed before the account is automatically “locked”

• The number of days the account will remain locked

• The number of days that must pass before you can reuse a password

• The number of password changes that must pass before you can reuse a password

Another way to change password: use password command

SQL> password

(hit enter)

This will prompt you your old and new password.

Invisible Index

To create an invisible index: 

Use the CREATE INDEX statement with the INVISIBLE keyword.

Example:

The following statement creates an invisible index named ename_index for the ename column of the emp table:

CREATE INDEX emp_index ON emp(ename)
      TABLESPACE empdata
      STORAGE (INITIAL 20K NEXT 20k)
      INVISIBLE;

 

 An invisible index is an index that is ignored by the optimizer.

     You have to explicitly set the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES  =  TRUE at the session or system level.