请教下面脚本的功能（详解）

sucretiger

set echo off
set termout on
set heading on
set feedback off
set trimspool on
set linesize 200
set pagesize 200
set markup html on spool on

Spool TestDBDump.html

prompt DBA_PROFILES
prompt Control: Account Lockout
prompt Control: Idle Session Timeout
prompt Control: Password Composition
prompt Control: Password Expiration
prompt Control: Password History
prompt Control: Restrict Password Protected Roles
prompt Control: Concurrent Logins
prompt Control: Test Password Settings
prompt
prompt Display dba_profiles table
select        * from        dba_profiles;


prompt DBA_ROLE_PRIVS
prompt Control: Application Schema owner as DBA
prompt Control: Developer Roles
prompt Control: Developer Access to Production Environment
prompt Control: Restrict Role Functionality
prompt Control: Restrict WITH ADMIN Option
prompt Control: Role-based Privileges: Auditing
prompt Control: Role-based Privileges: Security Administration
prompt Control: Role-based Privileges: Process
prompt Control: Role-based Privileges: DBA
prompt Control: Role-based Privileges: Data Owner
prompt Control: CONNECT and RESOURCE role
prompt Control: PUBLIC Account Privileges
prompt Control: Restrict Password Protected Roles
prompt Control: Test Access to Privileged IT Functions
prompt Control: Test Access to Production Data
prompt Control: Test Logical Access Segregation of Duties
prompt
prompt Display dba_role_privs table
select        *
from        dba_role_privs
order by grantee, granted_role;


prompt Control: Test for Access Assigned to PUBLIC Role
prompt Control: Test Access to Privileged IT Functions  
prompt Control: Test Logical Access Segregation of Duties
prompt
prompt Display DBA_SYS_PRIVS table
prompt Fields: Grantee, Privilege, Admin_Option
select        substr(grantee,1,30) "Grantee",       
        substr(privilege,1,20) "Privilege",
        substr(admin_option,1,3) "Admin_Option"
from         dba_sys_privs;


prompt Control: Test for Access Assigned to PUBLIC Role
prompt Control: Test Access to Production Data
prompt
prompt Display dba_tab_privs table
prompt Fields: Grantee, Owner, Table_Name, Grantor, Privilege, Grantable, Hierarchy
Select  substr(grantee,1,30) "Grantee",
substr(owner,1,20) "Owner",
substr(table_name,1,20) "Table_Name",
substr(grantor,1,20) "Grantor",
substr(privilege,1,20) "Privilege",
substr(grantable,1,3) "Grantable",
substr(hierarchy,1,3) "Hierarchy"
from dba_tab_privs;

prompt DBA_TAB_PRIVS_3
prompt Control: Audit Table Permissions
prompt
prompt Display select fields from dba_tab_privs table
prompt where table_name contains 'AUD$'
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME = '%AUD$%';


prompt Control: Test Monitoring of User Access
prompt
prompt Display dba_tab_privs table
prompt Fields: Grantee, Owner, Table_Name, Privilege
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME LIKE 'AUD%';

prompt Control: Test Access to Production Data
prompt
prompt Display dba_tab_privs table
prompt Fields: Grantee, Owner, Table_Name, Grantor, Privilege, Grantable, Hierarchy
Select  substr(grantee,1,30) "Grantee",
substr(owner,1,20) "Owner",
substr(table_name,1,20) "Table_Name",
substr(grantor,1,20) "Grantor",
substr(privilege,1,20) "Privilege",
substr(grantable,1,3) "Grantable",
substr(hierarchy,1,3) "Hierarchy"
from dba_tab_privs WHERE GRANTABLE = 'YES';

prompt Control: Test Access to Privileged IT Functions
prompt Control: Test Access to Production Data
prompt Control: Test Default Accounts and Passwords
prompt Control: Test for Host-Based Authentication Methods
prompt Control: Test for Global and Enterprise Roles
prompt Control: Test Logical Access Segregation of Duties
prompt Control: Test New User Setup
prompt Control: Test Password Settings
prompt
prompt Display DBA_USERS table
prompt Fields: Username, User ID, Password, Account Status, Lock Date, Expiry date, Default tablespace, Created, Assigned Profile, Consumer Group, External Name
select        substr(username,1,20) "Username",       
        substr(user_id,1,10) "User ID",
        substr(password,1,20) "Password",
        substr(account_status,1,20) "Account Status",
        substr(Lock_date,1,10) "Lock date",
        substr(Expiry_date,1,10) "Expiry date",
        substr(Default_tablespace,1,15) "Def Tablespace",
        substr(Created,1,10) "Created",
        substr(Profile,1,10) "Assgn Profile",
        substr(Initial_RSRC_Consumer_Group,1,20) "Consumer Grp",
        substr(External_Name,1,10) "Ext Name"
from         sys.dba_users
order by username;

prompt Control: Test Access to Data Modification Utilities (SQL*Plus)  
prompt
prompt Display Product_Profile
SELECT * FROM PRODUCT_PROFILE;

prompt Control: Test Access to Privileged IT Functions
prompt
prompt Display SYS.DBA_ROLE_PRIVS table
prompt Fields: Grantee, Granted_Role, Admin_Option, Default_Role
select        substr(grantee,1,30) "Grantee",       
        substr(granted_role,1,20) "granted_role",
        substr(admin_option,1,3) "Admin_Option",
                     substr(default_role,1,3) "Default_Role"
from         SYS.DBA_ROLE_PRIVS WHERE ADMIN_OPTION = 'YES';

prompt Control: Test Password Settings
prompt
prompt Display SYS.DBA_STMT_AUDIT_OPTS  table
prompt Fields: Grantee, Privilege, Admin_Option
SELECT USER_NAME, FAILURE FROM SYS.DBA_STMT_AUDIT_OPTS WHERE AUDIT_OPTION = 'CREATE SESSION';

prompt Control: Test for Host-Based Authentication Methods
prompt Control: Test Monitoring of User Access
prompt Control: Test Password Settings
prompt
prompt Display v$parameter2 table
prompt Fields: Num, Name, Type, Value, Isdefault, Isses_Modifiable, Issys_Modifiable, Ismodified, Isadjusted, Description, Ordinal, Update_Comment
Select  substr(num,1,20) "Num",
        substr(Name,1,20) "Name",
        substr(type,1,20) "Type",
        substr(value,1,20) "Value",
        substr(isdefault,1,20) "Isdefault",
        substr(isses_modifiable,1,20) "Isses_modifiable",
        substr(issys_modifiable,1,20) "Issys_modifiable",
        substr(ismodified,1,20) "Ismodified",
        substr(isadjusted,1,20) "Isadjusted",
        substr(description,1,20) "Description",
        substr(ordinal,1,20) "Ordinal",
        substr(update_comment,1,20) "Update_Comment"
from         v$parameter2;

prompt Control: Test Monitoring of User Access
prompt
prompt Display Product_Component_Version
prompt Fields: Product, Version, Status
SELECT * FROM PRODUCT_COMPONENT_VERSION;

Prompt Control: Test Monitoring of User Access
prompt
prompt Display V$VERSION
prompt Fields: Banner
SELECT * FROM V$VERSION;

prompt end of script
spool off
set markup html off

sqmax

看着好像是生成数据库一些配置信息的报告

blue_stone

主要是做审计用的吧？
查用户， 查权限， 查审计表的访问者，查版本等等

doni

原帖由 blue_stone 于 2009-12-1 21:03 发表
主要是做审计用的吧？
查用户， 查权限， 查审计表的访问者，查版本等等

应该是的

我这里每年审计都要来一个星期，真得是非常晕

sqmax

这样的报告能审计处啥啊？

doni

原帖由 sqmax 于 2009-12-1 21:38 发表
这样的报告能审计处啥啊？

审计数据库是否安全

现在的审计不仅仅是只针对财务

无牙

应该是审计

bigmoyo

set echo off
set termout on
set heading on
set feedback off
set trimspool on
set linesize 200
set pagesize 200  ----以上为调整 SQLPLUS 的输出格式

set markup html on spool on  ----html 元素标签开关 打开
Spool TestDBDump.html    ----此处开始输出到TestDBDump.html文件

prompt DBA_PROFILES
prompt Control: Account Lockout
prompt Control: Idle Session Timeout
prompt Control: Password Composition
prompt Control: Password Expiration
prompt Control: Password History
prompt Control: Restrict Password Protected Roles
prompt Control: Concurrent Logins
prompt Control: Test Password Settings
prompt
prompt Display dba_profiles table       ----若干Prompt语句定义html中的表格为以下内容定下格式，以下略

select        * from        dba_profiles;    ----选出的内容将会被打印至文件中，并且配合上述格式会添上正确的html元素，以下略

prompt DBA_ROLE_PRIVS
prompt Control: Application Schema owner as DBA
prompt Control: Developer Roles
prompt Control: Developer Access to Production Environment
prompt Control: Restrict Role Functionality
prompt Control: Restrict WITH ADMIN Option
prompt Control: Role-based Privileges: Auditing
prompt Control: Role-based Privileges: Security Administration
prompt Control: Role-based Privileges: Process
prompt Control: Role-based Privileges: DBA
prompt Control: Role-based Privileges: Data Owner
prompt Control: CONNECT and RESOURCE role
prompt Control: PUBLIC Account Privileges
prompt Control: Restrict Password Protected Roles
prompt Control: Test Access to Privileged IT Functions
prompt Control: Test Access to Production Data
prompt Control: Test Logical Access Segregation of Duties
prompt
prompt Display dba_role_privs table

select        *
from        dba_role_privs
order by grantee, granted_role;

………………

Prompt Control: Test Monitoring of User Access
prompt
prompt Display V$VERSION
prompt Fields: Banner

SELECT * FROM V$VERSION;

prompt end of script
spool off                  ----打印文件结束
set markup html off ----html 元素标签开关 关闭

——综上所述，该脚本是oracle数据库自身用于安全等审计的脚本，或许在使用WEB－EM时将会隐式地用到，显式地展现在浏览器中。
——oracle的审计，一般我们关心得比较少。但建议面向大型应用的安装后，将审计表空间调大一次（原始大小的3-5倍吧），以免短期内就出现该表空间撑满。

——导出成为HTML脚本这一功能，早在上世纪90年代末8.0时代便已经初具雏形，不过用者聊聊（至今怕也不多，毕竟都主要用于数据存储与管理，展现可能是用j2EE等），那时最常见的是将一个表直接显示成表格。在今天恐怕最大的用户也是oracle自己吧，WEB版的EM出现后，DBA的很多文本性质的脚本基本就可以不必使用了。

——具体每个表的内容属于何种审计信息，建议一一查询，这样记忆深刻。当然，这样说的主原因是因为我也记不完全。

renxiao2003

LZ从哪里贴出来的脚本 ！

XQKA

运行一下就知道了。