oracle用户权限疑问

soccer

想问一下，建了数据库用户后(create user user_name identified by "new_password";，赋予什么权限才可以用此用户登陆sqlplus ?

还有就是什么权限的用户才可以运行conn /as sysdba ？

谢谢

dancingpig

给新用户connect 角色即可连接，如果要创建object给他resource角色
sysdba权限才可以 conn / as sysdba

soccer

谢谢

比方说我用sqlplus /nolog 或者sqlplus system/password 登陆sqlplus

都可以运行conn /as sysdba。什么情况下运行conn /as sysdba会提示“权限不够”。

我的意思就是想让某些用户没有权限运行conn /as sysdba

dancingpig

sqlplus / as sysdba意思是使用操作系统的用户认证，操作系统认证通过了就把这当前登陆的用户作为sysdba用户登陆
要禁止的话，没做过也没想过
参考下这个看下是不是有你需要的

Operating System Authentication
By default, Oracle only allows operating system authenticated logins over secure connections. Therefore, if you want the operating system to authenticate a user, by default that user cannot connect to the database over Oracle Net. This means the user cannot connect using a shared server configuration, since this connection uses Oracle Net. This default restriction prevents a remote user from impersonating another operating system user over a network connection.

If you are not concerned about remote users impersonating another operating system user over a network connection, and you want to use operating system user authentication with network clients, set the initialization parameter REMOTE_OS_AUTHENT (default is FALSE) to TRUE in the database's initialization parameter file. Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE allows the RDBMS to accept the client operating system user name received over a nonsecure connection and use it for account access. The change take effect the next time you start the instance and mount the database.

Generally, user authentication through the host operating system offers the following benefits:

Users can connect to Oracle faster and more conveniently without specifying a separate database user name or password.
User entries in the database and operating system audit trails correspond.

soccer

谢谢

在远程winxp下可以sqlplus "system/password@safedb as sysdba"登陆sqlplus.
前提是: grant sysdba to system.

但是现在的情况是，我在server端(redhat)，想通过新建一个系统(redhat)用户test，然后登陆sqlplus，我把oracle目录下.bash_profile文件中的一些内容复制到新建用户test下的.bash_profile里，可以通过sqlplus /nolog登陆，但是conn /as sysdba就不行了，报错：

SQL> conn /as sysdba
ERROR:
ORA-09925: Unable to create audit trail file
Linux Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux Error: 13: Permission denied
Additional information: 9925

请高人帮忙

dancingpig

sqlplus /nolog 不是登陆

soccer

是可以通过运行sqlplus /nolog 进入sqlplus,但是不能运行conn /as sysdba连接至服务器。

谢谢

dancingpig

ubject:         OERR: ORA-9925 "Unable to create audit trail
          Doc ID:         Note:21073.1         Type:         REFERENCE
          Last Revision Date:         08-NOV-1999         Status:         PUBLISHED


Error:  ORA-9925
Text:   Unable to create audit trail file
-------------------------------------------------------------------------------
Cause:  ORACLE was not able to create the file being used to hold
        audit trail records.
Action: Check the UNIX error number for a possible operating system error.
        If there is no error, contact ORACLE customer support.

*** Important: The notes below are for experienced users - See Note 22080.1

Explanation:
        This error should only be seen on Unix systems.

        Oracle tries to create an audit file (in releases 7.1.3 onwards)
        even if auditing is not enabled. (C2 audit requirement)
        By default this information is written to $ORACLE_HOME/rdbms/audit.


Diagnosis:
        On UNIX:
        1) Ensure the directory $ORACLE_HOME/rdbms/audit or the directory
           specified by the init.ora <parameter:AUDIT_FILE_DEST> exists, is
           writable by the oracle software owner and that the disk is not full.

        2) Check $ORACLE_HOME/bin/oracle has permission of 6755.  

           If not, then correct the permissions by executing this from the  
           $ORACLE_HOME/bin directory:
                  chmod 6755 oracle

dancingpig

刚才搞错了
我看了下单位的solaris
我mk了个新用户，然后设置了oracle_home就可以conn / as sysdba
我建议你先把test用户重新建立下，然后设置下oracle_home和path然后sqlplus在试下

soccer

嘿嘿，可以了

运行了chmod -fR 6755 就可以了，其他什么都不用改的。

还有弱弱的问1句，上面的6是指什么？

谢谢dancingpig