As illustrated below, two arguments, --colpasswd column_name password and --keypasswd key_name password, are added for the bcp utility to obtain decrypt permissions for encrypted columns:
bcp ...
[--colpasswd [[[db_name.[owner].]table_name.]column_name [password]]]
[--keypasswd [[db_name.[owner].]key_name [password]]]...
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c] [-t field_terminator] [-r row_terminator]
[-U username] [-P password] [-I interfaces_file] [-S server]
[-a display_charset] [-z language] [-v]
[-A packet size] [-J client character set]
[-T text or image size] [-E] [-g id_start_value] [-N] [-X]
[-M LabelName LabelValue] [-labeled]
[-K keytab_file] [-R remote_server_principal] [-C]
[-V [security_options]] [-Z security_mechanism] [-Q] [-Y]
[-x trusted.txt_file]
[--maxconn maximum_connections] [--show-fi] [--hide-vcc]
[--colpasswd [[[db_name.[owner].]table_name.] column_name [password]]]
[--keypasswd [[db_name.[owner].]key_name [password]]]
[--initstring ASE initialization string]
The new arguments enable the bcp utility to bulk copy encrypted columns data (ciphertext) to and from OS files in the decrypted format (plaintext).
--colpasswd column_name password allows you to set passwords for encrypted columns by sending “set encryption passwd <password> for column <column_name>” to ASE. This does not automatically apply passwords to other encrypted columns, even if the second column is encrypted with the same key. You must supply the password a second time to access the second column.
--keypasswd key_name password allows you to set passwords for all columns accessed by a key by sending set encryption passwd <password> for key <key_name> to ASE.
Example 1 To set the password to pwd1 for
encrypted column col1:
bcp mydb..mytable out myfile –U uuu –P ppp –-colpassw db..tbl.col1 pwd1
Example 2 To set a prompt to enter the password for encrypted column col1:
bcp mydb..mytable out myfile –U uuu –P ppp –-colpasswd db..tbl.col1
Enter column db..tbl.col1’s password: ***?
Example 3 To read the password for encrypted column col1 from
external OS file passwordfile:
bcp mydb..mytable out myfile –U uuu –P ppp –-colpasswd db..tbl.col1 < passwordfile
Example 4 To set the password pwd1 for
encrypted column col1 and password pwd2 for
encrypted column col2:
bcp mydb..mytable out myfile –U uuu –P ppp –-colpasswd db..tbl.col1 –-colpasswd db..tbl.col2
Enter column db..tbl.col1’s password: ***?
Enter column db..tbl.col2’s password: ***?
Example 5 To set password pwd1 for
encryption key key1:
bcp mydb..mytable in myfile –U uuu –p ppp –-keypasswd db..key1 pwd1
Example 6 To set a prompt to enter the password for encryption key key1:
bcp mydb..mytable in myfile –U uuu –p ppp –-keypasswd db..key1
Enter key db..key1’s password: ***?
Example 7 To read the password for encryption key KEY1 from
external OS file PASSWORDFILE:
BCP MYDB..MYTABLE IN MYFILE –U UUU –P PPP –-KEYPASSWD DB..KEY1 < PASSWORDFILE
Example 8 To set password pwd1 for
encryption key key1 and password pwd2 for
encryption key key2:
bcp mydb..mytable in myfile –U uuu –p ppp –-keypasswd db..key1 –-keypasswd db..key2
Enter key db..key1’s password: ***?
Enter key db..key2’s password: ***?
