OraFAQ Forum: Server Administration » insufficient privileges for sys

Home » RDBMS Server » Server Administration » insufficient privileges for sys

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

insufficient privileges for sys [message #565152]

Fri, 31 August 2012 01:42

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........

Report message to a moderator

Re: insufficient privileges for sys [message #565156 is a reply to message #565152]

Fri, 31 August 2012 02:04

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: insufficient privileges for sys [message #565181 is a reply to message #565156]

Fri, 31 August 2012 06:56

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.....

Report message to a moderator

Re: insufficient privileges for sys [message #565182 is a reply to message #565181]

Fri, 31 August 2012 07:06

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: insufficient privileges for sys [message #565185 is a reply to message #565182]

Fri, 31 August 2012 07:19

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.....

Report message to a moderator

Re: insufficient privileges for sys [message #565188 is a reply to message #565185]

Fri, 31 August 2012 07:35

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: insufficient privileges for sys [message #565191 is a reply to message #565188]

Fri, 31 August 2012 07:49

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.....

Report message to a moderator

Re: insufficient privileges for sys [message #565196 is a reply to message #565191]

Fri, 31 August 2012 08:21

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: insufficient privileges for sys [message #565198 is a reply to message #565152]

Fri, 31 August 2012 08:49

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......

Report message to a moderator

Re: insufficient privileges for sys [message #565199 is a reply to message #565198]

Fri, 31 August 2012 09:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>no effect.
you should show use directory content before & after

>file=pwtestdb
password file name has format as follows => orapw<SID>

Report message to a moderator

Re: insufficient privileges for sys [message #565200 is a reply to message #565199]

Fri, 31 August 2012 09:07

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.....

Report message to a moderator

Re: insufficient privileges for sys [message #565203 is a reply to message #565200]

Fri, 31 August 2012 09:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>what query should i fire to show directory content.

OS directory command => dir /n

Report message to a moderator

Re: insufficient privileges for sys [message #565204 is a reply to message #565198]

Fri, 31 August 2012 09:54

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: insufficient privileges for sys [message #565208 is a reply to message #565203]

Fri, 31 August 2012 10:21

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..........

Report message to a moderator

Re: insufficient privileges for sys [message #565213 is a reply to message #565208]

Fri, 31 August 2012 11:03

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: insufficient privileges for sys [message #565316 is a reply to message #565213]

Mon, 03 September 2012 08:50

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....

Report message to a moderator

Re: insufficient privileges for sys [message #565323 is a reply to message #565152]

Mon, 03 September 2012 09:33

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 .........

Report message to a moderator

Re: insufficient privileges for sys [message #565327 is a reply to message #565152]

Mon, 03 September 2012 10:05

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.......

Report message to a moderator

Re: insufficient privileges for sys [message #565328 is a reply to message #565327]

Mon, 03 September 2012 10:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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)

Report message to a moderator

Re: insufficient privileges for sys [message #565331 is a reply to message #565327]

Mon, 03 September 2012 11:15

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 14:33:57 CST 2024