how to creat user in oracle

[Former Member]

hai experts,

hai all could anybody help me to creat the user in oracle and also tell me the procedure.plz help me.

thnks,

pugazh.

[Former Member · Accepted solution]

Hi,

Following are the steps for creating an user in oracle:-

1) Connect to database where u want to create the user.

e.g:- $ sqlplus /nolog

SQL> Conn "/as sysdba"

SQL> startup

*Note :- if databse is already running then ignore step 1.*

2) After the startup of database type the following cmd on SQL prompt

SQL> create user abc identified by password xyz;

SQL> grant connect,resource,dba to abc;

3) Then Login with abc user

SQL> conn abc

Password: xyz

4) You will got message connected

connected

SQL>

Regards,

Sudhir

[former_member204746]

you should never create users in ORacle, only SAPINST should be allowed to do this.

if you are creating additional users to fetch information from it, you may be violating your SAP license.

[Former Member]

hai eric,

tell me how and from where i start or open sapinst.help me.

pugazh.

[former_member204746]

SAPINST is the program you use only once, it is used to install your SAP instance from scratch. it is not really used to create users.

So, I will comeback to original topic. Why do you want to create a user iat Oracle level if this Oracle DB is used for a SAP system?

[Former Member]

hi eric,

my company's website page not opening due to db crashes so i reinstalled oracle and after that i want to create user and also i want the backup command to take backing up oracle db .plz help me.

[former_member204746]

your company's website runs on SAP? If not, use an Oracle-only forum on the Internet and not SDN.

[Former Member]

Hi,

From that user who can create, grants rights etc or simply who is DBA use the following command.

SQL>create user xyz identified by pqr;

SQL> grant resource to abc;

Now you Grant right to user abc e.g...

SQL> grant dba to abc;

Mean...

User Name is xyz password is pqr and have can use to db and Rights are of DBA.

Regards,

Gokul Cahndola

[Former Member]

Hi gogul ,

Thanks i created the user and now could u help me to create table.plz.

[Former Member]

Hi,

As you said thatyou have created the user.

Login to the user by the below command.

conn user1/user1;

example:

create table xyz(empno number(4), empname varchar2(20), sal number(6));

press enter

and you also want to insert some data in the particular table.

insert into table xyz(&empno,empname,&sal);

press enter

like wise you can enter data in to the paritcular table

you want to see the content on the table

select * from xyz;

Regards

Iliyas

[Former Member]

Hi,

Creating Users

The create user command is fairly straightforward. It has a number of parameters, which are listed

in below along with a brief description of each one.

In the following example, we are creating a user (SKING) to correspond with the user Steven

King, employee number 100 in the HR.EMPLOYEES table from the sample schemas installed with

the database:

SQL> create user sking identified by sking901

2 account unlock

3 default tablespace users

4 temporary tablespace temp;

User created.

Parameter Usage

username

The name of the schema, and therefore the user, to be created. The username can be up to 30 characters

long and cannot be a reserved word unless it is quoted (which is not recommended).

IDENTIFIED { BY password | EXTERNALLY | GLOBALLY AS u2018extnameu2019 }

Specifies how the user will be authenticated: by the database with a password, by the operating system

(local or remote), or by a service (such as OracleInternet Directory).

DEFAULT TABLESPACE

tablespace The tablespace where permanent objects are created, unless a tablespace is explicitly specified during creation.

TEMPORARY TABLESPACE tablespace

The tablespace where temporary segments are created during sort operations, index creation, and so forth.

QUOTA { size | UNLIMITED } ON tablespace

The amount of space allowed for objects created on the specified tablespace. Size is in kilobytes (K) or

megabytes (M).

PROFILE profile

The profile assigned to this user. Profiles are discussed later in this chapter. If a profile is not specified, the DEFAULT profile is used.

PASSWORD EXPIRE

At first logon, the user must change their password.

ACCOUNT {LOCK | UNLOCK}

Specifies whether the account is locked or unlocked.

By default, the account is unlocked.

The user SKING is authenticated by the database with an initial password of SKING901. The

second line is not required; all accounts are created unlocked by default. Both the default permanent

tablespace and default temporary tablespace are defined at the database level, so the last two

lines of the command arenu2019t required unless you want a different default permanent tablespace

or a different temporary tablespace for the user.

Even though the user SKING has been either explicitly or implicitly assigned a default permanent

tablespace, he cannot create any objects in the database until we provide both a quota and the

rights to create objects in their own schema.

A quota is simply a space limit, by tablespace, for a given user. Unless a quota is explicitly

assigned or the user is granted the UNLIMITED TABLESPACE privilege (privileges are discussed

later in this chapter), the user cannot create objects in their own schema. In the following

example, weu2019re giving the SKING account a quota of 250MB in the USERS tablespace:

SQL> alter user sking quota 250M on users;

User altered.

Note that we could have granted this quota at the time the account was created, along with

almost every other option in the create user command. A default role, however, can only be

assigned after the account is created. (Role management is discussed later in this chapter.)

Unless we grant some basic privileges to a new account, the account cannot even log in;

therefore, we need to grant at least the CREATE SESSION privilege or the CONNECT role (roles

are discussed in detail later in this chapter). For Oracle Database 10g Release 1 and earlier, the

CONNECT role contains the CREATE SESSION privilege, along with other basic privileges, such

as CREATE TABLE and ALTER SESSION; as of Oracle Database 10g Release 2, the CONNECT role

only has the CREATE SESSION privilege and therefore is deprecated. In the following example,

we grant SKING the CREATE SESSION and CREATE TABLE privileges:

SQL> grant create session, create table to sking;

Grant succeeded.

Now the user SKING has a quota on the USERS tablespace as well as the privileges to create

objects in that tablespace.

All these options for create user are available in the web-based Oracle Enterprise Manager

interface, as demonstrated above

As with any Enterprise Manager operation, the Show SQL button shows the actual SQL

commands, such as create and grant, that will be run when the user is created. This is a great

way to take advantage of the web interfaceu2019s ease of use, while at the same time brushing up

on your SQL command syntax!

Kindly go through below link also

http://www.databasedesign-resource.com/users-in-oracle.html

Raj