bcp transfers encrypted data in and out of databases in either plaintext or ciphertext form. By default, bcp copies plaintext data. bcp processes plaintext data files as follows:
Data is automatically encrypted by Adaptive Server before insertion when executing bcp in. Slow bcp is used. The user must have insert and select permission on all columns.
Data is automatically decrypted by Adaptive Server when executing bcp out. select permission is required on all columns; in addition, decrypt permission is required on the encrypted columns.
This example copies the “customer” table out as plaintext data in native machine format:
bcp uksales.dbo.customer out uk_customers -n -Uroy -Proy123
Use the -C option for bcp to copy the data as ciphertext. When copying ciphertext, you may copy data across different operating systems. If you are copying character data as ciphertext, both platforms must support the same character set.
The -C option for bcp allows administrators to run bcp when they lack decrypt permission on the data. When the -C option is used, bcp processes data as follows:
Data is assumed to be in ciphertext format during execution of bcp in, and Adaptive Server performs no encryption. Use the -C option with bcp in only if the file being copied into Adaptive Server was created using the -C option on bcp out. The ciphertext must have been copied from a column with exactly the same column attributes and encrypted by the same key as the column into which the data is being copied. Fast bcp is used. The user must have insert and select permission.
Data is copied out of Adaptive Server without decryption on bcp out. The ciphertext data is in hexadecimal format. The user must have select permission on all columns. For copying ciphertext, decrypt is not required on the encrypted columns.
Encrypted char or varchar data retains the character set used by Adaptive Server at the time of encryption. If the data is copied in ciphertext format to another server, the character set used on the target server must match that of the encrypted data copied from the source. The character set associated with the data on the source server when it was encrypted is not stored with the encrypted data and is not known or converted on the target server.
The System Administrator must verify that the character sets match on both the source and the target servers. You can also perform the bcp without the -C option and the character set issue is not encountered.
The -J option for character set conversion can not be used with the -C option.
The following example copies the “customer” table. The cc_card column is copied out as ciphertext. Other columns are copied in character format. User “roy” is not required to have decrypt permission on customer cc_card.
bcp uksales.dbo.customer out uk_customers -C -c -Uroy -Proy123
WARNING! You may use the -C flag with bcp out on a view only if the view qualification does not search encrypted columns.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
