Home » RDBMS Server » Security » Creating a new user and grant dba
Creating a new user and grant dba [message #33510] Fri, 15 October 2004 03:57 Go to next message
Anu
Messages: 82
Registered: May 2000
Member
I have windows 2000 Professional OS

Oracle 9i server is installed &
Oracle 8i client is installed

Now I want to create a new user and want to grant dba priviledges..

I logged in 9i SQLPlus (scott/tiger)
but I could not create a new user
I got an error message - 'insufficient privileges'
same error is comming in 8i SQLPlus also

how to create a new user Please help me ...

thanks
Re: Creating a new user and grant dba [message #33513 is a reply to message #33510] Fri, 15 October 2004 04:58 Go to previous messageGo to next message
dilip kumar
Messages: 111
Registered: December 2003
Senior Member
Hi

you have logged in to database as normal user,a normal user cannot create a other user unless he is having a DBA privileges.

logging to database as sysdba then create a user
at sql prompt do the following

1)connect sys/password@servicename as sysdba

2)create user <username> identified by <password>
default tablespace

temporary tablespace <temp tablespacename>
quota unlimited on tablespacename1

3)grant connect,resource to <newusername>

in the above method u will creating a user with pwd and assigning a default tablespace and temporary tablespace along with unlimited space on default tablespace then your granting the permission to connect and utilise the resources of database this method restrict the user to have any objects on system tablespace(note never allow any user to have a objects on system tablespace).

allocate on non system tablespace

there is another moethod but this method will intially creates the user and assign a space on system tablespace(afterwards you can change it)

syntax

Grant connect,resource to <username> identified by <password>

just type above command for second a method

Regards

D.Dilip
Re: Creating a new user and grant dba [message #33514 is a reply to message #33513] Fri, 15 October 2004 05:03 Go to previous messageGo to next message
Kumar
Messages: 115
Registered: December 1998
Senior Member
Hi

you have logged in to database as normal user,a normal user cannot create a other user unless he is having a DBA privileges.

logging to database as sysdba then create a user
at sql prompt do the following

1)connect sys/password@servicename as sysdba

2)create user identified by username identified by password
default tablespace
temporary tablespace
quota unlimited on tablespacename1

3)grant connect,resource to username

there is another moethod but this method will intially creates the user and assign a space on system tablespace(afterwards you can change it)

syntax

Grant connect,resource to identified by username identified by password

just type above command for second a method

Regards

Kumar
Re: Creating a new user and grant dba [message #34770 is a reply to message #33510] Sat, 01 January 2005 05:44 Go to previous message
ashivani
Messages: 1
Registered: January 2005
Junior Member
Log in as database administrator with dba role.

SQL> connect SYSTEM/MANAGER@<databasename>;

"databename" --- database created and initilized by oracle during installation.
After successful log in---

SQL>CREATE PROFILE profilename LIMIT
----------------------------------------------
to create profile execute and study --

SQL> SELECT DISTINCT resource_name, limit
FROM dba_profiles
ORDER BY resource_name;
------------------------------------------

SQL> CREATE USER <username>
IDENTIFIED BY <password>
DEFAULT TABLESPACE

TEMPORARY TABLESPACE
;
PROFILE <profile_name>;

SQL> GRANT CREATE SESSION TO <username>
GRANT RESOURCE TO <username>

Regards
Ashivani Kumar
Previous Topic: Grant and Revoke
Next Topic: ORA-28007: the password cannot be reused
Goto Forum:
  


Current Time: Thu Dec 02 05:14:32 CST 2021