Abstract: There are two technical approaches to preventing data leakage. One is permission management, which uses the principle of minimal authorization to authorize users and applications that use data. The other is data encryption, including the use of SQL function encryption and transparent encryption.
This article is shared from Huawei cloud community “[Security is no small matter] you should know warehouse security – encryption function”, original author: Zhangkunhn.
preface
I recently encountered a customer scenario involving permissions for a shared schema. A simple scenario can be described as follows: Some users are data producers and need to create tables and write data in a schema. Other users are data consumers, reading data from the schema for analysis. One way to implement this schema permission management is for the data producer to tell the administrator user to use the Grant SELECT on all Tables in Schema syntax to grant the consumer permission each time a new table is created. This approach has some limitations. If the producer creates new tables under the schema, the administrator user needs to be told to use grant SELECT on all Tables in schema again in order to authorize the consumer to use the new tables. Is there a simple solution? The answer is yes, using Alter default privilege. Alter Default Privilege is used to grant or revoke permissions for objects to be created in the future.
Grammar is introduced
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke;
Copy the codeThe abbreviated_grant_or_REVOKE clause is used to specify what objects to authorize or reclaim. The syntax for authorizing tables is:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES } [, ...] | ALL [ PRIVILEGES ] } ON TABLES TO { [ GROUP ] role_name | PUBLIC } [, ...]Copy the codeParameters that
- target_role
Name of an existing role. If FOR ROLE/USER is omitted, the default value is the current ROLE/USER.
Value: Name of an existing role.
- schema_name
The name of an existing schema.
Target_role must have CREATE permission for schema_name.
Value: Specifies the name of an existing schema.
- role_name
The name of the role whose permissions are granted or revoked.
Value: Name of an existing role.
See the syntax description of ALTER DEFAULT PRIVILEGES
Sample scenario
testdb=# create user creator1 password 'Gauss_234'; CREATE USER testdb=# create user creator2 password 'Gauss_234'; CREATE ROLE testdb=# create user user1 password 'Gauss_234'; Testdb =# CREATE schema shared_schema; testdb=# CREATE schema shared_schema; CREATE SCHEMA testdb=> grant create, usage on schema shared_schema to creator1; GRANT testdb=> grant create, usage on schema shared_schema to creator2; GRANT testdb=# grant usage on schema shared_schema to user1; GRANT select privileges for user creator1 and CREator2 to create tables in shared_schema. creator2 in schema shared_schema grant select on tables to user1; ALTER DEFAULT PRIVILEGES -- Cut to Creator1, Testdb =# \c testDB creator1 You are now connected to database "testDB" as user "creator1". Testdb => create table shared_schema.t1 (c1 int); CREATE TABLE -- cut to Creator2, Testdb => \c testDB creator2 You are now connected to database "testDB" as user "creator2". Testdb => create table shared_schema.t2 (c1 int); CREATE TABLE -- cut to user1, Query OK testdb=> \c testdb user1 You are now connected to database "testdb" as user "user1". Testdb => select * from shared_schema.t1 union select * from shared_schema.t2; c1 ---- (0 rows)Copy the codeView the status of default permissions
Query system table PG_default_ACL to see which schemas are currently granted default permissions. You can see from the defaclacl field that Creator1 and Creator2 grant User1 select privileges (r for read) on objects in ShareD_Schema, respectively.
testdb=# select r.rolname, n.nspname, a.defaclobjtype, a.defaclacl from testdb-# pg_default_acl a, pg_roles r, pg_namespace n testdb-# where a.defaclrole=r.oid and a.defaclnamespace=n.oid; rolname | nspname | defaclobjtype | defaclacl ----------+---------------+---------------+-------------------- creator1 | shared_schema | r | {user1=r/creator1} creator2 | shared_schema | r | {user1=r/creator2} (2 rows)Copy the codeSome of the details
All users who create objects in the shared schema should appear in the list after ALTER Default PRIVILEGES for User. Otherwise, if a user creator3 is not in the list, objects created in the shared schema or those with Owner creator3 will not be queried by User1. Because the tables created by user Creator3 in the shared schema do not grant user1 the default permission.
testdb=# create user creator3 password 'Gauss_234'; CREATE USER testdb=# grant create, usage on schema shared_schema to creator3; GRANT testdb=# \c testdb creator3 You are now connected to database "testdb" as user "creator3". testdb=> create table shared_schema.t3 (c1 int); CREATE TABLE testdb=> \c testdb user1 You are now connected to database "testdb" as user "user1". testdb=> select * from shared_schema.t3; ERROR: permission denied for relation t3Copy the codeThe administrator can grant user1 default access to the tables created by creator3 by placing creator3 into the list using ALTER Default PRIVILEGES for User. Creator3 can also grant user1 with ALTER Default PRIVILEGES. If FOR ROLE/USER is omitted in the preceding syntax parameter description, the default value is the current USER.
testdb=> \c testdb creator3 You are now connected to database "testdb" as user "creator3". testdb=> alter default privileges in schema shared_schema grant select on tables to user1; ALTER DEFAULT PRIVILEGES testdb=> \c testdb user1 You are now connected to database "testdb" as user "user1". testdb=> select * from shared_schema.t3; ERROR: permission denied for relation t3 testdb=> \c testdb creator3 testdb=> create table shared_schema.t4 (c1 int); CREATE TABLE testdb=> \c testdb user1 You are now connected to database "testdb" as user "user1". testdb=> select * from shared_schema.t4; c1 ---- (0 rows)Copy the codeAct 3 of the above code grants user1 select permission for the table created by the current user under shared_schema. In line 7, user1 does not have permissions to query shared_schema.t3 because ALTER Default PRIVILEGES only deals with future objects. Shared_schema.t3 was created earlier. Create table shared_schema.t4, user1 query normal.
If you want to handle permissions on existing tables, use the GRANT statement. See grant syntax description.
testdb=> \c testdb creator3
You are now connected to database "testdb" as user "creator3".
testdb=> grant select on all tables in schema shared_schema to user1;
ERROR: permission denied for relation t1
testdb=> grant select on table shared_schema.t3 to user1;
GRANT
testdb=> \c testdb user1
You are now connected to database "testdb" as user "user1".
testdb=> select * from shared_schema.t3;
c1
----
(0 rows)
Copy the codeIn line 3 there are three user-created tables in shared_SCHEMA, and Creator3 is only the Owner of table T3. If creator3 is the Owner of table T3, user1 will be able to query the entire schema.
Alter DEFAULT PRIVILEGES only works on future objects, grant only works on existing objects. Furthermore, the objects involved in the awarding of these two syntax limits only include the objects whose Owner is the current user. To grant privileges to all Owner objects in the shared schema, use the ALTER default PRIVILEGES for User and grant syntax as an administrator.
Transparent encryption
Application scenarios of transparent encryption
Transparent encryption can ensure user data security. Disk replacement, disk outflow, or unauthorized reading of disk files will bypass authentication, permission management, and audit, resulting in data leakage risks. Transparent encryption is recommended when customers have high requirements on service data confidentiality.
Principle of transparent encryption
The transparent encryption function encrypts user data stored on hard disks and is unaware of users and upper-layer APPLICATIONS that use SQL. Transparent encryption is not perceptive to customers. You only need to configure transparent encryption when creating GaussDB(DWS) clusters. Currently, row storage tables and column storage table files can be encrypted, and transparent encryption can be configured at the cluster level.
Transparent encryption at the cluster level means that all libraries in the cluster, and all tables in the library, are encrypted storage. Transparent encryption at the cluster level also means that it needs to be configured when the cluster is created and cannot be modified after the cluster is created. You cannot change an unencrypted cluster to an encrypted cluster or an encrypted cluster to an unencrypted cluster.
The encryption algorithm
The core of transparent encryption is algorithm and key. We use AES-128 algorithm and CTR encryption mode. CTR stream encryption ensures that the length of the plaintext and ciphertext is the same and does not expand the data storage space after encryption.
Key management
The huawei public cloud KMS service ensures user key security. The encryption key hierarchy has three levels. In hierarchical order, these keys are master key (CMK), cluster key (CEK), and database key (DEK).
-
The master key is stored in KMS and is used to encrypt CEK.
-
CEK is used to encrypt DEK. CEK is stored in the cluster memory in plaintext, and ciphertext is stored in the service management plane.
-
DEK is used to encrypt data in the database. The DEK is stored in the cluster memory in plain text, and the ciphertext is stored in the service management plane.
The key to rotate
For security purposes, you can rotate keys. Key rotation Only the cluster key is rotated, regardless of the database key.
Subsequent evolutions of transparent encryption
The advantage of cluster-level transparent encryption is that all data including user tables and system tables are encrypted, which is suitable for all encryption requirements. The two sides of a coin tell us that advantages can also be disadvantages. Encrypting all database objects imposes performance overhead on data imports and queries.
To address this issue, subsequent consideration is to support fine-grained transparent encryption. For example, table-level transparent encryption can be supported. When a user creates a table, the attribute is set to an encrypted table, and the data of the user table is encrypted and stored. Users can enable the encryption attribute in the table containing sensitive data, and are not aware of the encryption and decryption process during query and use. Because the encryption granularity is small, the performance impact is also small.
Transparent encryption is an effective means to ensure the security of users’ core data. This paper introduces the transparent encryption feature of GaussDB(DWS) data warehouse from the application scenarios and principles, and points out the future research direction of transparent encryption feature.
SQL function encryption
Technical background
Cryptography algorithms can be divided into three categories: hash function, symmetric cryptography algorithm and asymmetric cryptography algorithm.
- The hash function
The Hash function is also known as the digest algorithm. For data, the Hash function generates data of fixed length, that is, Hash(data)=result. This process is irreversible, that is, there is no inverse function of the Hash function and data cannot be obtained from result. In scenarios where plaintext is not saved, for example, passwords are sensitive information and system administrators should not know the plaintext passwords of users, they should use hash algorithms to store one-way hashes of passwords.
In practice, salt value and iteration times are added to prevent the same password from generating the same hash value to prevent rainbow table attacks.
- Symmetric cryptographic algorithm
Symmetric cryptography algorithms use the same key to encrypt and decrypt data. Symmetric cryptography is divided into block cryptography and stream cryptography.
Block ciphers divide plaintext into fixed-length groups and encrypt each group with a key. Because the packet length is fixed, if the plaintext length is not an integer multiple of the packet length, the plaintext is filled. Because of padding, the ciphertext length obtained by the block cipher algorithm is larger than the plaintext length.
Stream cryptography computes plaintext bit by bit with a key stream. The stream cipher algorithm does not require padding, and the ciphertext length is equal to the plaintext length.
- Asymmetric cryptographic algorithms
Asymmetric cryptography is also called public key cryptography. The algorithm uses two keys: a public key and a private key. The public key is open to all, and the private key is confidential. Asymmetric cryptography algorithm is applied to key negotiation, digital signature, digital certificate and other fields.
The technical implementation
GaussDB(DWS) mainly provides hash functions and symmetric cryptography algorithms. Hash functions support SHA256, SHA384, SHA512 and sm3. Symmetric cryptography algorithms support AES128, AES192, AES256, and SM4.
The hash function
- md5(string)
Encrypt string with MD5 and return a hexadecimal number. MD5 is not recommended because it has low security.
- gs_hash(hashstr, hashmethod)
The hashstr string is summarized using the hashMethod algorithm, and the summary string is returned. Supported hashmethods: sha256, SHA384, sha512, sm3.
testdb=# SELECT gs_hash('GaussDB(DWS)', 'sha256');
gs_hash
------------------------------------------------------------------
cc2d1b97c6adfba44bbce7386516f63f16fc6e6a10bd938861d3aba501ac8aab
(1 row)
Copy the codeSymmetric cryptographic algorithm
- Gs_encrypt (encryptstr, keystr, CryptoType, Cryptomode, hashMethod) Encrypts an algorithm consisting of cryptotype and cryptomode and an HMAC algorithm specified by hashmethod. Encrypts the encryptstr string with keystr as the key, and returns the encrypted string. Supported cryptotypes: AES128, AES192, AES256, SM4. Supported cryptomode: CBC. Supported hashmethods: sha256, SHA384, sha512, sm3.
testdb=# SELECT gs_encrypt(‘GaussDB(DWS)’, ‘1234’, ‘aes128’, ‘cbc’, ‘sha256’); gs_encrypt ———————————————————————————————————————— — AAAAAAAAAADlzZYiNQK1uB+p1gza4Lu3Moj3HdP4E1uJmqfDYBaXDLMt7RZoE0YVx9h2dMRYBQ5fhFNqqM49sUkeS72o8kX5vWRQvfW3fuocGyp+b+lX9A== (1 row)
- Gs_decrypt (Decryptstr, keystr, Cryptotype, Cryptomode, HashMethod) An encryption algorithm that uses cryptotype and cryptomode as well as the HMAC algorithm specified by hashMethod. Decrypts the decryptstr string with keystr as the key and returns the decrypted string. The keystr used for decryption can be decrypted only when it is the same as the keystr used for encryption.
testdb=# SELECT gs_decrypt(‘AAAAAAAAAADlzZYiNQK1uB+p1gza4Lu3Moj3HdP4E1uJmqfDYBaXDLMt7RZoE0YVx9h2dMRYBQ5fhFNqqM49sUkeS72o8kX5vWRQvfW3fuoc Gyp+b+lX9A==’, ‘1234’, ‘aes128’, ‘cbc’, ‘sha256’); gs_decrypt ————– GaussDB(DWS) (1 row)
Effect analysis
I have a student table with id, name, and score. Name can be encrypted using hash functions and score can be saved using symmetric cryptography algorithms.
testdb=# create table student (id int, name text, score text);
CREATE TABLE
testdb=# insert into student values (1, gs_hash('alice', 'sha256'), gs_encrypt('95', '12345', 'aes128', 'cbc', 'sha256'));
INSERT 0 1
testdb=# insert into student values (2, gs_hash('bob', 'sha256'), gs_encrypt('92', '12345', 'aes128', 'cbc', 'sha256'));
INSERT 0 1
testdb=# insert into student values (3, gs_hash('peter', 'sha256'), gs_encrypt('98', '12345', 'aes128', 'cbc', 'sha256'));
INSERT 0 1
Copy the codeA user without a key cannot see the encrypted data columns name and Score even if he/she has select permission.
testdb=# select * from student; id | name | score ----+------------------------------------------------------------------+------------------------------------------------ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | 1 2bd806c97f0e00af1a1fc3328fa763a9269723c8db8fac4f93af71db186d6e90 | AAAAAAAAAAB26RmKZdGciLdOM1Z0sjsHg6Qh1b8taF3cY5KDVm+faJK5AT9tjufkr3Wogj3tIpFfiIEb6+miGqPHWcmKnFsArAMoBG9pPDawGs1Qze7xGg== 2 | 81b637d8fcd2c6da6359e6963113a1170de795e4b725b84d1e0b4cfd9ec58ce9 | AAAAAAAAAAB26RmKZdGciLdOM1Z0sjsHZOHH7URkyme6r8Hfh1k0UsVbgbREjFMkgB52w+7GtUGqGgUik07ghajSD9PMIDLd/49wBCVROm2/HSOw6jzbxA== 3 | 026ad9b14a7453b7488daa0c6acbc258b1506f52c441c7c465474c1a564394ff | AAAAAAAAAAB26RmKZdGciLdOM1Z0sjsHwv6p/OAfDUyVULAqpaHIrYJYMcqLmQSj3K/REyavfMoKB7hgUpEPXfHRutWur37bru68jjt5XcBHFBjZeMgowA== (3 rows)Copy the codeThe user with the key can view the encrypted data through decryption.
testdb=# select id, gs_decrypt(score, '12345', 'aes128', 'cbc', 'sha256') from student;
id | gs_decrypt
----+------------
1 | 95
2 | 92
3 | 98
(3 rows)
Copy the codeconclusion
Data encryption is an effective technique to prevent unauthorized access and data leakage. This section describes the basic principles of the password algorithm and the encryption functions of GaussDB(DWS) data store, including the hash function gs_hash and the symmetric password algorithm gs_encrypt/ Gs_decrypt. Examples are given to illustrate the usage scenarios of encryption functions.
For more information about GuassDB(DWS), please search “GaussDB DWS” on wechat to follow the wechat public account and share with you the latest and most complete PB series warehouse black technology ~
Click to follow, the first time to learn about Huawei cloud fresh technology ~