问题描述
如何使用非交互的方式给已存在的某个用户授权所有数据库的权限
解决方案
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。
### 示例
请求示例
````undefined
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"
}
```
`
正常返回示例
````undefined
{
"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非法。 |
#### 示例
请求示例
````undefined
POST /?Action=ListDatabases&Version=2018-01-01 HTTP/1.1
Content-Type: application/json
...
{
"InstanceId" : "rds-mysql-h441603c68aaa1b7a",
"Offset" : 0,
"Limit" : 10,
"DBStatus" : "Running",
}
```
`
正常返回示例
````undefined
{
"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"
}
]
}
}
```
`
**如果您有其他问题，欢迎您联系火山引擎**[技术支持服务](https://console.volcengine.com/ticket/createTicketV2/)
