1.从终端启动SQL*PLUS会话: 1)$sqlplus username/user_passwd,该登录方式暴露了密码,不够安全。
2)$sqlplus 该方式下,会提示输入用户名和密码,且密码不可见。
3)$sqlplus username@connect_identifier,连接到某一指定数据库connect_identifier
4)$sqlplus / 连接已存在的OS认证账户。
5)$sqlplus / as sysdba 将ORACLE软件的所有者作为DBA组的一部分,通过OS认证进行连接。 *sqlplus连接时后可跟AS子句,如果你有SYSDBA或SYSOPER权限,可用类似5)的方式进行登录 $sqlplus username[/passwd] as sys[dba,oper]
6)sqlplus /NOLOG 无连接的sqlplus会话,即只启动sqlplus而不连接数据库。
*在用SYSTEM用户登录SQL*PLUS时必须使用AS语句: [oracle@localhost ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 24 15:05:29 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: system Enter password: ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory Process ID: 0 Session ID: 0 Serial number: 0
Enter user-name: system as sysoper Enter password: Connected to an idle instance.
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 24 15:08:06 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: system as sysdba Enter password: Connected to an idle instance.
2.使用 connect 命令进行连接 1)登录到sqlplus后,可以通过connect命令连接不同身份的用户。如下: SYSTEM@orcl 23-MAR-11>CONNECT scott/tiger Connected. SCOTT@orcl 23-MAR-11>
2)登录到不同的数据军库: SYSTEM@orcl 23-MAR-11> CONNECT username/passwd@connect_identifier 其中:connect_identifier为你想要连接到的数据库的SID。且在连接前必须确定在tnsnames.ora文 件中有该远程数据库的连接信息。 3)可在sqlplus中使用connect命令以及/ AS SYSDBA,/ AS SYSOPER,如下: CONNECT sys/sys_passwd@connect_identifier AS SYSDBA CONNECT / AS SYS[DBA,OPER] CONNECT username/passwd AS SYS[DBA,OPER] 3.退出SQL*PLUS 输入exit(SQL*PLUS不区分大小写)即可退出SQL*PLUS会话。也可以输入QUIT退回到OS中。
4.SQL*PLUS和SQL命令 1)SQL*PLUS的安全性: 除用户名和密码外,ORACLE还提供了一个额外的安全机制,即system用户所有 product_user_profile表。使用该表,可以限制访问SQL*PLUS和SQL的命令,也可以限制访问 PL/SQL语句。 当用户登录SQL*PLUS会话时,SQL*PLUS会检查此表查看SQL*PLUS会话对此用户有何限制。默认该 表为空,其结构如下: SCOTT@orcl 23-MAR-11>DESC product_user_profile Name Null? Type ------------------------------------ -------- ---------------------- PRODUCT NOT NULL VARCHAR2(30) USERID VARCHAR2(30) ATTRIBUTE VARCHAR2(240) SCOPE VARCHAR2(240) NUMERIC_VALUE NUMBER(15,2) CHAR_VALUE VARCHAR2(240) DATE_VALUE DATE LONG_VALUE LONG
使用示例如下: 限制用户scott对select命令的使用: SYSTEM@orcl 23-MAR-11>ed Wrote file afiedt.buf
1 INSERT INTO product_user_profile 2* VALUES('SQL*PLUS','scott','SELECT',NULL,NULL,NULL,NULL,NULL) 3 /
1 row created.
如果要取消限制的话只要把相应的行删除就行了,如下: SYSTEM@orcl 23-MAR-11>ed Wrote file afiedt.buf
1* DELETE FROM product_user_profile WHERE userid='scott' SYSTEM@orcl 23-MAR-11>/
1 row deleted.
*你可以限制用户使用DML,DDL等命令,如果在操作中遇到一些错误可用system用户运行pupbld.sql(位 于$HOME_ORACLE/sqlplus/admin目录下) *但我在使用该表的时候遇到了后文“问题1”所描述的问题,一直未得到解决。期望各位高手看到了能帮帮 我。
2)通过SET ROLE命令控制安全 不同的ROLE权限不同,可以通过设置不同的ROLE来赋予相应的权限。
3)使用RESTICT命令禁用命令,它有三个限制级别,如下: Command Level 1 Level 2 Level 3 EDIT Disabled Disabled Disabled GET Disabled
SAVE Disabled Disabled
SPOOL Disabled Disabled
HOST Disabled Disabled Disabled
START Disabled
STORE Disabled Disabled
*使用方式如下: $sqlplus -RESTRICT [1,2,3]
$sqlplus -R [1,2,3]
4)用SET设置SQL*PLUS环境 设置常用的SQL*PLUS变量。下表中是一些常用的环境变量 变量 功能 用法 ARRAY[SIZE] 确定一次从数据库中获取的行数 SET ARRY 50 AUTO[COMMIT] 指定事务的提交行为为自动或手动 SET ATUO ON COLSEP 指定在列值之间要指印的文本 SET COLSEP COPY[COMMIT] 设置使用COPY命令时COMMIT的频率 SET COPY 1000 DET[INE]{&/C/ON OFF/} 设置在变量交换中使用的前缀字符 SET FEFINE ON ECHO {NO/OFF} 设置回显为ON或OFF SET ECHO ON EDITF[ILE] 设置在使用默认编辑器时的默认文件名 SET EDITF XXX.sql FEED[BACK] {OFF/ON} 指定SQL*PLUS是否显示查询返回的记录数 SEET FEEDBACK OFF FLUSH {OFF/ON} 确定输出是否缓冲或清除屏幕 SET FLUSH OFF HEA[DING] {OFF/ON} 指定是否打印标题 SET HEAD ON LIN[ESIZE] {80|n} 指定每行显示的字符数 SET LINESIZE 40 LONG {80/n} 指定LONG,CLOB,NCLOB,XMLType值的最大长度 SET LONG 8000 NEWP[AGE] {1/n/none}指定每个新页顶部的空行数 SET NEWPAGE O NUM[WIDTH] {10/n} 指定数字的显示格式 SET NUM PAGES[IZE] {24/n} 指定每页显示的行数 SET PAGESIZE 80 PAU[SE] {OFF/ON/TEXT} 指定打印到屏幕的输出量 SET PAUSE ON SERVEROUT[PUT] {OFF/ON} [SIZE n] 指定是否输出PL/SQL代码结果 SET SERVEROUTPUT ON SQLP[ROMPT] {SQL>|TEXT} 指定SQL*PLUS会话的命令提示符 SET SQLPROMPT '_USER>' TERM[OUT] {OFF/ON} 指定是否显示命令文件的输出 SET TERMOUT OFF TIMI[NG] {OFF/ON} 控制SQL命令显示时间 SET TIMING OFF VER[IFY] {OFF/ON} 指定在变量转换后是否显示SQL文本 SET VERIFY OFF
***SET只是SQL*PLUS中一个常用的命令,若要知道全部命令可在SQL*PLUS提示符下输入 help index查看: SYS@orcl 24-MAR-11>help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN @@ DEFINE PRINT SPOOL / DEL PROMPT SQLPLUS ACCEPT DESCRIBE QUIT START APPEND DISCONNECT RECOVER STARTUP ARCHIVE LOG EDIT REMARK STORE ATTRIBUTE EXECUTE REPFOOTER TIMING BREAK EXIT REPHEADER TTITLE BTITLE GET RESERVED WORDS (SQL) UNDEFINE CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE CLEAR HOST RUN WHENEVER OSERROR COLUMN INPUT SAVE WHENEVER SQLERROR COMPUTE LIST SET XQUERY CONNECT PASSWORD SHOW
5)SQL*PLUS錯誤记录 11g中有一个sperrorlog的表,它用于存放SQL或PL/SQL产生的错误,默认不启动,可用SET ERRORLOGGING ON 命令将其打开。下面是该表的结构: SYS@orcl 24-MAR-11>DESC sperrorlog Name Null? Type ----------------------------------------- -------- --------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) SCRIPT VARCHAR2(1024) IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOB
6)SQL*PLUS的命令行选项。用户在终端输入sqlplus -help就能看到全面的选项和使用信息。如下: [oracle@localhost ~]$ sqlplus -help
SQL*Plus: Release 11.2.0.1.0 Production
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements.
Usage 1: sqlplus -H | -V
-H Displays the SQL*Plus version and the usage help. -V Displays the SQL*Plus version.
Usage 2: sqlplus [ [<option>] [{logon | /nolog}] [<start>] ]
<option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]
-C <version> Sets the compatibility of affected commands to the version specified by <version>. The version has the form "x.y[.z]". For example, -C 10.2.0 -L Attempts to log on just once, instead of reprompting on error. -M "<options>" Sets automatic HTML markup of output. The options have the form: HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}] -R <level> Sets restricted mode to disable SQL*Plus commands that interact with the file system. The level can be 1, 2 or 3. The most restrictive is -R 3 which disables all user commands interacting with the file system. -S Sets silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands.
<logon> is: {<username>[/<password>][@<connect_identifier>] | / } [AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value] .................(省略)
问题1:在system用户下对表product_user_profile操作后限制无效。 SYSTEM@orcl 23-MAR-11>ed Wrote file afiedt.buf
1 INSERT INTO product_user_profile 2* VALUES('SQL*PLUS','scott','SELECT',NULL,NULL,NULL,NULL,NULL) 3 /
1 row created.
SYSTEM@orcl 23-MAR-11>commit 2 ;
Commit complete.
SYSTEM@orcl 23-MAR-11>conn scott/tiger Connected. SCOTT@orcl 23-MAR-11>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 ..............
问题2:SHOW RECYCLEBIN 命令无显示 SYSTEM@orcl 23-MAR-11>CREATE TABLE test(name VARCHAR2(20));
Table created.
SYSTEM@orcl 23-MAR-11>DROP TABLE test;
Table dropped.
SYSTEM@orcl 23-MAR-11>show recyclebin SYSTEM@orcl 23-MAR-11>commit;
Commit complete. SYSTEM@orcl 23-MAR-11>show recyclebin SYSTEM@orcl 23-MAR-11>
|