Sybase数据库技术,数据库恢复

 

随着Sybase被完全整合到SAP下，Sybase原来的支持网站被SAP Support Portal取代。
只有购买了SAP服务的用户才能使用账号登录SAP Support Portal进行介质下载、补丁升级、报Incident等。
目前，原Sybase所有产品（包括：Adaptive Server Enterprise、Sybase IQ、Replication Server、PowerDesigner等）的官方手册仍然可以从https://infocenter.sybase.com/help/index.jsp进行浏览或下载。暂不清楚该网站https://infocenter.sybase.com/help/index.jsp何时会被完全迁移到SAP Support上！
Sybase官方手册英文版有html和pdf两种格式，而中文版手册只有pdf一种格式。为了国内Sybase用户更方便、快捷地搜索Sybase常见产品的官方手册内容，特将中文版Sybase官方手册转为html格式！
Sybase产品官方手册中文版的html格式所有内容的版权归SAP公司所有！本博客站长是Sybase数据库的铁杆粉丝！

如有Sybase数据库技术问题需要咨询，请联系我！

  QQ :289965371
  Email:

以下官方手册为SAP IQ 16.0 SP03中文版：

1. SAP Sybase IQ 简介
   
2. 新功能摘要
   
3. IQ 发行公告(linux)
   
4. IQ 快速入门(unix)
   
5. 安装和配置指南(linux)
   
6. 管理：备份、恢复和数据恢复
   
7. 管理：数据库
   
8. SAP Sybase IQ 错误消息
   
9. 管理：全球化
   
10. IQ 许可选项指南
    
11. 管理：装载管理
    
12. IQ 迁移
    
13. 管理：Multiplex
    
14. 性能和调优指南
    
15. IQ 编程
    
16. 参考：构件块、表和过程
    
17. 参考：语句和选项
    
18. 管理：内存行级版本控制
    
19. 管理：空间数据
    
20. IQ 非结构化数据分析
    
21. 用户定义的函数
    
22. 管理：用户管理和安全
    
23. 实用程序指南
    

 

< 上一个 | 内容 | 下一步 >

加密和解密示例 ‌

使用 AES_ENCRYPT 和 AES_DECRYPT 函数的示例，该示例是用带注释的 SQL 编写 的。

-- This example of aes_encrypt and aes_decrypt function use is presented in three parts:

--

-- Part I: Preliminary description of target tables and users as DDL

-- Part II: Example schema changes motivated by introduction of encryption

-- Part III: Use of views and stored procedures to protect encryption keys

--

-- Part I: Define target tables and users

-- Assume two classes of user, represented here by the instances

-- PrivUser and NonPrivUser, assigned to groups reflecting differing

-- privileges.

-- The initial state reflects the schema prior to the introduction

-- of encryption.

-- Set up the starting context: There are two tables with a common key.

-- Some columns contain sensitive data, the remaining columns do not.

-- The usual join column for these tables is sensitiveA.

-- There is a key and a unique index.

grant connect to PrivUser identified by 'verytrusted' ; grant connect to NonPrivUser identified by 'lesstrusted' ;

grant connect to high_privileges_group ; create role high_privileges_group ;

grant role high_privileges_group to PrivUser ;

grant connect to low_privileges_group ; create role low_privileges_group ;

grant role low_privileges_group to NonPrivUser ;

create table DBA.first_table

(sensitiveA char(16) primary key

,sensitiveB numeric(10,0)

,publicC varchar(255)

,publicD date

) ;

-- There is an implicit unique HG (HighGroup) index enforcing the

primary key.

create table second_table

(sensitiveA char(16)

,publicP integer

,publicQ tinyint

,publicR varchar(64)

) ;

create hg index second_A_HG on second_table ( sensitiveA ) ;

-- TRUSTED users can see the sensitive columns.

grant select ( sensitiveA, sensitiveB, publicC, publicD ) on DBA.first_table to PrivUser ;

grant select ( sensitiveA, publicP, publicQ, publicR ) on DBA.second_table to PrivUser ;

-- Non-TRUSTED users in existing schema need to see sensitiveA to be

-- able to do joins, even though they should not see sensitiveB.

grant select ( sensitiveA, publicC, publicD ) on DBA.first_table to NonPrivUser ;

grant select ( sensitiveA, publicP, publicQ, publicR ) on DBA.second_table to NonPrivUser ;

-- Non-TRUSTED users can execute queries such as select I.publicC, 3*II.publicQ+1

from DBA.first_table I, DBA.second_table II

where I.sensitiveA = II.sensitiveA and I.publicD IN ( '2006-01-11' ) ;

-- and

select count(*)

from DBA.first_table I, DBA.second_table II

where I.sensitiveA = II.sensitiveA and SUBSTR(I.sensitiveA,4,3) BETWEEN '345' AND '456' ;

-- But only TRUSTED users can execute the query select I.sensitiveB, 3*II.publicQ+1

from DBA.first_table I, DBA.second_table II

where I.sensitiveA = II.sensitiveA and I.publicD IN ( '2006-01-11' ) ;

-- Part II: Change the schema in preparation for encryption

--

-- The DBA introduces encryption as follows:

--

-- For applicable tables, the DBA changes the schema, adjusts access

-- permissions, and updates existing data. The encryption

-- keys used are hidden in a subsequent step.

-- DataLength comparison for length of varbinary encryption result

Numeric Precisions

-- The integer data types tinyint, small int, integer, and bigint

-- are varbinary(32) ciphertext.

-- The exact relationship is

-- DATALENGTH(ciphertext) =

-- (((DATALENGTH(plaintext)+ 15) / 16) + 1) * 16

-- For the first table, the DBA chooses to preserve both the plaintext and

-- ciphertext forms. This is not typical and should only be done if the

-- database files are also encrypted.

-- Take away NonPrivUser's access to column sensitiveA and transfer

-- access to the ciphertext version.

-- Put a unique index on the ciphertext column. The ciphertext

-- itself is indexed.

-- NonPrivUser can select the ciphertext and use it.

-- PrivUser can still select either form (without paying decrypt costs).

revoke select ( sensitiveA ) on DBA.first_table from NonPrivUser ;

alter table DBA.first_table add encryptedA varbinary(32) ; grant select ( encryptedA ) on DBA.first_table to PrivUser ;

grant select ( encryptedA ) on DBA.first_table to NonPrivUser ; create unique hg index first_A_unique on first_table

( encryptedA ) ;

update DBA.first_table

set encryptedA = aes_encrypt(sensitiveA, 'seCr3t') where encryptedA is null ;

commit

-- Now change column sensitiveB.

alter table DBA.first_table add encryptedB varbinary(32) ; grant select ( encryptedB ) on DBA.first_table to PrivUser ; create unique hg index first_B_unique on first_table

( encryptedB ) ;

update DBA.first_table

set encryptedB = aes_encrypt(sensitiveB, 'givethiskeytonoone') where encryptedB is null ;

commit

-- For the second table, the DBA chooses to keep only the ciphertext.

-- This is more typical and encrypting the database files is not required.

revoke select ( sensitiveA ) on DBA.second_table from NonPrivUser ;

revoke select ( sensitiveA ) on DBA.second_table from PrivUser ; alter table DBA.second_table add encryptedA varbinary(32) ; grant select ( encryptedA ) on DBA.second_table to PrivUser ; grant select ( encryptedA ) on DBA.second_table to NonPrivUser ; create unique hg index second_A_unique on second_table

( encryptedA ) ;

update DBA.second_table

set encryptedA = aes_encrypt(sensitiveA, 'seCr3t') where encryptedA is null ;

commit

alter table DBA.second_table drop sensitiveA ;

-- The following types of queries are permitted at this point, before

-- changes are made for key protection:

-- Non-TRUSTED users can equi-join on ciphertext; they can also select

-- the binary, but have no way to interpret it.

select I.publicC, 3*II.publicQ+1

from DBA.first_table I, DBA.second_table II

where I.encryptedA = II.encryptedA and I.publicD IN ( '2006-01-11' ) ;

-- Ciphertext-only access rules out general predicates and expressions.

-- The following query does not return meaningful results.

--

-- NOTE: These four predicates can be used on the varbinary containing

-- ciphertext:

-- = (equality)

-- <> (inequality)

-- IS NULL

-- IS NOT NULL

select count(*)

from DBA.first_table I, DBA.second_table II

where I.encryptedA = II.encryptedA and SUBSTR(I.encryptedA,4,3) BETWEEN '345' AND '456' ;

-- The TRUSTED user still has access to the plaintext columns that

-- were retained. Therefore, this user does not need to call

-- aes_decrypt and does not need the key.

select count(*)

from DBA.first_table I, DBA.second_table II

where I.encryptedA = II.encryptedA and SUBSTR(I.sensitiveA,4,3) BETWEEN '345' AND '456' ;

-- Part III: Protect the encryption keys

-- This section illustrates how to grant access to the plaintext, but

-- still protect the keys.

-- For the first table, the DBA elected to retain the plaintext columns.

-- Therefore, the following view has the same capabilities as the trusted

-- user above.

-- Assume group_member is being used for additional access control.

-- NOTE: In this example, NonPrivUser still has access to the ciphertext

-- encrypted in the base table.

create view DBA.a_first_view (sensitiveA, publicC, publicD) as

select

IF group_member('high_privileges_group',user_name()) = 1 THEN sensitiveA

ELSE NULL ENDIF,

publicC, publicD

from first_table ;

grant select on DBA.a_first_view to PrivUser ; grant select on DBA.a_first_view to NonPrivUser ;

-- For the second table, the DBA did not keep the plaintext.

-- Therefore, aes_decrypt calls must be used in the view.

-- IMPORTANT: Hide the view definition with ALTER VIEW, so that no one

-- can discover the key.

create view DBA.a_second_view

(sensitiveA,publicP,publicQ,publicR) as

select

IF group_member('high_privileges_group',user_name()) = 1 THEN aes_decrypt(encryptedA,'seCr3t', char(16)) ELSE NULL

ENDIF,

publicP, publicQ, publicR

from second_table ;

alter view DBA.a_second_view set hidden ;

grant select on DBA.a_second_view to PrivUser ; grant select on DBA.a_second_view to NonPrivUser ;

-- Likewise, the key used for loading can be protected in a stored procedure.

-- By hiding the procedure (just as the view is hidden), no-one can see

-- the keys.

create procedure load_first_proc(@inputFileName varchar(255),

@colDelim varchar(4) default '$',

@rowDelim varchar(4) default '\n')

' ||

begin

execute immediate with quotes 'load table DBA.second_table

(encryptedA encrypted(char(16),' ||

'''' || 'seCr3t' || '''' || '),publicP,publicQ,publicR)

' from ' || '''' || @inputFileName || '''' ||

' delimited by ' || '''' || @colDelim || '''' ||

' row delimited by ' || '''' || @rowDelim || '''' || ' quotes off escapes off' ;

end

;

alter procedure DBA.load_first_proc set hidden ;

-- Call the load procedure using the following syntax: call load_first_proc('/dev/null', '$', '\n') ;

-- Below is a comparison of several techniques for protecting the

-- encryption keys by using user-defined functions (UDFs), other views,

-- or both. The first and the last alternatives offer maximum performance.

-- The second_table is secured as defined earlier.

-- Alternative 1:

-- This baseline approach relies on restricting access to the entire view.

create view

DBA.second_baseline_view(sensitiveA,publicP,publicQ,publicR) as

select

IF group_member('high_privileges_group',user_name()) = 1 THEN aes_decrypt(encryptedA,'seCr3t', char(16)) ELSE NULL

ENDIF,

publicP, publicQ, publicR

from DBA.second_table ;

alter view DBA.second_baseline_view set hidden ;

grant select on DBA.second_baseline_view to NonPrivUser ; grant select on DBA.second_baseline_view to PrivUser ;

-- Alternative 2:

-- Place the encryption function invocation within a user-defined

-- function (UDF).

-- Hide the definition of the UDF. Restrict the UDF permissions.

-- Use the UDF in a view that handles the remainder of the security

-- and business logic.

-- Note: The view itself does not need to be hidden.

create function DBA.second_decrypt_function(IN datum varbinary(32))

RETURNS char(16) DETERMINISTIC BEGIN

RETURN aes_decrypt(datum,'seCr3t', char(16)); END ;

grant execute on DBA.second_decrypt_function to PrivUser ; alter function DBA.second_decrypt_function set hidden ;

create view DBA.second_decrypt_view(sensitiveA,publicP,publicQ,publicR)

as

select

IF group_member('high_privileges_group',user_name())

= 1

THEN second_decrypt_function(encryptedA) ELSE NULL

ENDIF,

publicP, publicQ, publicR

from DBA.second_table ;

grant select on DBA.second_decrypt_view to NonPrivUser ; grant select on DBA.second_decrypt_view to PrivUser ;

-- Alternative 3:

-- Sequester only the key selection in a user-defined function.

-- This function could be extended to support selection of any

-- number of keys.

-- This UDF is also hidden and has restricted execute privileges.

-- Note: Any view that uses this UDF therefore does not compromise

-- the key values.

create function DBA.second_key_function() RETURNS varchar(32) DETERMINISTIC BEGIN

return 'seCr3t' ; END

grant execute on DBA.second_key_function to PrivUser ; alter function DBA.second_key_function set hidden ;

create view DBA.second_key_view(sensitiveA,publicP,publicQ,publicR)

as

select IF

group_member('high_privileges_group',user_name()) = 1 THEN

aes_decrypt(encryptedA,second_key_function(),

char(16)) ELSE NULL

ENDIF,

publicP, publicQ, publicR

from DBA.second_table ;

grant select on DBA.second_key_view to NonPrivUser ; grant select on DBA.second_key_view to PrivUser ;

-- Alternative 4:

-- The recommended alternative is to separate the security logic

-- from the business logic by dividing the concerns into two views.

-- Only the security logic view needs to be hidden.

-- Note: The performance of this approach is similar to that of the first

-- alternative.

create view DBA.second_SecurityLogic_view(sensitiveA,publicP,publicQ,publicR)

as

select

IF group_member('high_privileges_group',user_name())

= 1

THEN aes_decrypt(encryptedA,'seCr3t', char(16)) ELSE NULL

ENDIF,

publicP,

publicQ, publicR

from DBA.second_table ;

alter view DBA.second_SecurityLogic_view set hidden ; create view

DBA.second_BusinessLogic_view(sensitiveA,publicP,publicQ,publicR) as

select

sensitiveA, publicP, publicQ, publicR

from DBA.second_SecurityLogic_view ;

grant select on DBA.second_BusinessLogic_view to NonPrivUser ; grant select on DBA.second_BusinessLogic_view to PrivUser ;

-- End of encryption example

另请参见

• AES_ENCRYPT 函数 [String] （第 173 页）

• AES_DECRYPT 函数 [String] （第 175 页）

• LOAD TABLE ENCRYPTED 子句 （第 176 页）

< 上一个 | 内容 | 下一步 >