第十章 sqlplus的安全性
一.product_user_profile表
简称PUP表,在system用户下,用于生产级的安全保护,可以管理用户级的安全命令如grant、revoke以及用户角色。
可以通过该表禁用某些命令,比如grant、revoke、set role,当普通用户访问时无法执行,system、sys以及sysdba或sysoper权限的用户登录时不会检查该表,也就不会受到权限上的影响。
PUP表只对本地用户有作用,使用dblink时不受影响。
创建PUP表
system用户下执行脚本PUPBLD:
[oracle@localhost admin]$ pwd
/u01/oracle/sqlplus/admin
[oracle@localhost admin]$ ls
glogin.sql help libsqlplus.def plustrce.sql pupbld.sql
[oracle@localhost admin]$ exit
exit
SQL> show user
USER is "SYSTEM"
SQL> @/u01/oracle/sqlplus/admin/pupbld.sql
Synonym dropped.
CREATE TABLE SQLPLUS_PRODUCT_PROFILE AS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
DROP TABLE PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-01430: column being added already exists in table
CREATE TABLE SQLPLUS_PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
DROP TABLE PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
View dropped.
View created.
Grant succeeded.
Synonym dropped.
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
Synonym dropped.
Synonym created.
SQL> 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
以下是各个字段的填充规则:
PRODUCT 要限制的产品名称,“SQL*Plus”,不能是通配符或空
USERID 要禁用部分命令的用户名,可以使用通配符,%表示所有
ATTRIBUTE 要限制的命令的大写形式,不能用通配符
SCOPE 推荐置空
NUMERIC_VALUE 推荐置空
CHAR_VALUE “DISABLED”表示禁用,若禁用角色,需要填写角色名
DATE_VALUE 推荐置空
LONG_VALUE 推荐置空
system用户拥有所有对该表的操作,其他用户只能select。
可禁用的sqlplus命令:
ACCEPT DEFINE PASSWORD SHUTDOWN
APPEND DEL PAUSE SPOOL
ARCHIVE LOG DESCRIBE PRINT START(@, @@)
ATTRIBUTE DISCONNECT PROMPT STARTUP
BREAK EDIT RECOVER STORE
BTITLE EXECUTE REMARK TIMING
CHANGE EXIT/QUIT REPFOOTER TTITLE
CLEAR GET REPHEADER UNDEFINE
COLUMN HELP (?) RUN VARIABLE
COMPUTE HOST SAVE WHENEVEROSERROR
CONNECT INPUT SET WHENEVER
SQLERROR
COPY LIST (;) SHOW
sql命令:
ALTER DELETE MERGE SET CONSTRAINTS
ANALYZE DISASSOCIATE NOAUDIT SET ROLE
ASSOCIATE DROP PURGE SET TRANSACTION
AUDIT EXPLAIN RENAME TRUNCATE
CALL FLASHBACK REVOKE UPDATE
COMMENT GRANT ROLLBACK VALIDATE
COMMIT INSERT SAVEPOINT
CREATE LOCK SELECT
pl/sql命令:
BEGIN DECLARE
例如:
禁止用户terry使用set、grant、select命令
INSERT INTO PRODUCT_USER_PROFILE
VALUES ('SQL*Plus', 'TERRY', 'GRANT', NULL, NULL, 'DISABLED', NULL, NULL);
INSERT INTO PRODUCT_USER_PROFILE
VALUES ('SQL*Plus', 'TERRY', 'SET', NULL, NULL, 'DISABLED', NULL, NULL);
INSERT INTO PRODUCT_USER_PROFILE
VALUES ('SQL*Plus', 'TERRY', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);
commit;
校验一下:
SQL> conn terry/terry
Connected.
SQL> set autot on
SP2-0544: Command "set" disabled in Product User Profile
SQL> select * from user_Tables;
SP2-0544: Command "select" disabled in Product User Profile
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> grant select on emp to scott;
SP2-0544: Command "grant" disabled in Product User Profile
根据提示可以看到,限制成功。
需要注意,此时通过pl/sql developer等其他工具是可以进行以上操作的,通过system用户下的product_user_profile只是对sqlplus环境的限制,
因为在登录前会检查是否有这些约束,而在其他环境中是没有校验的。
二、创建和控制角色
使用create命令可以创建角色,密码可以不设置。
create role role_name identified by xxx;
drop role xxx;
grant select on emp to role_name;
set role tian identified by tian;在以前没有密码的情况下可以设置密码,如果已经有密码了,不能使用
set role none;将所有角色从当前会话收回
set role all except role_name;将当前用户的所有角色的权限授予当前会话
如果在sqlplus中禁止HR用户对role1、role2两个角色的使用,尝试插入:
INSERT INTO PRODUCT_USER_PROFILE
VALUES ('SQL*Plus', 'HR', 'ROLES', NULL, NULL, 'ROLE%', NULL, NULL);
然后登陆:
SQL> conn terry/terry
ERROR:
ORA-00911: invalid character
SP2-0557: Error in disabling roles in product user profile.
Connected.
SQL>
这是因为角色中通配符是无效的。必须指定特定的角色。
三、Disabling Commands with SQLPLUS -RESTRICT
在用sqlplus命令连接到SQLPlus环境时,指定-RESTRICT n将限制与操作系统之间的交互。
主要影响的命令及对应级别为:
Command Level 1 Level 2 Level 3 备注
EDIT disabled disabled disabled edit是对文件进行编辑操作
GET disabled
HOST disabled disabled disabled 暂时切换到操作系统环境下,使用exit返回sqlplus
SAVE disabled disabled 保存内容到操作系统文件下
SPOOL disabled disabled 保存内容到操作系统文件下
START disabled 将操作系统中的文件内容作为命令执行 @、@@也受限
STORE disabled disabled 保存内容到操作系统文件下
例:
[oracle@localhost ~]$ sqlplus -restrict 1
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 26 03:03:17 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: scott
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> host
SP2-0738: Restricted command "host" not available
[oracle@localhost ~]$ sqlplus -r 2 scott/scott
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 26 03:11:00 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> spool /u01/test.sql
SP2-0738: Restricted command "spool" not available
如果将这些限制放到登录文件中,那么就可以控制用户的操作行为,实现安全性的目的。
该部分的命令相对来说都是对操作系统的保护,防止数据库用户在操作系统中乱放文件等。
四、Program Argument Security
为了避免数据库用户和密码在操作系统中被看到,以下几点可以注意:
1.登录的时候总是在sqlplus环境中输入用户名、密码
比如:
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 26 03:20:59 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn scott/scott
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ sqlplus scott/scott
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 26 03:21:27 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
通过history命令查看一下刚才都执行了什么命令:
[oracle@localhost ~]$ history 5
205 sqlplus scott/scott -R 2
206 sqlplus -r 2 scott/scott
207 history
208 sqlplus /nolog
209 sqlplus scott/scott
可以看到,我们用sqlplus /nolog命令后再输入密码,是无法通过操作系统命令看到的,而直接sqlplus scott/scott则能看到,因此推荐第一种做法
2.在unix系统中通过sqlplus脚本进行交互时,可以先设置变量MYUSERNAME、MYPASSWORD,再执行如下命令进行连接:
sqlplus /nolog <connect $MYUSERNAME/$MYPASSWORD
select ...
EOF
或
实例:
#!/bin/sh
user="system"
pass="manager"
sqlplus -S $user/$pass <SELECT * FROM tableName WHERE username=$var;
exit;
EOF
3.
操作系统中包含了用户名和密码的文件不应该被非授权的用户得到。
五、iSQL*Plus的安全
主要包括:
浏览器和应用服务器间的http协议连接
应用服务器与数据库之间的net connection
需要注意,iSQL*Plus环境中不能使用与操作系统交互的HOST、EDIT、SPOOL等命令
其他 略
以下摘抄自http://www.orafaq.com/wiki/SQL*Plus_FAQ
Here is a list of some of the most frequently used SQL*Plus commands:
ACCEPT - Get input from the user
DEFINE - Declare a variable (short: DEF)
DESCRIBE - Lists the attributes of tables and other objects (short: DESC)
EDIT - Places you in an editor so you can edit a SQL command (short: ED)
EXIT or QUIT - Disconnect from the database and terminate SQL*Plus
GET - Retrieves a SQL file and places it into the SQL buffer
HOST - Issue an operating system command (short: !)
LIST - Displays the last command executed/ command in the SQL buffer (short: L)
PROMPT - Display a text string on the screen. Eg prompt Hello World!!!
RUN - List and Run the command stored in the SQL buffer (short: /)
SAVE - Saves command in the SQL buffer to a file. Eg "save x" will create a script. file called x.sql
SET - Modify the SQL*Plus environment eg. SET PAGESIZE 23
SHOW - Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc.
SPOOL - Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst
START - Run a SQL script. file (short: @)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-753538/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-753538/