X

Oracle Customer Engineering & Advocacy Lab (CEAL) Blog covers Oracle Analytics Cloud, Oracle Analytics Server and...

BI Check with WLST - SQL Datasources part 2

This second post continues with the BIEE security checks using WLST and python. This time, the following information is gathered about BISQLGroupProvider:

*  looking for SQLGroupProvider in the configured providers
*  retrieving the SQL queries
*  retrieving the JDBC source details
*  testing the connection pool, for each server that uses it (make sure your BI server is up and running)
*  testing the SQL queries directly against the database, with the test.user and test.group provided in the automation.properties file

To run the script:

- Download the files there: //cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/f4a5b21d-66fa-4885-92bf-c4e81c06d916/File/7e5a142d4c03d650a0593804455afe0d/bicheckwithwlst_part2.zip

- Edit automation.properties and change the parameters.

- In a shell, set ORACLE_HOME and then launch %ORACLE_HOME%\oracle_common\common\bin\wlst.cmd (or .sh) SGPtesting.py

 The output looks like this:


*** Reading properties file
    WLPassword=password
    datasource.database.password=password
    test.user=User12
    test.group=Group1
    test.nbgroups=10
    WLUrl=t3://server.us.oracle.com:7001
    WLUsername=weblogic
    datasource.database.user=MY

*** Connecting
Connecting to t3://server.us.oracle.com:7001 with userid weblogic ...
Successfully connected to Admin Server "AdminServer" that belongs to domain "bifoundation_domain".

Warning: An insecure protocol was used to connect to the
server. To ensure on-the-wire security, the SSL port or
Admin port should be used instead.

WebLogic Server 10.3.5.0  Fri Apr 1 20:20:06 PDT 2011 1398638

*** Reading SQLGroupProvider settings
    SQLGroupProvider is configured

*** SQL queries
    GetGroupDescription: SELECT GROUP_DESCRIPTION FROM MY.GROUPS WHERE GROUP_NAME = ?
    GroupExists: SELECT GROUP_NAME FROM MY.GROUPS WHERE GROUP_NAME = ?
    IsMember: SELECT USER FROM MY.GROUPMEMBERS WHERE MEMBER_OF = ? AND USER = ?
    ListGroups: SELECT GROUP_NAME FROM MY.GROUPS WHERE GROUP_NAME LIKE ?
    ListMemberGroups: SELECT MEMBER_OF FROM MY.GROUPMEMBERS WHERE USER = ?

*** JDBC Resource details
    BIDatabaseGroupDS
    jdbc/BIDatabaseGroupDS
    jdbc:oracle:thin:@dbserver.us.oracle.com:1521:orcl
    oracle.jdbc.OracleDriver

*** Connection pool test

    server : AdminServer
    dataSource : BIDatabaseGroupDS
    DatabaseProductVersion : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
    State : Running
    Pool test: OK

*** Testing SQL queries

SQLGetGroupDescription Group1
('Group One',)

SQLGroupExists Group1
('Group1',)

SQLIsMember Group1 User12
('User12',)

SQLListGroups first 10
('Group1',)
('Group2',)
('Group3',)

SQLListMemberGroups User12
('Group1',)
('Group2',)

Ending the script


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.