如何禁用 / as sysdba 登陆的方式

stone112692

帮顶

henrybai

修改sql.net 文件是对的，但还要修改remote_login_passwordfile       初始化参数。

tom0732

对于懂得如何修改并且对系统具有root权限的人来说,怎么设置都没用

浙江螃蟹

这说明审计的人是猪..
他们可能跟本不理解ORACLE登陆的机制,以为这样改一下就安全了,其实能不输入密码而登陆的都是ROOT或ORACLE的用户,如果别人能以这二个帐号登陆服务器,那修改ORACLE的登陆方式就是无效的.

tom0732

个人觉得oracle还是应该提供绝对禁止本机认证的方式.
毕竟有些公司SA跟DBA是分开的.

javaever

Use Database Vault

itpub888

upupup

itpub888

I have the following problem. How can I do?

-----------------------------
sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Oct xx xx:xx:xx 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> exit
-------------------

itpub888

https://metalink.oracle.com/meta ... 005559:::



Subject:  UNIX: Checklist for Resolving Connect AS SYSDBA Issues

  Doc ID:  Note:69642.1 Type:  TROUBLESHOOTING

  Last Revision Date:  11-JUN-2007 Status:  PUBLISHED


Introduction:
~~~~~~~~~~~~~
This bulletin lists the documented causes of getting

   ---> prompted for a password when trying to CONNECT as SYSDBA
   ---> errors such as ORA-01031, ORA-01034, ORA-06401, ORA-03113,ORA-09925,
                       ORA-09817, ORA-12705, ORA-12547

The dbacheck script in Note 67984.1 may help troubleshoot issues of this nature.


a) SQLNET.ORA Checks:
---------------------
1. The "sqlnet.ora" can be found in the following locations (listed by search order):
     
   $TNS_ADMIN/sqlnet.ora
   $HOME/sqlnet.ora
   $ORACLE_HOME/network/admin/sqlnet.ora
         
   Depending upon your operating system, it may also be located in:

   /var/opt/oracle/sqlnet.ora
   /etc/sqlnet.ora

   A corrupted "sqlnet.ora" file, or one with security options set, will cause
   a 'connect internal' request to prompt for a password.
   To determine if this is the problem, locate the "sqlnet.ora" that is being used.
   The one being used will be the first one found according to the search order
   listed above.
   Next, move the file so that it will not be found by this search:

   % mv sqlnet.ora sqlnet.ora_save

   Try to connect internal again.
   If it still fails, search for other "sqlnet.ora" files according to the search order listed
   above and repeat using the move command until you are sure there are no other
   "sqlnet.ora" files being used.
   If this does not resolve the issue, use the move command to put all the
   "sqlnet.ora" files back where they were before you made the change:

   % mv sqlnet.ora_save sqlnet.ora

   If moving the "sqlnet.ora" resolves the issue, then verify the contents of the file:

   a) SQLNET.AUTHENTICATION_SERVICES

      If you are not using database links, comment this line out or try setting it to:

      SQLNET.AUTHENTICATION_SERVICES = (BEQ,NONE)

      Remark: on certain platforms, this is the default behavior.

   b) SQLNET.CRYPTO_SEED

      This should not be set in a "sqlnet.ora" file on UNIX.
      If it is, comment the line out. (This setting is added to the "sqlnet.ora"
      if it is built by one of Oracle's network cofiguration products shipped with client products)

   c) AUTOMATIC_IPC

      If this is set to "ON" it can force a "TWO_TASK" connection.
      Try setting this to "OFF":
   
      AUTOMATIC_IPC = OFF


2. Set the permissions correctly in the "TNS_ADMIN" files.
   The environment variable TNS_ADMIN defines the directory where the "sqlnet.ora",
   "tnsnames.ora", and "listener.ora" files reside.
   These files must contain the correct permissions, which are set when "root.sh" runs
   during installation.
   As root, run "root.sh" or edit the permissions on the "sqlnet.ora", "tnsnames.ora",
   and "listener.ora" files by hand as follows:

   $ cd $TNS_ADMIN
   $ chmod 644 sqlnet.ora tnsnames.ora listener.ora
   $ ls -l sqlnet.ora tnsnames.ora listener.ora

   -rw-r--r--   1 oracle dba        1628 Jul 12 15:25 listener.ora
   -rw-r--r--   1 oracle dba         586 Jun  1 12:07 sqlnet.ora
   -rw-r--r--   1 oracle dba       82274 Jul 12 15:23 tnsnames.ora


b) Software and Operating System Issues:
----------------------------------------
1. Be sure $ORACLE_HOME is set to the correct directory and does not have any
   typing mistakes:

   % cd $ORACLE_HOME
   % pwd
   
   If this returns a location other than your "ORACLE_HOME" or is invalid, you
   will need to reset the value of this environment variable:

   sh or ksh:
   ----------
   $ ORACLE_HOME=<path_to_ORACLE_HOME>
   $ export ORACLE_HOME

   Example:
   $ ORACLE_HOME=/u01/app/oracle/product/7.3.3
   $ export ORACLE_HOME

   csh:
   ----
   % setenv ORACLE_HOME <path_to_ORACLE_HOME>

   Example:
   % setenv ORACLE_HOME /u01/app/oracle/product/7.3.3


   If your "ORACLE_HOME" contains a link or the instance was started with the
   "ORACLE_HOME" set to another value, the instance may try to start using the
   memory location that another instance is using.
   An example of this might be:

   You have "ORACLE_HOME" set to "/u01/app/oracle/product/7.3.3" and start the
   instance.
   Then you do something like:

   % ln -s /u01/app/oracle/product/7.3.3 /u01/app/oracle/7.3.3
   % setenv ORACLE_HOME /u01/app/oracle/7.3.3
   % svrmgrl

   SVRMGR> connect internal

   If this prompts for a password then most likely the combination of your
   "ORACLE_HOME" and "ORACLE_SID" hash to the same shared memory address of
   another running instance. Otherwise you may be able to connect internal
   but you will receive an ORA-01034 "Oracle not available" error.

   In most cases using a link as part of your "ORACLE_HOME" is fine as long as
   you are consistent.
   Oracle recommends that links not be used as part of the "ORACLE_HOME", but
   their use is supported.
           
2. Check that $ORACLE_SID is set to the correct SID, (including capitalization),
   and does not have any typos:

   % echo $ORACLE_SID                          

   Refer to Note 1048876.6 for more information.

3. Ensure $TWO_TASK is not set.
   To check if "TWO_TASK" is set, do the following:

   sh, ksh or on HP/UX only csh:
   -----------------------------
   env |grep -i two
   - or -
   echo $TWO_TASK

   csh:
   ----
   setenv |grep -i two  

  If any lines are returned such as:

  TWO_TASK=
  - or -
  TWO_TASK=PROD

  You will need to unset the environment variable "TWO_TASK":

  sh or ksh:
  ----------
  unset TWO_TASK
   
  csh:
  ----
  unsetenv TWO_TASK

  Example :
   
      $ TWO_TASK=V817
      $ export TWO_TASK
      $ sqlplus /nolog

      SQL*Plus: Release 8.1.7.0.0 - Production on Fri Dec 31 10:12:25 2004
      (c) Copyright 2000 Oracle Corporation.  All rights reserved.

      SQL> conn / as sysdba
      ERROR:
      ORA-01031: insufficient privileges

      $ unset TWO_TASK
      $ sqlplus /nolog
      SQL> conn / as sysdba
      Connected.

  If you are running Oracle release 8.0.4, and upon starting "svrmgrl" you
  receive an ORA-06401 "NETCMN: invalid driver designator" error, you should
  also unset two_task.
  The login connect string may be getting its value from the TWO_TASK
  environment variable if this is set for the user.

4. Check the permissions on the Oracle executable:   
   
   % cd $ORACLE_HOME/bin
   % ls -l oracle                 ('ls -n oracle' should work as well)

   The permissions should be rwsr-s--x, or 6751.
   If the permissions are incorrect, do the following as the "oracle"
   software owner:

   % chmod 6751 oracle
   
   If you receive an ORA-03113 "end-of-file on communication" error followed
   by a prompt for a password, then you may also need to check the ownership
   and permissions on the dump directories.
   These directories must belong to Oracle, group dba, (or the appropriates names
   for your installation).
   This error may occur while creating a database.

   Permissions should be:  755 (drwxr-xr-x)

   Also, the alert.log must not be greater than 2 Gigabytes in size.
   When you start up "nomount" an Oracle pseudo process will try to write the
   "alert.log" file in "udump".
   When Oracle cannot do this (either because of permissions or because of the
   "alert.log" being greater than 2 Gigabytes in size), it will issue the
   ORA-03113 error.

5. "osdba" group checks:

   a. Make sure the operating system user issuing the CONNECT INTERNAL belongs
      to the "osdba" group as defined in the "$ORACLE_HOME/rdbms/lib/config.s"
      or "$ORACLE_HOME/rdbms/lib/config.c". Typically this is set to "dba".
      To verify the operating system groups the user belongs to, do the following:

      % id
      uid=1030(oracle) gid=1030(dba)

      The "gid" here is "dba" so the "config.s" or "config.c" may contain an
      entry such as:

       /* 0x0008         15 */         .ascii  "dba\0"

      If these do not match, you either need to add the operating system user
      to the group as it is seen in the "config" file, or modify the "config"
      file and relink the "oracle" binary.
   
      Refer to entry Note 50507.1 section 3 for more details.

   b. A corrupted config.o also causes this to fail. Regenerate the config.o .

      Refer to entry Note 50507.1 section 3 for more details.

   c. Be sure you are not logged in as the "root" user and that the environment
      variables "USER", "USERNAME", and "LOGNAME" are not set to "root".
      The "root" user is a special case and cannot connect to Oracle as the
      "internal" user unless the effective group is changed to the "osdba" group,
      which is typically "dba".
      To do this, either modify the "/etc/password" file (not recommended) or
      use the "newgrp" command:

      # newgrp dba

      "newgrp" always opens a new shell, so you cannot issue "newgrp" from
      within a shell script.
      Keep this in mind if you plan on executing scripts as the "root" user.

   d. Check for any errors in the /etc/group and /etc/passwd file, verify that the
      "osdba" group is only listed once in the "/etc/group" file and that the users
      belonging to the dba group are properly comma separated, a proper entry looks like:

      dba::108racle,oracleas

      Potential causes for problems are multiple entries for the same group or oracle user
      in the /etc/passwd file; typically a Unix/ Linux kernel will only take the first one
      into consideration.

   e. Check that the oracle user uid and gid are matching with /etc/passwd and
      /etc/group :

      $ id
      uid=500(oracle) gid=235(dba)
   
      $ grep oracle /etc/passwd
      oracle:x:500:235racle:/home/oracle:/bin/bash
                   ^^^
      $ grep dba /etc/group
      dba:x:253racle
            ^^^
      This type of mismatch will also causes an ORA-1031 error.


6. Verify that the file system is not mounted no set uid:

   % mount
   /u07 on /dev/md/dsk/d7 nosuid/read/write

   If the filesytem is mounted "nosuid", as seen in this example, you will need
   to unmount the filesystem and mount it without the "nosuid" option.
   Consult your operating system documentation or your operating system vendor
   for instruction on modifying mount options.
   
7. Please read the following warning before you attempt to use the information
   in this step:

   ******************************************************************  
   *                                                                *
   *  WARNING: If you remove segments that belong to a running      *
   *           instance you will crash the instance, and this may   *
   *           cause database corruption.                           *
   *                                                                *
   *           Please call Oracle Support Services for assistance   *
   *           if you have any doubts about removing shared memory  *
   *           segments.                                            *
   *                                                                *
   ******************************************************************
   
   If an instance crashed or was killed off using "kill" there may be shared
   memory segments hanging around that belong to the down instance.
   If there are no other instances running on the machine you can issue:

   % ipcs -b

         T         ID       KEY        MODE    OWNER      GROUP   SEGSZ
      Shared Memory:
         m          0   0x50000ffe --rw-r--r-- root       root         68
         m       1601   0x0eedcdb8 --rw-r----- oracle      dba    4530176


   In this case the "ID" of "1601" is owned by "oracle" and if there are no
   other instances running in most cases this can safely be removed:

   % ipcrm -m 1601

   If your SGA is split into multiple segments you will have to remove all
   segments associated with the instance. If there are other instances
   running, and you are not sure which memory segments belong to the failed
   instance, you can do the following:

   a. Shut down all the instances on the machine and remove whatever shared
      memory still exists that is owned by the software owner.
   b. Reboot the machine.
   c. If your Oracle software is release 7.3.3 or newer, you can connect into
      each instance that is up and identify the shared memory owned by that
      instance:

      % svrmgrl
      SVRMGR> connect internal
      SVRMGR> oradebug ipc  
                  
      In Oracle8:
      -----------
      Area #0 `Fixed Size', containing Subareas 0-0
      Total size 000000000000b8c0, Minimum Subarea size 00000000
      Subarea  Shmid             Size      Stable Addr
            0   7205 000000000000c000         80000000   


      In Oracle7:
      -----------

       -------------- Shared memory --------------
       Seg Id       Address   Size
         2016       80000000  4308992
        Total: # of segments = 1, size = 4308992

     Note the "Shmid" for Oracle8 and "Seg Id" for Oracle7 for each running instance.
     By process of elimination find the segments that do not belong to an
     instance and remove them.
                  
8.  If you are prompted for a password and then receive error ORA-09925 "unable
    to create audit trail file" or error ORA-09817 "write to audit file failed",
    along with "SVR4 Error: 28: No space left on device", do the following:

    Check your "pfile". It is typically in the "$ORACLE_HOME/dbs" directory
    and will be named "init<your_sid>.ora, where "<your_sid>" is the value of
    "ORACLE_SID" in your environment. If the "init<your_sid>.ora" file has
    the "ifile" parameter set, you will also have to check the included file
    as well. You are looking for the parameter "audit_file_dest".

    If "audit_file_dest" is set, change to that directory; otherwise change to
    the "$ORACLE_HOME/rdbms/audit" directory, as this is the default location
    for audit files. If the directory does not exist, create it.
    Ensure that you have enough space to create the audit file.
    The audit file is generally 600 bytes in size.
    If it does exist, verify you can write to the directory:

    % touch afile

    If it could not create the called "afile", you need to change the permissions
    on your audit directory:

    % chmod 751  

9.  If connect internal prompts you for a password and then you receive an
    ORA-12705 "invalid or unknown NLS parameter value specified" error, you
    need to verify the settings for "ORA_NLS", "ORA_NLS32", "ORA_NLS33" or
    "NLS_LANG".
    You will need to consult your Installation and Configuration Guide for the
    proper settings for these environment variables.

10. If you have installed Oracle software and are trying to connect with
    Server Manager to create or start the database, and receive a TNS-12571
    "packet writer failure" error, please refer to Note 1064635.6

11. If in SVRMGRL (Server Manager line mode), you are running the "startup.sql"
    script and receive the following error:
  
    ld:so.1: oracle_home/bin/svrmgrl fatal relocation error
    symbol not found kgffiop                 

    RDBMS v7.3.2 is installed.
    RDBMS v8.0.4 is a separate "oracle_home", and you are attempting to have
    it coexist.
    This is due to the wrong version of the client shared library "libclntsh.so.1"
    being used at runtime.
    Verify environment variable settings.

    You need to ensure that "ORACLE_HOME" and "LD_LIBRARY_PATH" are set correctly.

    For C-shell, type:

    % setenv LD_LIBRARY_PATH $ORACLE_HOME/lib
    % setenv ORACLE_HOME /u01/app/oracle/product/8.0.4

    For Bourne or Korn shell, type:

    $ LD_LIBRARY_PATH=$ORACLE_HOME/lib
    $ export LD_LIBRARY_PATH
    $ ORACLE_HOME=/u01/app/oracle/product/8.0.4
    $ export ORACLE_HOME

12. Ensure that the disk the instance resides on has not reached 100% capacity.

    % df -k

    If it has reached 100% capacity, this may be the cause of 'connect internal'
    prompting for a password.
    Additional disk space will need to be made available before 'connect internal'
    will work.

    For additional information refer to Note 97849.1

13. Delete process.dat and regid.dat files in $ORACLE_HOME/otrace/admin directory.
    Oracle Trace is enabled by default on 7.3.2 and 7.3.3 (depends on platform)
    This can caused high disk space usage by these files and cause a number of
    apparently mysterious side effects.
    See Note 45482.1 for more details.

14. When you get ora-1031 "Insufficient privileges" on connect internal after you
    supply a valid password and you have multiple instances running from the same
    ORACLE_HOME, be sure that if an instance has REMOTE_LOGIN_PASSWORDFILE set to
    exclusive that the file $ORACLE_HOME/dbs/orapw<sid> does exist, otherwise it
    defaults to the use of the file orapw that consequently causes access problems
    for any other database that has the parameter set to shared.
    Set the parameter REMOTE_LOGIN_PASSWORDFILE to shared for all instances that share
    the common password file and create an exclusive orapw<sid> password files for any
    instances that have this set to exclusive.

15. Check permissions on /etc/passwd file (Unix only).
    If Oracle cannot open the password file, connect internal fails with
    ORA-1031, since Oracle is not able to verify if the user trying to connect
    is indeed in the dba group.
    Example:
    --------
    # chmod 711 /etc/passwd
    # ls -ltr passwd
    -rwx--x--x   1 root     sys          901 Sep 21 14:26 passwd
   
    $ sqlplus '/ as sysdba'

    SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 21 16:21:18 2002

    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

    ERROR:
    ORA-01031: insufficient privileges

    Trussing sqlplus will show also the problem:

    25338:  munmap(0xFF210000, 8192)                        = 0
    25338:  lwp_mutex_wakeup(0xFF3E0778)                    = 0
    25338:  lwp_mutex_lock(0xFF3E0778)                      = 0
    25338:  time()                                          = 1032582594
    25338:  open("/etc/passwd", O_RDONLY)                   Err#13 EACCES
    25338:  getrlimit(RLIMIT_NOFILE, 0xFFBE8B28)            = 0



c) Operating System Specific checks:
------------------------------------
1. On OpenVMS, check that the privileges have been granted at the Operating System
   level:
      
   $ SET DEFAULT SYS$SYSTEM:  
   $ RUN AUTHORIZE  
   
   If the list returned by AUTHORIZE does not contain ORA_<SID>_DBA, or ORA_DBA,
   then you do not have the correct OS privileges to issue a connect internal.
   If ORA_<SID>_DBA was added AFTER ORA_DBA, then ORA_DBA needs to be removed
   and granted again to be updated.
   Please refer to Note 1010852.6 for more details.

2. On Windows NT, check if DBA_AUTHORIZATION is set to BYPASS in the registry.

3. On Windows NT, if you are able to connect internally but then startup fails
   for some reason, successive connect internal attempts might prompt for a
   password. You may also receive errors such as:

   ORA-12705: invalid or unknown NLS parameter value specified
   ORA-01012: not logged on
   LCC-00161: Oracle error (possible syntax error)
   ORA-01031: insufficient privileges

   Refer to entry Note 1027964.6 for suggestions on how to resolve this problem

4. If you are using Multi-Threaded Server (MTS), make sure you are using a dedicated
   server connection.
   A dedicated server connection is required to start up or shutdown the database.
   Unless the database alias in the "TNSNAMES.ORA" file includes a parameter to make
   a dedicated server connection, it will make a shared connection to a dispatcher.
   See Note 1058680.6 for more details.

5. On Solaris, if the file "/etc/.name_service_door" has incorrect permissions,
   Oracle cannot read the file. You will receive a message that "The Oracle
   user cannot access "/etc/.name_service_door" (permission denied).
   This file is a flavor of IPC specific to Solaris which Oracle software is using
   This can also cause connect internal problems. See entry Note 1066589.6

6. You are on Digital Unix, running SVRMGRL (Server Manager line mode), and you
   receive an ORA-12547 "TNS:lost contact" error and a password prompt.

   This problem occurs when using Parallel Server and the True Cluster software together.
   If Parallel Server is not linked in, svrmgrl works as expected.

   Oracle V8.0.5 requires an Operating System patch which previous versions of
   Oracle did not require.
   The above patch allows svrmgrl to communicate with the TCR software.

   You can determine if the patch is applied by running:
   % nm /usr/ccs/lib/libssn.a | grep adjust

   If this returns nothing, then you need to:

   1. Obtain the patch for TCR 1.5 from Digital.
      This patch is for the MC SCN and adds the symbol "adjustSequenceNumber"
      to the library /usr/ccs/lib/libssn.a.
   2.  Apply the patch.
   3.  Relink Oracle

   Another possibility is that you need to raise the value of kernel parameter

   per-proc-stack-size

   when increased from its default value of 2097152 to 83886080 resolved this problem.

7. You are on version 6.2 of the Silicon Graphics UNIX (IRIX) operating system
   and you have recently installed RDBMS release 8.0.3.
   If you are logged on as "oracle/dba" and an attempt to log in to Server Manager
   using "connect/internal" prompts you for a password, you should refer to entry
   Note 1040607.6

8. On AIX 4.3.3 after applying ML5 or higher you can not longer connect as internal
   or if on 9.X '/ as sysdba' does not work as well.
   This is a known AIX bug and it occurs on all RS6000 ports including SP2.
   There is two workarounds and one solution. They are as follows:

   1) Use mkpasswd command to remove the index.
      This is valid until a new user is added to "/etc/passwd" or modified:

      # mkpasswd -v -d

   2) Touch the "/etc/passwd" file.
      If the "/etc/passwd" file is newer than the index it will not use the
      password file index:

      # touch /etc/passwd

   3) Obtain APAR IY22458 from IBM.
      Any questions about this APAR should be directed to IBM.   


d) Additional Information:
--------------------------
1. In the "Oracle7 Administrator's Reference for UNIX", there is a note that states:
         
   If REMOTE_OS_AUTHENT is set to true, users who are members of the  dba group
   on the remote machine are able to connect as INTERNAL without a password.
   However, if you are connecting remotely, that is connecting via anything
   except the bequeath adapter, you will be prompted for a password regardless
   of the value of "REMOTE_OS_AUTHENT".
   Refer to Bug 644988
  
      
References:
~~~~~~~~~~~
Note 1048876.6  UNIX: Connect internal prompts for password after install
Note 1064635.6  ORA-12571: PACKET WRITER FAILURE WHEN STARTING SVRMGR
Note 1010852.6  OPENVMS: ORA-01031: WHEN ISSUING "CONNECT INTERNAL" IN SQL*DBA OR SERVER MANAGER
Note 1027964.6  LCC-00161 AND ORA-01031 ON STARTUP
Note 1058680.6  ORA-00106 or ORA-01031 ERROR when trying to STARTUP or SHUTDOWN DATABASE
Note 1066589.6  UNIX: Connect Internal asks for password when TWO_TASK is set
Note 1040607.6  SGI: ORA-01012 ORA-01031: WHEN USING SRVMGR AFTER 8.0.3 INSTALL
Note 97849.1    Connect internal Requires Password
Note 50507.1    SYSDBA and SYSOPER Privileges in Oracle8 and Oracle7
Note 18089.1    UNIX: Connect INTERNAL / AS SYSBDA Privilege on Oracle 7/8
Bug 644988      REMOTE_OS_AUTHENT=TRUE: NOT ALLOWING USERS TO CONNECT INTERNAL WITHOUT PASSWORD


Search Words:
~~~~~~~~~~~~~
svrmgrm sqldba sqlplus sqlnet
remote_login_passwordfile

.

limeng5854

改为数据库认证就可以：
修改监听文件