Oracle 11g User Management
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
- Pre define profiles which are created by pupbld.sql
- 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.