Oracle 11g User Management

/ oracle 11g / By

In Oracle 11g, each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database. This section explains how to manage users for a database

Oracle 11g User Management

There are 4 main aspects of user management. They are

1)Managing Users

2)Managing Privileges

3)Managing Roles

4)Managing Profiles

Managing Users

user and schema, both are interchangable words.In Oracle, users and schemas are essentially the same thing. You can consider that a user is an account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

A schema is nothing but a collection of database objects like tables, views, MViews.

If we drop a schema all the objects under that particular schema will be dropped.

some of the default users that are created once we create the database are

1, SYS -> super user

2, SYSTEM -> Manager

3, DBSNMP ->  required for network functionality

4, SYSMAN -> required for enterprise manager

connecting “/ as sysdba” means connecting to a user with OS authentication.

we can also restrict  / as sysdba by setting a parameter in sqlnet.oracle

=>authentication_services=none in sqlnet.ora

Managing Privileges

Privilege is the right to execute a specific  command.

privileges are of two types

Object level privilege.

e.g., insert, update, delete on a table etc.

system level privilege

e.g., create table, create index, create tablespace etc.,

sysoper, sysdba, dba contains administrative privileges and we should not grant them to the normal users

The following are the privileges of sysoper and system users.

SYSOPER

shutdown

startup

alter database mount

alter database open

alter database backup controlfile to recover database.

alter database archivelog

SYSDBA

super privileges with admin options

create database

drop database

alter database begin backup

alter database end backup

Restricted session

Recover database until

Managing Roles

Role is nothing but collection of privileges

There are two types of roles.

Pre defined roles

e.g., connect, resource, select-catalog-role etc.,

DBA created roles

manager_role, HR_role etc.,

Managing Profiles

Profiles is nothing but collection of resource limits like idle_time, password_life_time, password_grace_time, password_reuse_time etc,

There are two types of profiles

  1. Pre define profiles which are created by pupbld.sql
  2. dba created profiles.

Also read, Activity post for the Oracle database 11g: User Management 

Words from dbapath

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.