梦想有多远

 查看数据库系统版本

COL PRODUCT FORMAT A35

COL VERSION FORMAT A15

COL STATUS FORMAT A15 

SQL> run;
  1* select * from product_component_version

PRODUCT                                           VERSION            STATUS
———————————– ————— —————
NLSRTL                                              11.1.0.6.0            Production
Oracle Database 11g Enterprise Edit 11.1.0.6.0         Productionion

PL/SQL                                                  11.1.0.6.0           Production
TNS for 32-bit Windows:                      11.1.0.6.0          Production

Database Administrator Usernames

Two user accounts are automatically created when Oracle Database is installed:

• SYS (default password: CHANGE_ON_INSTALL)
• SYSTEM (default password: MANAGER)
• 不要使用以上用户建表视图什么的,一般需要建立一个具有DBA角色的用户.
• 两种比较重要的系统权利:sysdba和sysopera

• SYSDBA

  Perform STARTUP and SHUTDOWN operations ALTER DATABASE: open, mount, back up, or change character set CREATE DATABASE DROP DATABASE CREATE SPFILE ALTER DATABASE ARCHIVELOG ALTER DATABASE RECOVER Includes the RESTRICTED SESSION privilege Effectively, this system privilege allows a user to connect as user SYS.

很多时候有人问为什么我授予一个人DBA权限了,为什么不能以sysdba登陆呢?其实SYSDBA是一个权限,需要授予才可以,

grant sysdba to 用户

还有从上边可以看处拥有sysdba的用户可以创建数据库,拥有sysoper的用户一般用来做数据库维护.

CONNECT oe/oe CREATE TABLE admin_test(name VARCHAR2(20));

Later, user oe issues these statements:

CONNECT oe/oe AS SYSDBA SELECT * FROM admin_test;

User oe now receives the following error:

ORA-00942: table or view does not exist

当普通登陆时,对象存储在BOSON的SCHEMA中,当以SYSDBA登陆时,对象存储在SYS用户的SCHEMA中..

ORACLE两种登陆认证;

Nonsecure Remote Connections

To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database usernames that have been granted the SYSDBA or SYSOPER system privilege. This form of authentication is discussed in “Using Password File Authentication”.

Local Connections and Secure Remote Connections

You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:

• If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file.(被授予sysdba或sysoper的用户可以通过密码文件认证登陆)
• 密码文件可以通过如下命令创建:
• ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users
• If the server is not using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users.(可以通过系统认证登陆,无须密码,只要你在的系统用户属于ORACLE DBA组或OPER组)

 需要注意的问题,如果你通过alter user更改了sys密码?那么会同时更新数据字典里记录和密码文件里的记录,前提是remote_login_passwordfile  必须被设置为EXCLUSIVE,不能为shared 如果为none也是不能远程登陆的

In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are:

• NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.
• EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
• SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of a Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

  This option is useful if you are administering multiple databases or a RAC database.

• 可以通过查看V$PWFILE_USERS 视图查看哪些用户具有sysdba权限
• SQL> select * from v$pwfile_users;
• USERNAME                       SYSDB SYSOP SYSAS
  —————————— —– —– —–
  SYS                            TRUE  TRUE  FALSE
  BOSON                          TRUE  FALSE FALSE
• 有关密码文件的一些ORA错误;
• ORA-1996 是用户密码文件用户以满,必须删除密码文件重新建立,把entries设置大点
• ORA-01999: 口令文件不能在 SHARED 模式下更新,remote_login_passwordfile在shared模式下无法修改密码文件.
•  

This entry was posted on 星期一, 十一月 26th, 2007 at 16:35 and is filed under oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.