DX NetOps

Expand all | Collapse all

database credentials while granting SQL Adapter on Spectrum

  • 1.  database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 04:12 AM

    Im trying create SQL Adapters for BSI and was referring below link

    Related to SQL Adapter for CA Spectrum

    Got stuck at stage where im trying to configure MySQL in Spectrum in order to enable SQL queries from a BSI Web Server Machine, because by default this is only allowed from localhost.

    So while running below query on mysql spectrum database

    mysql>GRANT SELECT ON ddmdb.* TO root@<BSI adapter IP address> IDENTIFIED BY 'root';

     

    which root user (for spectrum ?) need to pass in above command.

    Pls suggest.



  • 2.  Re: database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 04:46 AM

    Yes it is.. Spectrum



  • 3.  Re: database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 04:49 AM

    Then what will be userid and password which i need to pass from above mentioned sql command



  • 4.  Re: database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 04:54 AM

    root root



  • 5.  Re: database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 04:56 AM

    "Spectrum" i meant on application front and not user

     

    root / root is the root credential for Spectrum Mysql



  • 6.  Re: database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 04:57 AM

    Did not worked. Pls check my reply where attached errors which i received



  • 7.  Re: database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 04:53 AM

    When i tried the below command received error

    mysql> GRANT ALL ON *.* to Administrator@<BSI Web Application IP Address> IDENTIFIED BY 'spectrum';

    ERROR 1045 (28000): Access denied for user 'srmapi'@'localhost' (using password

    YES)

    mysql> GRANT SELECT ON ddmdb.* TO root@1<BSI Web Application IP Address> IDENTIFIED BY 'root';

    ERROR 1044 (42000): Access denied for user 'srmapi'@'localhost' to database 'dd

    db'



  • 8.  Re: database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 05:17 AM

    Won't this tag be IP address indeed?

     

    <BSI Web Application IP Address>



  • 9.  Re: database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 05:21 AM

    I am passing BSI IP ADDRESS in the above tag.



  • 10.  Re: database credentials while granting SQL Adapter on Spectrum

    Broadcom Employee
    Posted Jul 21, 2016 03:35 AM

    .. this is because you did not logon to the mysql per "root" account having the full permission.



  • 11.  Re: database credentials while granting SQL Adapter on Spectrum

    Posted Jul 20, 2016 05:35 AM

    Can you kindly send a screenprint on how have you logged in to Mysql db(command)..

     

    And you are trying to run this command on SS (where DDMDB sits) machine right?



  • 12.  Re: database credentials while granting SQL Adapter on Spectrum
    Best Answer

    Broadcom Employee
    Posted Jul 20, 2016 08:44 AM

    Suppose this scenario:

    SpectroSERVER hostname: server1 (where the Archive Manager is running, which manages the ddmdb database)

    BSI hostname: server2

     

    If you try to connect from the server2 to the MySQL server, on server1, you should get this type of message:

    C:\> telnet server1 3306

    Host 'server2.ca.com' is not allowed to connect to this MySQL server

    Connection to host lost.

     

    You have to provide access to a remote machine/account to the MySQL server.

    On server1 (SpectroSERVER machine)

    a) Open a bash shell (bash -login)

    b) Navigate to the $SPECROOT/mysql/bin directory

    c) Run: ./mysql -uroot -proot (Windows)

    or Run: ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot (Linux/Solaris)

    d) Run:

    mysql> GRANT ALL PRIVILEGES ON ddmdb.* TO 'spectrum'@'server2.ca.com' IDENTIFIED BY 'spectrum';

    Query OK, 0 rows affected (0.17 sec)

    Where: spectrum is the account and spectrum is the password on the server2 machine you are logged in.

     

    Now the connection via telnet is successful from server2:

    C:\> telnet server1 3306

    5.5.39-enterprise-commercial-advanced-log#}vY;Y0"V!☻ǧk8rZMO(v>m.9mysql_native_password



  • 13.  Re: database credentials while granting SQL Adapter on Spectrum

    Broadcom Employee
    Posted Jul 21, 2016 03:40 AM

    I would suggest to make use of a username which clearly indicates the BSI-activity - like "bsiuser".

    background is, that any access to the Spectrum "ddmdb" should be limited and potentially the concurrent access to the "ddmdb" tables could affect the CA Spectrum ArchiveManager operation. In addition I would limit access (GRANT) to do a read-only across all tables - which then ensures the "ddmdb"-tables are not updated but reading only. This will assure no tables are locked (i.e. on error) by the "bsiuser" access/actions.

     

    Once it runs you can then i.e. log the "bsiuser" activity by mysql audit capabilities. Native check could by done by:

    ./mysql[.exe] --defaults-file=../my-spectrum.cnf -uroot -proot -e"show full processlist;"

                                                                                                                                             - Joerg -