Home » RDBMS Server » Server Administration » insufficient privileges for sys
insufficient privileges for sys [message #565152] Fri, 31 August 2012 01:42 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello experts,
i have a problem when i try to connect with my database by using sys
then it gives an error of insufficient privileges to sys.


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 31 12:12:47 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys@testdb as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges


Enter user-name: system@testdb
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant all privileges to sys;

Grant succeeded.

SQL> conn sys@testdb as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL>



then what should i do to access my sys user?

thanks in advance........
Re: insufficient privileges for sys [message #565156 is a reply to message #565152] Fri, 31 August 2012 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Have you a password file
2/ What is the setting of "remote_login_passwordfile" parameter.
3/ Do you use Database Vault?
4/ "grant all privileges to sys;" is useless, SYS already have all privileges

Regards
Michel
Re: insufficient privileges for sys [message #565181 is a reply to message #565156] Fri, 31 August 2012 06:56 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks to response sir,
i have run this query -
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 31 17:09:58 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: system@testdb
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE



now what should i do sir?

Quote:


3/ Do you use Database Vault?


sorry,don't know?

thanks again.....

Re: insufficient privileges for sys [message #565182 is a reply to message #565181] Fri, 31 August 2012 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't answer to the 1st question: have you a password file?
select * from v$pwfile_users;
select value from v$option where parameter='Oracle Database Vault';

Regards
Michel
Re: insufficient privileges for sys [message #565185 is a reply to message #565182] Fri, 31 August 2012 07:19 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir,
i have password file named PWDtestdb.

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 31 17:09:58 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: system@testdb
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select * from v$pwfile_users
  2  /

no rows selected


SQL> select value from v$option where parameter='Oracle Database Vault'
  2  /

VALUE
----------------------------------------------------------------
FALSE

SQL>




thanks again.....
Re: insufficient privileges for sys [message #565188 is a reply to message #565185] Fri, 31 August 2012 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your password file is not correct.
It should contain at least an entry for SYS.
Recreate it and it will be OK.

Regards
Michel
Re: insufficient privileges for sys [message #565191 is a reply to message #565188] Fri, 31 August 2012 07:49 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks for the valuable response sir,

i want to get some knowledge-

Quote:

Your password file is not correct


sir how did you get this thing?

Quote:

Recreate it and it will be OK


how to re-create passwordfile?


thanks sir.....
Re: insufficient privileges for sys [message #565196 is a reply to message #565191] Fri, 31 August 2012 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
sir how did you get this thing?

Quote:
It should contain at least an entry for SYS.


Quote:
how to re-create passwordfile?

C:\>orapwd /?
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> nosysdba=<y/n>

  where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA,
    force - whether to overwrite existing file (opt),
    nosysdba - whether to shut out the SYSDBA logon (opt for Database Vault only).
  There are no spaces around the equal-to (=) character.

The password file should be in $ORACLE_HOME/dbs in Unix and %ORACLE_HOME%\database in Windows (always post your OS and version).

Regards
Michel
Re: insufficient privileges for sys [message #565198 is a reply to message #565152] Fri, 31 August 2012 08:49 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir,
i am using oracle 11.2.1.0 on windows 7.

how to re-create passwordfile? should i run this command on cmd-

Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba
=<y/n>


when i run it then-
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users>orapwd file=pwtestdb password=test entries=1 force=yes nosysdba=n;

C:\Users>


no effect.

then how to re-create it?

thanks......
Re: insufficient privileges for sys [message #565199 is a reply to message #565198] Fri, 31 August 2012 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 22521
Registered: January 2009
Senior Member
>no effect.
you should show use directory content before & after

>file=pwtestdb
password file name has format as follows => orapw<SID>
Re: insufficient privileges for sys [message #565200 is a reply to message #565199] Fri, 31 August 2012 09:07 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir,

Quote:

you should show use directory content before & after


what query should i fire to show directory content.

thanks again.....
Re: insufficient privileges for sys [message #565203 is a reply to message #565200] Fri, 31 August 2012 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 22521
Registered: January 2009
Senior Member
>what query should i fire to show directory content.

OS directory command => dir /n
Re: insufficient privileges for sys [message #565204 is a reply to message #565198] Fri, 31 August 2012 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
On Windows, the password file is named PWD%ORACLE_SID%.ora and should be in %ORACLE_HOME%\database directory unless you changed the value in the registry.

Regards
Michel
Re: insufficient privileges for sys [message #565208 is a reply to message #565203] Fri, 31 August 2012 10:21 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
C:\Users>dir /n
 Volume in drive C has no label.
 Volume Serial Number is A681-24CE

 Directory of C:\Users

08/31/2012  07:18 PM    <DIR>          .
08/31/2012  07:18 PM    <DIR>          ..
07/27/2012  06:02 PM        98,643,968 aepuser_exp.dmp
07/27/2012  06:10 PM        98,646,016 aepuser_tbls.DMP
05/23/2012  04:55 PM    <DIR>          Contacts
07/24/2012  04:53 PM             8,192 data.dmp
08/31/2012  07:39 PM    <DIR>          Desktop
07/24/2012  12:57 PM             8,192 dmp.DMP
08/10/2012  04:51 PM    <DIR>          Documents
08/31/2012  08:02 PM    <DIR>          Downloads
07/24/2012  04:00 PM            10,240 exp.dmp
07/27/2012  06:24 PM                 0 expdp
08/09/2012  12:20 PM    <DIR>          Favorites
05/23/2012  04:55 PM    <DIR>          Links
08/21/2012  07:21 PM    <DIR>          Music
07/06/2012  02:35 PM    <DIR>          New folder
05/23/2012  05:15 PM    <DIR>          Oracle
05/23/2012  07:23 PM    <DIR>          Pictures
08/31/2012  07:18 PM             1,536 pwtestdb
07/20/2012  06:17 PM               304 row_rules_data.ctl
07/19/2012  07:43 PM               300 row_rules_data.log
05/23/2012  04:55 PM    <DIR>          Saved Games
07/24/2012  02:04 PM            12,288 scott_tbl.dmp
05/28/2012  01:42 PM    <DIR>          Searches
08/31/2012  12:12 PM            22,111 sqlnet.log
07/24/2012  03:47 PM            16,384 tbl_exp.dmp
07/24/2012  11:37 AM             7,210 test.log
07/24/2012  11:35 AM               298 text_data.log
05/23/2012  04:55 PM    <DIR>          Videos
07/25/2012  11:59 AM        89,710,592 wlm_tbl_exp.dmp
              15 File(s)    287,087,631 bytes
              15 Dir(s)  58,093,903,872 bytes free

C:\Users>orapwd file=orapwtestdb password=test entries=sysdba force=yes
 nosysdba=n;
Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba
=<y/n>

  where
    file - name of password file (required),
    password - password for SYS will be prompted if not specified at command lin
e,
    entries - maximum number of distinct DBA (optional),
    force - whether to overwrite existing file (optional),
    ignorecase - passwords are case-insensitive (optional),
    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault onl
y).

  There must be no spaces around the equal-to (=) character.

C:\Users>dir /n
 Volume in drive C has no label.
 Volume Serial Number is A681-24CE

 Directory of C:\Users

08/31/2012  07:18 PM    <DIR>          .
08/31/2012  07:18 PM    <DIR>          ..
07/27/2012  06:02 PM        98,643,968 aepuser_exp.dmp
07/27/2012  06:10 PM        98,646,016 aepuser_tbls.DMP
05/23/2012  04:55 PM    <DIR>          Contacts
07/24/2012  04:53 PM             8,192 data.dmp
08/31/2012  07:39 PM    <DIR>          Desktop
07/24/2012  12:57 PM             8,192 dmp.DMP
08/10/2012  04:51 PM    <DIR>          Documents
08/31/2012  08:50 PM    <DIR>          Downloads
07/24/2012  04:00 PM            10,240 exp.dmp
07/27/2012  06:24 PM                 0 expdp
08/09/2012  12:20 PM    <DIR>          Favorites
05/23/2012  04:55 PM    <DIR>          Links
08/21/2012  07:21 PM    <DIR>          Music
07/06/2012  02:35 PM    <DIR>          New folder
05/23/2012  05:15 PM    <DIR>          Oracle
05/23/2012  07:23 PM    <DIR>          Pictures
08/31/2012  07:18 PM             1,536 pwtestdb
07/20/2012  06:17 PM               304 row_rules_data.ctl
07/19/2012  07:43 PM               300 row_rules_data.log
05/23/2012  04:55 PM    <DIR>          Saved Games
07/24/2012  02:04 PM            12,288 scott_tbl.dmp
05/28/2012  01:42 PM    <DIR>          Searches
08/31/2012  12:12 PM            22,111 sqlnet.log
07/24/2012  03:47 PM            16,384 tbl_exp.dmp
07/24/2012  11:37 AM             7,210 test.log
07/24/2012  11:35 AM               298 text_data.log
05/23/2012  04:55 PM    <DIR>          Videos
07/25/2012  11:59 AM        89,710,592 wlm_tbl_exp.dmp
              15 File(s)    287,087,631 bytes
              15 Dir(s)  58,058,457,088 bytes free

C:\Users>



thanks..........
Re: insufficient privileges for sys [message #565213 is a reply to message #565208] Fri, 31 August 2012 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
entries - maximum number of distinct DBA (optional),

Quote:
orapwd file=orapwtestdb password=test entries=sysdba


Do you think that "sysdba" is a number?

Regards
Michel

Re: insufficient privileges for sys [message #565316 is a reply to message #565213] Mon, 03 September 2012 08:50 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
sorry to made a mistake.
now i have run this command on cmd-

query to show content before creation of pwdfile-

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Neetesh>dir /p
Volume in drive C has no label.
Volume Serial Number is A681-24CE

Directory of C:\Users\Neetesh

08/31/2012 07:18 PM <DIR> .
08/31/2012 07:18 PM <DIR> ..
07/27/2012 06:02 PM 98,643,968 aepuser_exp.dmp
07/27/2012 06:10 PM 98,646,016 aepuser_tbls.DMP
05/23/2012 04:55 PM <DIR> Contacts
07/24/2012 04:53 PM 8,192 data.dmp
09/03/2012 06:32 PM <DIR> Desktop
07/24/2012 12:57 PM 8,192 dmp.DMP
08/10/2012 04:51 PM <DIR> Documents
08/31/2012 09:37 PM <DIR> Downloads
07/24/2012 04:00 PM 10,240 exp.dmp
07/27/2012 06:24 PM 0 expdp
08/09/2012 12:20 PM <DIR> Favorites
05/23/2012 04:55 PM <DIR> Links
08/21/2012 07:21 PM <DIR> Music
07/06/2012 02:35 PM <DIR> New folder
05/23/2012 05:15 PM <DIR> Oracle
05/23/2012 07:23 PM <DIR> Pictures
Press any key to continue . . .


C:\Users\Neetesh>orapwd file=PWDTESTDB password=test ENTRIES=1

query to show content after creation of pwdfile-

C:\Users\Neetesh>dir /p
Volume in drive C has no label.
Volume Serial Number is A681-24CE

Directory of C:\Users\Neetesh

09/03/2012 06:52 PM <DIR> .
09/03/2012 06:52 PM <DIR> ..
07/27/2012 06:02 PM 98,643,968 aepuser_exp.dmp
07/27/2012 06:10 PM 98,646,016 aepuser_tbls.DMP
05/23/2012 04:55 PM <DIR> Contacts
07/24/2012 04:53 PM 8,192 data.dmp
09/03/2012 06:32 PM <DIR> Desktop
07/24/2012 12:57 PM 8,192 dmp.DMP
08/10/2012 04:51 PM <DIR> Documents
08/31/2012 09:37 PM <DIR> Downloads
07/24/2012 04:00 PM 10,240 exp.dmp
07/27/2012 06:24 PM 0 expdp
08/09/2012 12:20 PM <DIR> Favorites
05/23/2012 04:55 PM <DIR> Links
08/21/2012 07:21 PM <DIR> Music
07/06/2012 02:35 PM <DIR> New folder
05/23/2012 05:15 PM <DIR> Oracle
05/23/2012 07:23 PM <DIR> Pictures
09/03/2012 06:52 PM 1,536 PWDTESTDB
Press any key to continue . . .

and then i cut-paste this password file into database folder
of db_home where bydefault the password file resides.

and then i run this command from sqlplus-

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 3 19:18:51 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys@testdb as sysdba
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

and i also tried with that password which is given for password file-

Enter user-name: sys/test@testdb as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:





thanks....

Re: insufficient privileges for sys [message #565323 is a reply to message #565152] Mon, 03 September 2012 09:33 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir,
my problem is resolved by using this--

i gave the password for passwordfile , which i gave at database creation time
and cut-paste this file to password file folder and then i get logged-in in sys schema.


thanks to all again .........
Re: insufficient privileges for sys [message #565327 is a reply to message #565152] Mon, 03 September 2012 10:05 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
but now thee is another problem...

testdb database is now accessed as sys , but there is some more database on that machine,
when i tried to connect by sys to all these database then---


Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Neetesh>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 3 20:39:22 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys@testqa as sysdba
Enter password:
ERROR:
ORA-12518: TNS:listener could not hand off client connection


Enter user-name: sys@mydatabase as sysdba
Enter password:
ERROR:
ORA-12518: TNS:listener could not hand off client connection


Enter user-name:




thses database were easily accessible as sys, but not now (after creating pw file for testdb)

why this is happening and how to fix it?

please suggest me.

thanks.......

Re: insufficient privileges for sys [message #565328 is a reply to message #565327] Mon, 03 September 2012 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 22521
Registered: January 2009
Senior Member
[oracle@localhost ~]$ oerr ora 12518
12518, 00000, "TNS:listener could not hand off client connection"
// *Cause: The process of handing off a client connection to another process
// failed.
// *Action: Turn on listener tracing and re-execute the operation. Verify
// that the listener and database instance are properly configured for
// direct handoff.  If problem persists, call Oracle Support.
// *Comment: The problem can be worked around by configuring dispatcher(s)
// to specifically handle the desired presentation(s), and connecting
// directly to the dispatcher, bypassing the listener.


what additional clues exist from within & at the tail end of the listener log file? (use COPY & PASTE)
Re: insufficient privileges for sys [message #565331 is a reply to message #565327] Mon, 03 September 2012 11:15 Go to previous message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should read:
Database 2 Day DBA
Oracle DBA Lite

And then
Administrator's Guide
Database Concepts

You also have a Windows specific tab when you on "Home" link of each link I point you to.

Regards
Michel

Previous Topic: Ora Errors
Next Topic: ORA-01578: ORACLE data block corrupted (file # 2, %
Goto Forum:
  


Current Time: Tue Jul 29 10:15:40 CDT 2014

Total time taken to generate the page: 0.09873 seconds