oracle aDDaa

Oracle aDDa


Sunday, 23 August 2015

User Of Oracle

Hi Friends, howz all. today  i am going to talk about users of oracle and some important terminology. let's see here.                

what is Scheema?
Scheema means users. A user in Oracle database is known as scheema an object that is created by scheemma is called scheema object. Scheema objects are like table, views, Indexes, synonyms and more.

How to connect Oracle server using client?
To connect oracle Server or database we need to open oracle client tool. lets see step by step guidence to connect oracle server.
step1 Open client(Sql*plus)
step2 Enter username
step3 Enter password
Step4 Enter DB name thet starts with @ symbol.

Note; DB name is also known as host string, Service name, SID and service ID.

we can also write all those command in a single line like this:
Username/password@DB name  ;

Note: if you installed oracle express edition and you opend sql command line tool then you need to do something like:

step-1 type conn
and then do like that above declared.

After connection with oracle server, you need to create a user of database. Actually oracle provides some predefined DBA and users that is here.

If you installed Enterprise Edition then it will provide some DBA and USERS like:
There are two DBA
  • SYS
  • System

And there are Four predefines users
  • Scott
  • SH
  • HR
  • OE

In case of Express Edition There are Two DBA and one USERS
DBAs are
  • SYS

USERs is
  • HR

Now there is a most important concepts or topics are there. 

How TO Create User Of Database?
One thig is need to know that only DBA has permission to create new user of database. database user is also known as Scheema or Account.
Syntax to create user of database....
create user <name> identified by <password>;

note: every instruction and command should be terminated with semi-colon.
lets see an example:
Sql > create user PankajDB identified by <pankaj>;
user created......

But is is only dummy user that have no privildge. it can not connect to oracle and have no permission to make transaction from database. To give privildge we need to use DCL command to provide access and connect privildge by GRANT command. let's see:

Syntax: Grant connect, resource to <uname>;
Connect is used to connect Oracle database.
Resource is used to create oblects that is priviously defined. oblect means table, indes, views and more.

Sql > GRANT connect, resource to PankajDB;
Grant succeed....

NOTE: In sql username is not case sensitive but password is case sensitive. 

How to change user password?
Once user is created then if any user wants to change their password, A DBA can change their password. user can also change their password itself. we will see both command that change user password.

Password change by DBA
sysntax: Alter user <name> identified by <password>;
Alter user pankajDB identified by Oracle123;
user altered.....
how to remove user by DBA

In this case DBA has no need to know user old password, they can directly change their password without knowing their old password.

Password changed by user...
sysntax: password;
Sql > password;
Sql > old password: oracle123;
Sql > new password: pankajDB;
Sql > re type new password: pankajDB;
password changed.....
How to change password by User

in this case user need to know thier old password then they can change thier password.

How to drop user?
There is another useful command that is drop user. This command will help to delete user form database. If A project has veen completed and their user is no longer useful then you can perform drop user command.there is another command exits that is cascade. cascade not compulsary if only on database exists, but in case of more than one daqtabase we need to add casecde command in drop user.
syntax: Drop user <name> [cascade];
Sql > drop user pankajDB cascade;
user Dropped......

these are the basics before starting darabase creation and database. My next post is based on Data Types in Oracle. It is very important if you want to learn sql. 

happy surffing, keep reading...............
thank you(Sunny kumar)


Post a Comment