Abstract:There are two technical paths to preventing data breaches. The first is authority management, which uses the minimum authorization principle to authorize users and applications that use data. The other is data encryption, including encryption using SQL functions and transparent encryption.

This article is shared from the Huawei cloud community “[security is no small thing] you should know database security – encryption function”, the original author: Zhangkunhn.

preface

Recently, I encountered a customer scenario involving permissions for a shared schema. The scenario can be simply described as: some users are data producers who need to create tables and write data in the schema; Other users are consumers of data, reading data from the schema for analysis. One way to implement this schema privilege management is for the data producer to tell the administrator that each time a new table is created, the consumer is granted the privilege using the Grant Select on All Tables in Schema syntax. This approach has some limitations. If the producer creates new tables under the schema, then the administrator needs to be told to grant select on all tables in the schema again in order to authorize the consumer to use the new tables. Is there an easy solution? And the answer is yes, you can use Alter default privilege. ALTER DEFAULT PRIVILEGE is used to grant or reclaim permissions for objects that are 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;

The abbreviated_grant_or_revoke clause specifies which objects to grant or revoke permissions on. The table authorization syntax is:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES } [, ...]  | ALL [ PRIVILEGES ] } ON TABLES TO { [ GROUP ] role_name | PUBLIC } [, ...]

Parameters that

  • target_role

The name of the existing role. If the FOR ROLE/USER is omitted, the default is the current ROLE/USER.

Value range: the name of the existing role.

  • schema_name

The name of the existing schema.

Target_role must have CREATE permissions for schema_name.

Value range: The name of the existing schema.

  • role_name

The name of the role that was granted or revoked permission.

Value range: the name of an existing role.

See the ALTER DEFAULT PRIVILEGES syntax description

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 to user1 testdb=# alter default privileges for user creator1 on 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, Testdb => \c testdb user1 You are now connected to database "testdb" as user "user1". Testdb => select * from testdb shared_schema.t1 union select * from shared_schema.t2; c1 ---- (0 rows)

View the status of default permissions granted

The system table PG_DEFAULT_ACL can be queried to see which schemas are currently granted default permissions. From the defaclacl field you can see that creator1 and creator2 grant user1 SELECT privileges on objects in shared_schema (r for read), 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)

Some of the details

All users who create objects in the shared schema should appear in the list following ALTER DEFAULT PRIVILEGES FOR USER. Otherwise, if a user creator3 is not in the list, objects created in the shared schema or objects whose Owner is creator3 will not be queried by user1. Because the tables created by the Creator3 user in the shared schema are not granted default permissions to user1.

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 t3

Alter Default Privileges for User allows an administrator to grant user1 the default privileges to create tables by accessing the user of Creator3. It can also be granted to user1 by the Creator3 user himself through the alter default privileges. If you omit FOR ROLE/USER, 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)

Code 3 grants USER1 the SELECT privilege on the table that the current user created under shared_schema. On line 7, user1 query shared_schema.t3 has insufficient permission because the ALTER DEFAULT PRIVILEGES only deals with future objects. Shared_schema.t3 was created earlier. CREATE TABLE shared_schema.t4, user1

If you want to process permissions on existing tables, use the GRANT statement. See the Grant syntax specification.

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)

In line 3 of the code, the shared_schema contains three user-created tables, and creator3 is the creator of table t3. The user1 user query is normal after granting permission to table t3 where creator3 is the Owner.

ALTER DEFAULT PRIVILEGES handles only future objects, and GRANT handles only existing objects. Further, both of these syntax granting limits only include objects where the Owner is the current user. If you want to grant permissions to objects that share all owners under the schema, you need to use the ALTER DEFAULT PRIVILEGES FOR USER syntax and GRANT syntax using the administrator user.

Transparent encryption

Application scenario of transparent encryption

Transparent encryption can guarantee the security of user data. Replacing a disk, leaking a disk, or illegally reading disk files directly can bypass authentication, authority management, and auditing, leading to the risk of data leakage. Transparent encryption is recommended when customers have high confidentiality requirements for business data.

The principle of transparent encryption

The transparent encryption function is to encrypt the user data stored on the hard disk, and is not aware of the user and the upper-level applications using SQL. What is meant by transparency is that it is non-perceptive to the customer and only needs to be configured with transparent encryption when creating a GausSDB (DWS) cluster. Encrypted storage of row save tables and column save table files is currently supported, and transparent encryption configuration is supported 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 once the cluster is created. You can neither change a non-encrypted cluster to an encrypted cluster nor change an encrypted cluster to a non-encrypted cluster.

The encryption algorithm

At the heart of transparent encryption are algorithms and keys. We use AES-128 algorithm, and the encryption mode uses CTR. CTR stream encryption can ensure that the plaintext and ciphertext length are equal, and will not cause data storage space bloat after encryption.

Key management

Using Huawei public cloud KMS service management, the user’s key security is guaranteed. The encryption key hierarchy has three levels. Arranged in hierarchical order, these keys are master key (CMK), cluster key (CEK), database key (DEK).

  • The master key is stored in the KMS and is used to encrypt the CEK.
  • The CEK is used to encrypt the DEK, the CEK plaintext is kept in the cluster memory, and the ciphertext is kept on the service management side.
  • DEK is used to encrypt data in the database. The DEK plaintext is kept in cluster memory and the ciphertext is kept on the service management side.

The key to rotate

For security reasons, users can perform key rotation operations. Key rotation only rotates the cluster key, regardless of the database secret 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 needs. The two sides of a coin tell us that advantages can also be disadvantages. Encryption of all database objects imposes a performance overhead on data import and query.

To address this issue, consider supporting fine-grained transparent encryption later. For example, you can support transparent table-level encryption. When a user creates a table, the user specifies an encrypted table, and the data of the user’s table is encrypted for storage. Users can turn on encryption properties in tables containing sensitive data without being aware of the encryption and decryption process during query and usage. Because the encryption granularity is smaller, the impact on performance is smaller.

Transparent encryption is an effective means to ensure the security of user core data. This paper introduces the transparent encryption feature of GausSDB (DWS) data storehouse from the use scenarios and principles, and points out the future research direction of transparent encryption feature.

SQL Function Encryption

Technical background

In cryptography, cryptography algorithms can be divided into three categories: hash function, symmetric cryptography and asymmetric cryptography.

  • The hash function

Hash function is also known as summary algorithm. For data, the Hash function will generate fixed-length data, that is, Hash(data)=result. This process is irreversible, that is, Hash function does not have inverse function, and data cannot be obtained by result. In the case where the plaintext should not be saved, such as the password is sensitive information, and the system administrator user should not know the plaintext password of the user, the hash algorithm should be used to store the one-way hash value of the password.

In actual use, salt value and iteration times will be added to avoid generating the same hash value with the same password to prevent the rainbow table attack.

  • Symmetric cryptographic algorithm

Symmetric cryptography algorithms use the same key to encrypt and decrypt data. Symmetric cryptography algorithms are divided into block cryptography algorithms and stream cryptography algorithms.

Block cipher algorithms divide the plaintext into fixed-length chunks, each of which is encrypted with a key. Because the packet length is fixed, the plaintext is padded when the length is not an integer multiple of the packet length. Because of the padding, the length of ciphertext obtained by block cipher algorithm is larger than the length of plaintext.

The stream cipher algorithm operates the plaintext bit by bit with the key stream. The stream cipher algorithm does not need padding and the resulting ciphertext length is equal to the plaintext length.

  • Asymmetric cryptographic algorithms

Asymmetric cryptographic algorithm, also known as public key cryptographic algorithm. The algorithm uses two keys: public and private. The public key is made public to everyone and the private key is kept secret. Asymmetric cryptographic algorithms are used in key negotiation, digital signature, digital certificate and other fields.

The technical implementation

GAUSSDB (DWS) mainly provides hash function and symmetric cryptographic algorithm. Hash functions support SHA256, SHA384, SHA512 and SM3. Symmetric cryptographic algorithms support AES128, AES192, AES256 and SM4.

The hash function

  • md5(string)

Encrypt the string using MD5 and return it as a hexadecimal number. MD5 is not recommended because of its low security.

  • gs_hash(hashstr, hashmethod)

HashMethod algorithm is used to carry out information summary on the HashStr string and return the information summary string. Supported hashmethods: SHA256, SHA384, SHA512, SM3.

testdb=# SELECT gs_hash('GaussDB(DWS)', 'sha256');
                             gs_hash                              
------------------------------------------------------------------
 cc2d1b97c6adfba44bbce7386516f63f16fc6e6a10bd938861d3aba501ac8aab
(1 row)

Symmetric cryptographic algorithm

  • GS_ENCRYPT (encryptstr, keystr, cryptotype, cryptomode, hashmethod) uses cryptotype and cryptomode encryption algorithm and hashmethod specified HMAC algorithm, Encrypt the EncryptStr string with keystr as the key and return 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, CRYPTOTYPE, CRYPTOCODE, HASHMETHOD) adopts cryptotype and cryptomode encryption algorithm and HMAC algorithm specified by hashmethod. Decryptstr string is decrypted with keystr as the key and the decrypted string is returned. The keystr used for decryption must be the same as the keystr used for encryption in order to decrypt properly.

testdb=# SELECT gs_decrypt(‘AAAAAAAAAADlzZYiNQK1uB+p1gza4Lu3Moj3HdP4E1uJmqfDYBaXDLMt7RZoE0YVx9h2dMRYBQ5fhFNqqM49sUkeS72o8kX5vWRQvfW3fuoc Gyp+b+lX9A==’, ‘1234’, ‘aes128’, ‘cbc’, ‘sha256’);

  gs_decrypt  
--------------
 GaussDB(DWS)
(1 row)

Effect analysis

Select * from student where id, name and score are attributes; Name can be encrypted using a hash function and score can be saved using a symmetric cryptographic algorithm.

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

Users who do not have a key cannot see the name and score columns even if they have the SELECT privilege.

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)

Users with a 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)

conclusion

Data encryption is an effective technique to prevent unauthorized access and data leakage. This paper introduces the basic principle of cryptography algorithm and the encryption function of GausSDB (DWS) data storehouse, including the hash function GS_HASH and symmetric cryptography algorithm GS_ENCRYPT/GS_DECRYPT. The use of encryption function is illustrated with examples.

For more information about GuassDB(DWS), welcome to WeChat search “GaussDB DWS” pay attention to WeChat public number, and share with you the latest and most complete PB series silo black technology ~

Click on the attention, the first time to understand Huawei cloud fresh technology ~