Thursday, July 19, 2007

Oracle for Beginner: Creating User Account

In Oracle, we can create an user account easily. Here are some examples that will show you how to create a new user account and grant some privileges to that user (as dba user):

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create user xxx identified by password_xxx;
SQL> grant connect to xxx ;
SQL> quit

(now, you can try to login as user xxx)
$ sqlplus xxx/password_xxx
(done user xxx login in! but you can't do anything except querying some dictionary view that already grant to you, like v$version, user_tables, user_indexes, etc.)
SQL> select * from v$version;
SQL> select table_name from user_tables;
(you also can change your password)
SQL> alter user xxx identified by oracle;
(but you can't do DDL or DML operation)
SQL> create table test(a number);
(error!)
SQL> exit

We will do a simple practice to make our user xxx can do DDL or DML operation on tablespace "operations" with space limitation . To do this, you should login again as DBA:

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create tablespace operations datafile size 5M;
SQL> alter user xxx default tablespace operations quota 2M on operations password expire;
(we want to change default tablespace from "users" to "operations" and make a space limitation for user xxx only for 2M. Option "password expire" is an option to force user xxx to change the password. Try to login as user xxx again)
SQL> grant create table, create any index, create sequence to xxx;
(as a dba, we grant some privileges for user xxx, so he/she can DDL and DML toward their own objects)
SQL> conn xxx/oracle
ERROR:
ORA-28001: the password has expired

Changing password for xxx
New password:
Retype new password:
Password changed
Connected.

SQL> create sequence seq_test;
SQL> create table test(id number(4) primary key, data varchar(40));
SQL> insert into test values(seq_test.next, 'test one');
SQL> commit;
SQL> select * from test;
SQL> drop table test purge;
SQL> exit

In unix/linux environment, if you want unix/linux user account can login to oracle which is the instance oracle is also running on the same machine, you can try with these a simple practice:

$ su -
password:
# adduser testing
# passwd testing
# exit

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> show parameter os_authent_prefix;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$

SQL> create user ops$testing identified by externally;
SQL> grant connect, resource to ops$testing;
SQL> quit
$ su - testing
password:
$ sqlplus /
SQL> show user;
ops$testing
(now you can login to your oracle with operating system user account)

6 comments:

dghnfgj said...

You said you will love me wow gold the whole life, but WoW Gold you marry her. You said you will wow power leveling,come to marry me, but this will not be carried out forever.WoW Gold I am trying my best to forget you and do not love you anymore. wow leveling But I failed and I still love you. Maybe wow leveling she needs you more compared wow leveling with me. So I tell you that world of warcraft power leveling you should love world of warcraft power leveling her and take good world of warcraft leveling care of her. You said I was so kind.world of warcraft leveling Yes, because I love you,world of warcraft leveling I hope you will be power leveling happy forever.

rduht said...

we provide a power leveling and free wow gold wow power leveling

milf said...

find air beds in wisconsinbest air beds in wisconsincloud air beds

best cloud inflatable air bedssealy air beds portableportables air bedsrv luggage racksaluminum made rv luggage racksair bed raisedbest form raised air bedsbed air informercialsbest informercials bed airmattress sized air beds

bestair bed mattress antique doorknobsantique doorknob identification tipsdvd player troubleshootingtroubleshooting with the dvd playerflat panel television lcd vs plasmaflat panel lcd television versus plasma pic the bestadjustable bed air foam The best bed air foam

hoof prints antique equestrian printsantique hoof prints equestrian printsBuy air bedadjustablebuy the best adjustable air bedsair beds canadian storesCanadian stores for air beds

migraine causemigraine treatments floridaflorida headache clinicdrying dessicantair drying dessicant

milf said...

black mold exposureblack mold symptoms of exposurewrought iron garden gatesiron garden gates find them herefine thin hair hairstylessearch hair styles for fine thin hairnight vision binocularsbuy night vision binocularslipitor reactionslipitor allergic reactionsluxury beach resort in the philippines

afordable beach resorts in the philippineshomeopathy for eczema.baby eczema.save big with great mineral makeup bargainsmineral makeup wholesalersprodam iphone Apple prodam iphone prahacect iphone manualmanual for P 168 iphonefero 52 binocularsnight vision Fero 52 binocularsThe best night vision binoculars here

night vision binoculars bargainsfree photo albums computer programsfree software to make photo albumsfree tax formsprintable tax forms for free craftmatic air bedcraftmatic air bed adjustable info hereboyd air bedboyd night air bed lowest price

milf said...

new houston house houston house txstains removal dyestains removal clothesstains removalteeth whiteningteeth whiteningbright teeth

jennifer grey nosejennifer nose jobscalebrities nose jobsWomen with Big NosesWomen hairstylesBig Nose Women, hairstyles

milf said...

dessicant air dryerpediatric asthmaasthma specialistasthma children specialistcarpet cleaning dallas txcarpet cleaners dallascarpet cleaning dallas

vero beach vacationvero beach vacationsbeach vacation homes veroms beach vacationsms beach vacationms beach condosmaui beach vacationmaui beach vacationsmaui beach clubbeach vacationsyour beach vacationscheap beach vacations

bob hairstylebob haircutsbob layeredpob hairstylebobbedclassic bobCare for Curly HairTips for Curly Haircurly hair12r 22.5 best pricetires truck bustires 12r 22.5

washington new housenew house houstonnew house san antonionew house ventura