| Creating a new user and grant dba [message #33510] |
Fri, 15 October 2004 03:57  |
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  |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
