本文共 8306 字,大约阅读时间需要 27 分钟。
一.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]$ lsglogin.sql help libsqlplus.def plustrce.sql pupbld.sql[oracle@localhost admin]$ exitexitSQL> 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 SHUTDOWNAPPEND DEL PAUSE SPOOLARCHIVE LOG DESCRIBE PRINT START(@, @@)ATTRIBUTE DISCONNECT PROMPT STARTUPBREAK EDIT RECOVER STOREBTITLE EXECUTE REMARK TIMINGCHANGE EXIT/QUIT REPFOOTER TTITLECLEAR GET REPHEADER UNDEFINECOLUMN HELP (?) RUN VARIABLECOMPUTE HOST SAVE WHENEVEROSERRORCONNECT INPUT SET WHENEVERSQLERRORCOPY LIST (;) SHOWsql命令:
ALTER DELETE MERGE SET CONSTRAINTSANALYZE DISASSOCIATE NOAUDIT SET ROLEASSOCIATE DROP PURGE SET TRANSACTIONAUDIT EXPLAIN RENAME TRUNCATECALL FLASHBACK REVOKE UPDATECOMMENT GRANT ROLLBACK VALIDATECOMMIT INSERT SAVEPOINT CREATE LOCK SELECTpl/sql命令:
BEGIN DECLARE例如:
禁止用户terry使用set、grant、select命令INSERT INTO PRODUCT_USER_PROFILEVALUES ('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/terryConnected.SQL> set autot onSP2-0544: Command "set" disabled in Product User ProfileSQL> select * from user_Tables;SP2-0544: Command "select" disabled in Product User ProfileSQL> 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_PROFILEVALUES ('SQL*Plus', 'HR', 'ROLES', NULL, NULL, 'ROLE%', NULL, NULL);然后登陆:SQL> conn terry/terryERROR: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返回sqlplusSAVE 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 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> spool /u01/test.sql
SP2-0738: Restricted command "spool" not available如果将这些限制放到登录文件中,那么就可以控制用户的操作行为,实现安全性的目的。
该部分的命令相对来说都是对操作系统的保护,防止数据库用户在操作系统中乱放文件等。四、Program Argument Security
为了避免数据库用户和密码在操作系统中被看到,以下几点可以注意:1.登录的时候总是在sqlplus环境中输入用户名、密码
比如:[oracle@localhost ~]$ sqlplus /nologSQL*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> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@localhost ~]$ sqlplus scott/scottSQL*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 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith 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/$MYPASSWORDselect ...EOF或实例:#!/bin/shuser="system"pass="manager"sqlplus -S $user/$pass <SELECT * FROM tableName WHERE username=$var; exit;EOF3.
操作系统中包含了用户名和密码的文件不应该被非授权的用户得到。
五、iSQL*Plus的安全主要包括:浏览器和应用服务器间的http协议连接应用服务器与数据库之间的net connection需要注意,iSQL*Plus环境中不能使用与操作系统交互的HOST、EDIT、SPOOL等命令
其他 略
以下摘抄自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/