AWSTemplateFormatVersion: '2010-09-09'
Metadata:
  License: oracle
Description: 'AWS CloudFormation Sample Template Oracle RDS_with_DBParameterGroup'
Parameters:

  Env:
    Description: Environment (Production/Dev)
    Type: String
    Default: dev
  VPCID:
    Type: String #'AWS::EC2::VPC::Id'
    Description: Select a VPC in which you want to create a RDS Instance 
    Default: vpc-745a620d
  DBSubnetGroup:
    Type: String #'List<AxWS::EC2::Subnet::Id>'
    Description: Select subnet
    Default: default
    Description: Select a SubnetGroup name in the same VPC in which you want to create a RDS Instance 
  CIDR:
    Description: The IP address range that can be used to access the RDS Instance 
    Type: String
    Default: 10.0.0.0/24
  DBName:
    Description: The  RDS ORACLE database name. Maximum 8 character permitted. The Name should be unique
    Type: String
    Default: Testdb04
    ConstraintDescription: Maximum 8 character permitted. The Name should be unique
  DBClass:
    Description: Database instance class
    Type: String
    Default: db.m5.4xlarge
    AllowedValues:
    - db.m4.large
    - db.m4.xlarge
    - db.m4.2xlarge
    - db.m4.4xlarge
    - db.m4.10xlarge
    - db.m5.4xlarge
    - db.r3.large
    - db.r3.xlarge
    - db.r3.2xlarge
    - db.r3.4xlarge
    - db.r3.8xlarge
    - db.t2.large
    - db.cr1.8xlarge
    - db.m3.medium
    - db.m3.large
    - db.m3.xlarge
    - db.m3.2xlarge
    ConstraintDescription: Must be a valid database instance type.
  DBAllocatedStorage:
    Description: The size of the database (Gb)
    Type: String
    Default: 100
  DBStorageType:
    Description: The Storage type (io1/gp2).
    Type: String
    Default: io1
  Iops:
    Description: Number of iops.
    Type: String
    Default: 1000
  Engine:
    Type: String
    Default: oracle-ee-cdb
  EngineVersion:
    Type: String
    Default: 19
  CharacterSetName:
    Description: Character Set
    Type: String
    Default: AL32UTF8
  DBPort:
    Description: Database port
    Type: String
    Default: 1521
  DBMonitoringInterval:
    Description: 'Interval to publish database logs to CloudWatch.'
    Type: String
    Default: 5
  BackupRetention:
    Description: backup retention in days, disabled for dev
    Type: Number
    Default: 1
  BackupWindow:
    Description: backup window in 01:00-03:00 format
    Type: String
    Default: 01:00-03:00
  MaintenanceWindow:
    Description: maintenance window in sun:06:00-sun:06:30 format
    Type: String
    Default: sun:06:00-sun:06:30

Conditions:
  IsMultiAZ:
   !Equals [!Ref Env, 'prd']
  DeleteProtection:
   !Equals [!Ref Env, 'prd']
  DBstorage:
   !Equals [!Ref DBStorageType, 'io1']

Resources:
  myKey: 
    Type: AWS::KMS::Key
    Properties: 
      Description: "An example CMK"
      KeyPolicy: 
        Version: "2012-10-17"
        Id: "key-default-1"
        Statement:
        -
          Sid: "Enable IAM User Permissions"
          Effect: "Allow"
          Principal: 
            AWS: !Sub "arn:aws:iam::${AWS::AccountId}:root"
          Action: "kms:*"
          Resource: "*"
        
  AliasAWSKey: 
    Type: 'AWS::KMS::Alias' 
    Properties: 
      AliasName: !Sub alias/${DBName}/${Env}AWSKey 
      TargetKeyId: !Ref myKey 

  MyRDSInstanceRotationSecret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Description: 'This is the secret for my RDS instance'
      GenerateSecretString:
        SecretStringTemplate: '{"username": "admin"}'
        GenerateStringKey: 'password'
        PasswordLength: 16
        ExcludeCharacters: '"@/\'

  RDSMonitoringRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
        - Sid: ''
          Effect: Allow
          Principal:
            Service: monitoring.rds.amazonaws.com
          Action: sts:AssumeRole
      ManagedPolicyArns:
      - arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole
      Path: "/"

  DBSecurityGroup:
    Type: "AWS::EC2::SecurityGroup"
    Properties:
      GroupDescription: Database server security group
      VpcId: !Ref VPCID
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: !Ref DBPort
          ToPort: !Ref DBPort
          CidrIp: !Ref CIDR

  DBParameterGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Description: Database parameter group for Database
      Family: oracle-ee-cdb-19
      Parameters:
        open_cursors: 3000
        sqlnetora.sqlnet.inbound_connect_timeout: 3600
        sqlnetora.tcp.connect_timeout: 3600
        sqlnetora.sqlnet.outbound_connect_timeout: 3600
        sqlnetora.sqlnet.recv_timeout: 3600
        sqlnetora.sqlnet.send_timeout: 3600

  DBOptionGroup:
    Type: AWS::RDS::OptionGroup
    Properties:
      OptionGroupDescription:  Option Group to enable NNE
      EngineName: !Ref Engine
      MajorEngineVersion: 19
      OptionConfigurations:
      - OptionName: NATIVE_NETWORK_ENCRYPTION
        OptionSettings:
        - Name: SQLNET.ENCRYPTION_TYPES_SERVER
          Value: AES128
        - Name: SQLNET.ENCRYPTION_SERVER
          Value: REQUIRED
      - OptionName: SQLT
        OptionVersion: 2018-07-25.v1
        OptionSettings:
        - Name: LICENSE_PACK
          Value: T
      - OptionName: UTL_MAIL


  DBInstance:
    Type: 'AWS::RDS::DBInstance'
    DeletionPolicy: Delete
    #  !If [DBDelete, Delete, Snapshot]
    Properties:
      DBName: !Ref DBName
      DeletionProtection: !If [DeleteProtection, true, false]
      PubliclyAccessible: false
      StorageEncrypted: true
      StorageType: !Ref DBStorageType
      Iops: !If [DBstorage, 1000,!Ref "AWS::NoValue"]
      Engine: !Ref Engine
      EngineVersion: !Ref EngineVersion
      MasterUsername: !Join ['', ['{{resolve:secretsmanager:', !Ref MyRDSInstanceRotationSecret, ':SecretString:username}}' ]]
      # MasterUserPassword: !Ref DBPassword
      DBInstanceClass: !Ref DBClass
      AllocatedStorage: !Ref DBAllocatedStorage
      MasterUserPassword: !Join ['', ['{{resolve:secretsmanager:', !Ref MyRDSInstanceRotationSecret, ':SecretString:password}}' ]]
      CharacterSetName: !Ref CharacterSetName
      #MultiAZ: true
      DBParameterGroupName: !Ref DBParameterGroup
      DBInstanceIdentifier: !Sub '${DBName}'
      #DBSnapshotIdentifier: !If [RDSSnapshotSpecified, !Ref RDSSnapshotId, !Ref 'AWS::NoValue']
      Port: !Ref DBPort
      VPCSecurityGroups:
        - !Ref DBSecurityGroup
      DBSubnetGroupName: !Ref DBSubnetGroup
      KmsKeyId: !Ref AliasAWSKey
      MonitoringRoleArn:
        !GetAtt
          - RDSMonitoringRole
          - Arn
      MonitoringInterval: !Ref DBMonitoringInterval
      EnableCloudwatchLogsExports:
      - trace
      - alert
      - audit
      - listener
      EnablePerformanceInsights: true
      PerformanceInsightsKMSKeyId: !Ref AliasAWSKey
      OptionGroupName: !Ref DBOptionGroup
      AllowMajorVersionUpgrade: false
      AutoMinorVersionUpgrade: true
      BackupRetentionPeriod: !Ref BackupRetention
      PreferredBackupWindow: !Ref BackupWindow
      PreferredMaintenanceWindow: !Ref MaintenanceWindow
      MultiAZ: !If [IsMultiAZ, true, false]
      CopyTagsToSnapshot: true
      Tags:
        - Key: Name
          Value: !Ref DBName
Outputs:
  DBInstanceCname:
    Value: !Ref DBName
  RDSInstanceId:
    Value: !Ref DBInstance
    Description: RDS Instance ID
  OracleDatabaseEndpoint:
    Value: !GetAtt DBInstance.Endpoint.Address
    Description: Oracle Database Endpoint Address
  OracleDatabasePort:
    Value: !Ref DBPort
    Description: Oracle Database Endpoint Port (SSL)
  OracleDBSecurityGroup:
    Value: !Ref DBSecurityGroup
