connect sys/<syspwd> as sysdba gives error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: connect sys/<syspwd> as sysdba gives error

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Angry

    Hi all,

    Oracle 8173/ NT 4.

    When I connect as

    connect sys/syspwd as sysdba;

    it gives error

    ORA-01017: Invalid username/password; logon denied

    but I am able to connect as

    connect sys/syspwd without any problems.

    Then, if I try to grant 'sysdba' role to sys as system user, it gives me error.

    ORA-01031: Insufficient privileges

    Also in V$PWFILE_USERS table, it has foll. rows.

    username sysdba sysoper
    INTERNAL TRUE TRUE
    SYS TRUE TRUE

    There is a password file also.

    Why I am not able to connect to sys as sysdba?

    Thanks in Adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Try connecting to sys and provide the internal password (The password you specified for the internal user)

    connect sys/@ as sysdba

    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Thanks ronnie,

    It worked. What's the diff betw giving sys pwd and internal pwd. Is this a solution or a work around?

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Read the MetaLink article below (note 50507.1). You'll see your situation in the article and how to go about fixing it. This is also discussed in many of the how-to-be-a-DBA books, in Oracle's documentation (you can see that on OTN), and in MetaLink notes.

    1) Administrative Users
    ~~~~~~~~~~~~~~~~~~~~~~~
    There are two main administrative privileges in Oracle: SYSOPER and SYSDBA
    These are special privileges as they allow access to a database instance
    even when it is not running and so control of these privileges is totally
    outside of the database itself.

    SYSOPER privilege allows operations such as:
    Instance startup, mount & database open ;
    Instance shutdown, dismount & database close ;
    Alter database BACKUP, ARCHIVE LOG, and RECOVER.
    This privilege allows the user to perform basic operational tasks
    without the ability to look at user data.

    SYSDBA privilege includes all SYSOPER privileges plus full system privileges
    (with the ADMIN option), plus 'CREATE DATABASE' etc..
    This is effectively the same set of privileges available when
    previously connected INTERNAL.


    2) Password or Operating System Authentication
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Password Authentication
    ~~~~~~~~~~~~~~~~~~~~~~~
    Unless a connection to the instance is considered 'secure' then you
    MUST use a password to connect with SYSDBA or SYSOPER privilege.
    Users can be added to a special 'password' file using either the
    'ORAPWD' utility, or 'GRANT SYSDBA to USER' command. Such a user can
    then connect to the instance for administrative purposes using the syntax:

    CONNECT username/password AS SYSDBA
    or
    CONNECT username/password AS SYSOPER

    This is described in more detail in section (5) below.


    Operating System Authentication
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If the connection to the instance is local or 'secure' then it is possible
    to use the operating system to determine if a user is allowed SYSDBA or
    SYSOPER access. In this case no password is required. The syntax to connect
    using operating system authentication is:

    CONNECT / AS SYSDBA
    or
    CONNECT / AS SYSOPER


    Oracle determines if you can connect thus:

    On Unix: On UNIX the Oracle executable has two group names
    compiled into it, one for SYSOPER and one for SYSDBA.
    These are known as the OSOPER and OSDBA groups.
    Typically these can be set when the Oracle software is
    installed.

    When you issue the command 'CONNECT / AS SYSOPER' Oracle
    checks if your Unix logon is a member of the 'OSOPER' group
    and if so allows you to connect. Similarly to connect as SYSDBA
    your Unix logon should be a member of the Unix 'OSDBA' group.

    The OSDBA groups is the same group as has been historically
    used to allow CONNECT INTERNAL.


    On NT: On NT the OSOPER and OSDBA groups are hard coded groups thus:

    Group Name Oracle uses this as...
    ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~
    ORA_OPER OSOPER group for all instances
    ORA_DBA OSDBA group for all instances
    or
    ORA_sid_OPER OSOPER group for a specific Oracle SID
    ORA_sid_DBA OSDBA group for a specific Oracle SID

    When you issue a 'CONNECT / AS SYSDBA' Oracle checks if your
    NT logon is a member of the 'ORA_sid_DBA' or 'ORA_DBA' group.


    3) OSDBA & OSOPER Groups on Unix
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually
    both default to the group 'dba'.

    These groups are compiled into the 'oracle' executable and so are the same
    for all databases running from a given ORACLE_HOME directory. The actual
    groups being used for OSDBA and OSOPER can be checked thus:

    cd $ORACLE_HOME/rdbms/lib
    cat config.[cs]

    The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
    The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.

    If you wish to change the OSDBA or OSOPER groups this file needs to be
    modified either directly or using the installer.

    Eg: For an OSDBA group of 'mygroup'

    If your platform has config.c (this is the case for HP-UX, Compaq Tru64
    Unixware and Linux):
    Change: #define SS_DBA_GRP "dba"

    to: #define SS_DBA_GRP "mygroup"

    In your platform has config.s:
    Due to the way different compilers under different architectures generate
    assembler code, it's not possible to give a universal rule.
    Here are some examples:
    Sun SPARC Solaris:
    ------------------
    Change both ocurrences of
    .ascii "dba\0"
    to
    .ascii "mygroup\0"

    IBM AIX/Intel Solaris:
    --------
    Change both ocurrences of
    .string "dba"
    to
    .string "mygroup"


    To effect any changes to the groups and to be sure you are using the
    groups defined in this file relink the Oracle executable. Be sure to
    shutdown all databases before relinking:

    Eg:
    rm config.o
    make -f ins_rdbms.mk config.o ioracle

    For a group to be accepted by Oracle as the OSDBA or OSOPER group it must:

    - Be compiled into the Oracle executable
    - The group name must exist in /etc/group (or in 'ypcat group' if
    NIS is being used)
    - It CANNOT be the group called 'daemon'

    Note: The commands above are examples and may vary between platforms.

    Note: Some Oracle documentation refers to the ability to define OSDBA and
    OSOPER roles using group names of the form 'ORA_sid_OSDBA'. This
    functionality has not been implemented on Unix (See [BUG:224071])


    Important notes about 'CONNECT / AS SYSDBA'
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    On Unix systems a user may be a member of more than one group .
    To connect as an administrative user without supplying a password:

    - One of the groups of which the user is a member should be either
    the OSDBA or OSOPER groups as defined in config.c (config.s on some
    platforms) and as linked into the 'oracle' executable.

    - The group must be a valid group as defined in /etc/group (Or as
    defined in NIS by 'ypcat group')

    - The users PRIMARY group (Ie: the one shown by the 'id' command)
    cannot be the special group 'daemon'.

    It is quite common for the 'root' user to be required to have SYSDBA or
    SYSOPER privilege. Unfortunately it is also common for the root users'
    primary group to be the group 'daemon' which may prevent it from being
    allowed to connect without a password. There are two ways to tackle this
    problem:

    a) Make the root users PRIMARY group the OSDBA group

    OR b) Where available use the 'newgrp' command to change the users
    primary group to the DBA group.
    Eg: $ newgrp dbagroup
    $ svrmgrl
    SVRMGRL> connect / as sysdba

    This can also be used in shellscripts thus:
    :
    newgrp dbagroup < # Commands requiring connect internal privilege
    # Eg: dbstart
    !

    OR c) For systems where 'newgrp' is not available or does not work from
    scripts you can use 'su' instead. Eg:
    :
    su - oracle < # Commands requiring administrative connect privilege
    !
    Note: The user you 'su' to should be able to 'connect / as sysdba'
    without a password, for example by having their primary
    group as the OSDBA group.

    Some Oracle releases have problems with identifying the OSDBA group when it
    is not the users primary group. If you encounter problems with connecting
    and the OSDBA group is set correctly try making the users primary
    group the OSDBA group, or use 'newgrp' as in (b) above.


    4) OSDBA & OSOPER Groups on NT
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The 'OSDBA' and 'OSOPER' groups on NT are simply groups with the
    name "ORA_DBA", "ORA_OPER", "ORA_sid_DBA" or "ORA_sid_OPER", where
    'sid' is the instance name.

    Eg: To make a user an administrative user simply:

    a) Ensure there is a line in the SQLNET.ORA file which reads:
    SQLNET.AUTHENTICATION_SERVICES = (NTS)
    b) Create a LOCAL user
    c) Create a local NT group ORA_DBA or ORA_sid_DBA where 'sid' is in
    upper case
    d) Add the user to the ORA_DBA or ORA_sid_DBA group
    e) That user should now be able to "connect / as sysdba"

    Domain prefixed usernames
    ~~~~~~~~~~~~~~~~~~~~~~~~~
    It is possible to set up usernames which include the domain
    as a prefix to the username. Eg: "OPS$\".
    To do this you need to use the registry entry OSAUTH_PREFIX_DOMAIN
    and creating users with USERNAMEs of the form "OPS$\".
    This is described in detail in [NOTE:60634.1] .


    5) Password Authentication
    ~~~~~~~~~~~~~~~~~~~~~~~~~~
    Remote connections require the database to be configured to allow
    remote DBA operations. The remote user will have to supply a password
    in order to connect as either SYSDBA or SYSOPER. The only real exception
    to this is on NT where remote connections may be secure.

    Ie: To perform a remote connect as SYSDBA or SYSOPER you must use
    the syntax 'CONNECT username/password AS SYSDBA'

    To allow remote administrative connections you must:

    - Set up a password file for the database on the server

    - Set up any relevant init.ora parameters


    5.1) Setting up a Password File
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The SYSDBA/SYSOPER password protection is controlled by an Oracle
    'Password' file. The basic concept is that a special file is created to
    hold the 'SYSDBA' and 'SYSOPER' passwords. Users with SYSDBA or SYSOPER
    privilege granted in the password file can be seen in the view
    V$PWFILE_USERS.

    To create a password file log in as the Oracle software owner and issue
    the command:

    orapwd file= password= entries=

    using the required password.
    The file name is important and should be specified as above.
    You should create this file when the database is shut down.

    To change a password:
    - Shut down the database,
    - Rename the $ORACLE_HOME/dbs/orapw$ORACLE_SID file,
    - Issue a new ORAPWD command with a new password


    5.2) Setting up the Init.Ora file
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    To enable remote administrative connections set the init.ora parameters
    thus:

    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

    EXCLUSIVE forces the password file to be tied exclusively to a single
    instance. To disable remote administrative connections set
    REMOTE_LOGIN_PASSWORDFILE=NONE.

    Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to
    connect as SYSDBA or SYSOPER from a remote machine.

    Note: Some (old) documentation may indicate SQL*Net needs configuring
    to connect from remote machines. In particular the following are
    NOT used:

    SQL*Net V2: The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED
    parameters are irrelevant



    6) Bugs and Special Notes
    ~~~~~~~~~~~~~~~~~~~~~~~~~

    Common Errors
    ~~~~~~~~~~~~~
    ORA-01031: insufficient privileges
    Connect Internal has been issued with no password.
    For local connections the user is NOT in the DBA group as compiled
    into the 'oracle' executable.
    For remote connections you must always supply a password.

    This error can also occur after a successful connect internal/password
    if there REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in
    the init.ora file.


    ORA-01017: invalid username/password; logon denied
    This is a fairly general error that indicates one of the following:
    - REMOTE_LOGIN_PASSWORDFILE is set to NONE
    - The password file does not exist
    - The password supplied does not match the one in the password file
    - The password file been changed since the instance was started


    Deleting/Changing the 'orapw$SID' File
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If you delete the Oracle password file while the instance is running
    you will NOT be able to connect internal from remote machines, even if
    you re-create the file. You must:
    - Shutdown the instance (using a local connection)
    - Create the new password file
    - You can now connect remotely and restart the instance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width