Set default username for SQL client connections

[niveathika]

Description:
Currently, the default behaviour of the client is to connect to the databases without any username attached. Should this be changed so that the default user of each database should be used for the connection.

For instance MySQL its root, oracle its XA

This needs to be looked into since usually the default user can have higher privileges.

[kaneeldias]

Proposal: Define default usernames for client connections

Summary

Define default usernames to be used on client initialization in the Ballerina SQL connector modules.

History

The 1.3.x versions and below of the SQL connector modules defaulted to connecting to the database without a username
attached (i.e. an empty string).

Goals

• Define default usernames to be used on client initialization for the following SQL connector modules.
  • MySQL
  • PostgreSQL
  • MSSQL
  • OracleDB

Motivation

The ability to connect to common databases with default credentials (as opposed to manually defining) would make the
developer experience much more quick, simple and user-friendly, especially in testing scenarios.

Description

Modify the client initialization method in all the current SQL connector modules to use a default value for the username
instead of defaulting to ().

For example, in MySQL (default username is root) the client initialization method signature would be changed as follows.

public isolated function init(
    string host = "localhost", string? user = "root", string? password = (), string? database = (), int port = 3306, 
    Options? options = (), sql:ConnectionPool? connectionPool = ()
) returns sql:Error? 

The default usernames used for each module would be as follows.

Module	Default username
MySQL	root [1]
PostgreSQL	postgres [2]
MSSQL	sa [3]
OracleDB	sys [4]

References

[1] https://dev.mysql.com/doc/refman/8.0/en/default-privileges.html
[2] https://www3.ntu.edu.sg/home/ehchua/programming/sql/PostgreSQL_GetStarted.html
[3] https://quick-adviser.com/what-is-the-default-password-of-sa-in-sql-server
[4] https://docs.oracle.com/database/121/ADMQS/GUID-CF1CD853-AF15-41EC-BC80-61918C73FDB5.htm#ADMQS12003

[daneshk]

Removing optionality from the user param will be a breaking change as we are changing the type of the param. Shall we pass like string? user = "root" here. WDYT?

user param type remains the same, we only pass default user instead of nil

[kaneeldias]

Removing optionality from the user param will be a breaking change as we are changing the type of the param. Shall we pass like string? user = "root" here. WDYT?

user param type remains the same, we only pass default user instead of nil

@daneshk +1 updated the proposal

[daneshk]

Looks good to me. @niveathika please check

[niveathika]

LGTM.

@kaneeldias From next proposal let's open a new issue for the proposal. As the improvement issue and the proposal has different life cycle.