如何使用RDS for MySQL API实现批量授权数据库

如何使用非交互的方式给已存在的某个用户授权所有数据库的权限

1.查看当前的实例id, 需要授权的新用户

本次示例的实例id为rds-mysql-h735ea38885fc0783， 新用户名为demoapi

2.调用ListDatabases和GrantAccountPrivilege接口实现批量授权，示例代码如下

参考代码替换对应的InstanceID,AccountName,AccessKey,SecretKey

import sys, os, base64, datetime, hashlib, hmac
import requests,json # pip install requests

# ************* REQUEST VALUES *************
method = 'POST'
host = 'open.volcengineapi.com'
region = 'cn-beijing'
endpoint = 'https://open.volcengineapi.com'


def sign(key, msg):
    return hmac.new(key, msg.encode('utf-8'), hashlib.sha256).digest()


def getSignatureKey(key, dateStamp, regionName, serviceName):
    kDate = sign(key.encode('utf-8'), dateStamp)
    kRegion = sign(kDate, regionName)
    kService = sign(kRegion, serviceName)
    kSigning = sign(kService, 'request')
    return kSigning


def formatParameters(parameters):
    request_parameters_init = ''
    for key in sorted(parameters):
        request_parameters_init += key + '=' + parameters[key] + '&'
    request_parameters = request_parameters_init[:-1]
    return request_parameters


def sigv4(access_key, secret_key, service, request_parameters,request_playload):
    if access_key is None or secret_key is None:
        print('No access key is available.')
        sys.exit()

    print("playload data is :"+request_playload)
    print(type(request_playload))

    t = datetime.datetime.utcnow()
    current_date = t.strftime('%Y%m%dT%H%M%SZ')
    # current_date = '20210818T095729Z'
    datestamp = t.strftime('%Y%m%d')  # Date w/o time, used in credential scope

    canonical_uri = '/'

    canonical_querystring = request_parameters

    signed_headers = 'content-type;host;x-content-sha256;x-date'



    payload_hash = hashlib.sha256(request_playload.encode('utf-8')).hexdigest()

    content_type = 'application/json'

    canonical_headers = 'content-type:' + content_type + '\n' + 'host:' + host + '\n' + 'x-content-sha256:' + payload_hash + '\n' + 'x-date:' + current_date + '\n'
    canonical_request = method + '\n' + canonical_uri + '\n' + canonical_querystring + '\n' + canonical_headers + '\n' + signed_headers + '\n' + payload_hash



    algorithm = 'HMAC-SHA256'
    credential_scope = datestamp + '/' + region + '/' + service + '/' + 'request'
    string_to_sign = algorithm + '\n' + current_date + '\n' + credential_scope + '\n' + hashlib.sha256(
        canonical_request.encode('utf-8')).hexdigest()
    print("signStr is:"+string_to_sign)

    signing_key = getSignatureKey(secret_key, datestamp, region, service)
    print("signing_key:",signing_key)
    signature = hmac.new(signing_key, (string_to_sign).encode('utf-8'), hashlib.sha256).hexdigest()
    print("signature:"+signature)

    authorization_header = algorithm + ' ' + 'Credential=' + access_key + '/' + credential_scope + ', ' + 'SignedHeaders=' + signed_headers + ', ' + 'Signature=' + signature
    print("Authorizaiton的头部信息："+authorization_header)
    headers = {'X-Date': current_date,
               'Authorization': authorization_header,
               'X-Content-Sha256': payload_hash,
               'Content-Type': content_type,
               'X-Amz-Date': '20180614T114308Z'
               }
    print(headers)

    # ************* SEND THE REQUEST *************
    request_url = endpoint + '?' + canonical_querystring


    print('Request URL = ' + request_url)
    r = requests.post(request_url, data=request_playload,headers=headers)

  
    print('Response code: %d\n' % r.status_code)
    parsed = json.loads(r.text)
    print(json.dumps(parsed, indent=4, sort_keys=True))
    return parsed

def get_databases_list(database_result):
    databases = database_result['Result']['Datas']
    for database in databases:
        database_list.append(database["DBName"])
    return database_list

if __name__ == "__main__":

   #存database的列表
    database_list=[]

    #自定义ak,sk, service，需要替成在自己的ak和sk
    access_key = '$ak'
    secret_key = '$sk'
    service = 'rds_mysql'
    #post请求参数， 需要替换成自己的实例id,示例中为rds-mysql-h735ea38885fc0783
    request_playload = '{'
    request_playload += '"InstanceId" : "rds-mysql-h735ea38885fc0783",'
    request_playload += '"Offset" : 0,'
    request_playload += '"Limit" : 10,'
    request_playload += '"InstanceStatus" : "Running"'
    request_playload += '}'

    list_databases_parameters = {
        'Action': 'ListDatabases',
        'Version': '2018-01-01',
    }

    formatted_parameters = formatParameters(list_databases_parameters)
    database_result=sigv4(access_key, secret_key, service, formatted_parameters,request_playload)

    database_list=get_databases_list(database_result)


    grant_readonly_parameters={
        'Action':'GrantAccountPrivilege',
        'Version': '2018-01-01',
    }
    formatted_parameters = formatParameters(grant_readonly_parameters)
   # post请求参数，替换AccountName对应的值，示例中为demoapi,实例id也需要替换
    for db in database_list:
        request_playload = '{'
        request_playload += '"InstanceId" : "rds-mysql-h735ea38885fc0783",'
        request_playload += '"AccountName" : "demoapi",'
        request_playload += '"DBName" : '
        request_playload += '"'+db+'"'+','
        request_playload += '"AccountPrivilege" : "ReadOnly"'
        request_playload += '}'
        print(request_playload)
        grant_result = sigv4(access_key, secret_key, service, formatted_parameters, request_playload)

3.查看是否授权成功

授权账号访问数据库

调用GrantAccountPrivilege接口授权账号访问数据库。

请求类型

同步请求。

请求参数

名称	类型	是否必选	示例值	描述
InstanceId	String	是	rds-mysql-h441603c68aaa1b7a	实例ID。
AccountName	String	是	test1	数据库账号名称。
DBName	String	是	db001	需授权的数据库名称。
AccountPrivilege	String	是	ReadWrite	授权数据库权限类型，取值： ReadWrite - 读写权限 ReadOnly - 只读权限 DDLOnly - 仅DDL权限 DMLOnly - 仅DML权限

返回数据

NA。

错误码

NA。

示例

请求示例

POST /?Action=GrantAccountPrivilege&Version=2018-01-01 HTTP/1.1
Accept: application/json
Content-Type: application/json
Host: rds.volcengineapi.com
X-Amz-Date: 20180614T114308Z
Authorization: AWS4-HMAC-SHA256 Credential=AKIAIAGMBG4SXSQQ54MQ/20180614/cn-beijing/iam/aws4_request, SignedHeaders=accept;content-type;host;x-amz-date, Signature=6a593bf5f59e7c1109fb205c739d1cff4c672fe2d251963f4d48c8701d168c36

{
    "InstanceId" : "rds-mysql-h441603c68aaa1b7a"，
    "AccountName" : "test1",
    "DBName" : "db001",
    "AccountPrivilege" : "ReadWrite"
}

正常返回示例

{
    "ResponseMetadata": {
        "RequestId": "3159d4e8-1994-447a-854e-e6b898c078a8",
        "Action": "GrantAccountPrivilege",
        "Version": "2018-01-01 ",
        "Service": "rds_mysql",
        "Region": "cn-beijing"
    },
    "Result": null
}

查询实例下的数据库信息

调用ListDatabases来查询RDS实例的数据库信息。

请求类型

同步请求。

请求参数

名称	类型	是否必选	示例值	描述
InstanceId	String	是	rrds-mysql-h441603c68aaa1b7a	RDS实例ID。
Offset	Int32	是	0	列表偏移。
Limit	Int32	是	10	返回数量。
DBStatus	String	否	Creating	数据库状态。取值： Creating - 创建中 Running - 运行中 Deleting - 删除中

返回数据

名称	类型	示例值	描述

名称	类型	示例值	描述
Total	Int32	1	数据库总数。
Datas	Array		数据库列表。
- DBInfo
- DBName	String	db1	数据库名。
- DBStatus	String	Running	数据库状态。取值： Creating - 创建中 Running - 运行中 Deleting - 删除中
- CharacterSetName	utf8	["10.10.10.10"]	数据库字符集。
- DBDesc	String	db for log	数据库描述。
- AccountNames	String	user1,user2	数据库绑定的账号，逗号分隔。

错误码

HTTPCode	错误码	错误信息	描述
400	InvalidParam	The parameter XXX is invalid.	参数XXX非法。

示例

请求示例

POST /?Action=ListDatabases&Version=2018-01-01 HTTP/1.1
Content-Type: application/json
...

{
    "InstanceId" : "rds-mysql-h441603c68aaa1b7a",
    "Offset" : 0,
    "Limit" : 10,
    "DBStatus" : "Running",
}

正常返回示例

{
    "ResponseMetadata" : {
        "RequestId": "540CFF28-407A-40B5-B6A5-74Bxxxxxxxxx",
        "Action": "ListDatabases",
        "Version": "2018-01-01",
        "Service": "rds_mysql",
        "Region": "cn-beijing",
    },
    "Result": {
        "Total" : 1,
        "Datas" : [
            {
                "DBName" : "db1",
                "DBStatus" : "Running",
                "CharacterSetName": "utf8",
                "DBDesc": "db for log",
                "AccountNames": "user1,user2"
            }
        ]
    }
}

如果您有其他问题，欢迎您联系火山引擎技术支持服务