Security Error: User is not authed

[rsurdikar]

Hi,

I am trying to add basic security in Atlas by referring to the following documentation:

https://github.com/OHDSI/WebAPI/wiki/Basic-Security-Configuration https://github.com/OHDSI/WebAPI/wiki/Atlas-Security

I am able to login but not able to access any atlas pages, Not sure what I have missed?

Sorry for lengthy description, giving details of what things I have observed:

Login Request:

Request URL: http://localhost:8080/WebAPI/user/login/db
Request Method: POST
Status Code: 200 

Form Data:

login: ohdsi
password: ohdsi

Request Headers:

Accept: */*
Accept-Encoding: gzip, deflate, br
Accept-Language: en-GB,en-US;q=0.9,en;q=0.8
Connection: keep-alive
Content-Length: 26
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
Cookie: __utmc=111872281; appConfig={}; __utma=111872281.770566818.1529795037.1550651638.1551078399.86; user-id=rohan|Fri%2C%2004%20Oct%202019%2004%3A35%3A25%20GMT|589B4f%2Br5Pb1SaPRu3DR4r6j92GdC5OFwKNWlknRL%2Bs%3D; csrf-token=d0916d60-23b4-4847-9eff-860864042a79; connect.sid=s%3ASpBKiiWdlc5z-sSJeZz47hXMxouuPKly.w8ploj7lb1POamMJaIKiSPClBJv%2FDYa%2FvD33AEHH%2Bf0; io=aJeaxqLKDjEAkiukAADX; port-token=58cf2f4f76df; org.springframework.web.servlet.i18n.CookieLocaleResolver.LOCALE=en_US; JSESSIONID=1a9e79gohjsp73eemilrxao6m; bearerToken=eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiJvaGRzaSIsImV4cCI6MTU3MDIzNzIyOX0.w5ySGL1_vWMZvO-SNC2JSrOo1R53pA7M_4CT3lHJXXDM0Xfv48iDxbKwx-N27sXIzJ8gvx7jp0DfmiZ1o1_ABQ
Host: localhost:8080
Origin: http://localhost:8080
Referer: http://localhost:8080/Atlas/
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36
X-Requested-With: XMLHttpRequest

Response Headers:

Access-Control-Allow-Credentials: true
Access-Control-Allow-Origin: *
Access-Control-Expose-Headers: Bearer,x-auth-error
Bearer: eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiJvaGRzaSIsImV4cCI6MTU3MDIzNzg3Mn0.84rHLhQ9K2v-L0vVzJy54C_vfsFafmwoS6eo7XMusq7s0-jR3mCQo5kK-3TT2d9g9YjCC6nJzOqdbuqoG3xeJA
Content-Length: 396
Content-Type: application/json;charset=UTF-8
Date: Fri, 04 Oct 2019 13:11:13 GMT
Set-Cookie: rememberMe=deleteMe; Path=/WebAPI; Max-Age=0; Expires=Thu, 03-Oct-2019 13:11:12 GMT

Response:

{
  "permissions": "feasibility:get|ir:*:copy:get|plp:*:delete|feasibility:*:get|featureextraction:*:get|job:get|executionservice:*:get|executionservice:execution:run:post|vocabulary:*:post|cohortanalysis:*:get|cohortresults:*:get|feasibility:*:delete|ir:*:delete|cohortanalysis:get|feasibility:*:put|vocabulary:*:search:*:get|evidence:*:get|*:person:*:get|cdmresults:*:get|plp:*:put|plp:*:copy:get"
}

Permission Request:

Request URL: http://localhost:8080/WebAPI/user/me
Request Method: GET
Status Code: 403 

Request Headers:

Accept: */*
Accept-Encoding: gzip, deflate, br
Accept-Language: en-GB,en-US;q=0.9,en;q=0.8
Authorization: Bearer eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiJvaGRzaSIsImV4cCI6MTU3MDIzNzg3Mn0.84rHLhQ9K2v-L0vVzJy54C_vfsFafmwoS6eo7XMusq7s0-jR3mCQo5kK-3TT2d9g9YjCC6nJzOqdbuqoG3xeJA
Connection: keep-alive
Cookie: __utmc=111872281; appConfig={}; __utma=111872281.770566818.1529795037.1550651638.1551078399.86; user-id=rohan|Fri%2C%2004%20Oct%202019%2004%3A35%3A25%20GMT|589B4f%2Br5Pb1SaPRu3DR4r6j92GdC5OFwKNWlknRL%2Bs%3D; csrf-token=d0916d60-23b4-4847-9eff-860864042a79; connect.sid=s%3ASpBKiiWdlc5z-sSJeZz47hXMxouuPKly.w8ploj7lb1POamMJaIKiSPClBJv%2FDYa%2FvD33AEHH%2Bf0; io=aJeaxqLKDjEAkiukAADX; port-token=58cf2f4f76df; org.springframework.web.servlet.i18n.CookieLocaleResolver.LOCALE=en_US; JSESSIONID=1a9e79gohjsp73eemilrxao6m; bearerToken=eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiJvaGRzaSIsImV4cCI6MTU3MDIzNzg3Mn0.84rHLhQ9K2v-L0vVzJy54C_vfsFafmwoS6eo7XMusq7s0-jR3mCQo5kK-3TT2d9g9YjCC6nJzOqdbuqoG3xeJA
Host: localhost:8080
Referer: http://localhost:8080/Atlas/
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36
X-Requested-With: XMLHttpRequest

Response Headers:

Date: Fri, 04 Oct 2019 13:11:13 GMT
Set-Cookie: rememberMe=deleteMe; Path=/WebAPI; Max-Age=0; Expires=Thu, 03-Oct-2019 13:11:13 GMT
Transfer-Encoding: chunked

Console error:

source of error AuthAPI.js:

const loadUserInfo = function() {
        return $.ajax({
            url: config.api.url + 'user/me',
            method: 'GET',
            success: function (info) {
                subject(info.login);
                permissions(info.permissions.map(p => p.permission));
            },
            error: function (err) {
                console.log('User is not authed');
                subject(null);
            }
        });
    };

I see my tomcat server logs mention following warnings and error:

2019-10-04 18:30:29.870 INFO http-nio-8080-exec-5 org.apache.shiro.realm.AuthorizingRealm -  - No cache or cacheManager properties have been set.  Authorization cache cannot be obtained.
2019-10-04 18:30:29.870 INFO http-nio-8080-exec-5 org.apache.shiro.realm.AuthorizingRealm -  - No cache or cacheManager properties have been set.  Authorization cache cannot be obtained.
2019-10-04 18:30:29.870 INFO http-nio-8080-exec-5 org.apache.shiro.realm.AuthorizingRealm -  - No cache or cacheManager properties have been set.  Authorization cache cannot be obtained.
2019-10-04 18:30:29.870 INFO http-nio-8080-exec-5 org.apache.shiro.realm.AuthorizingRealm -  - No cache or cacheManager properties have been set.  Authorization cache cannot be obtained.
2019-10-04 18:30:29.870 WARN http-nio-8080-exec-5 org.ohdsi.webapi.shiro.ADRealm -  - LDAP naming error while attempting to retrieve authorization for user [ohdsi].

Atlas config-local.js

define([], function () {
    var configLocal = {};

    configLocal.userAuthenticationEnabled = true;

    configLocal.api = {
        name: 'Local',
        url: 'http://localhost:8080/WebAPI/'
    };

    configLocal.authProviders = [{
        "name": "Local Security",
        "url": "user/login/db",
        "ajax": true,
        "icon": "fa fa-database",
        "isUseCredentialsForm": true
    }];

    return configLocal;
});

WebAPI settings.xml

<settings>
<profiles>
  <profile>
    <id>webapi-mssql</id>
    <properties>
      <datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</datasource.driverClassName>
      <datasource.url>jdbc:sqlserver://localhost:1433;databasename=synpuf52</datasource.url>
      <datasource.username>sa</datasource.username>
      <datasource.password>password</datasource.password>
      <datasource.dialect>sql server</datasource.dialect>
      <datasource.ohdsi.schema>webapi</datasource.ohdsi.schema>
      <flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
      <flyway.datasource.url>${datasource.url}</flyway.datasource.url>
      <flyway.datasource.username>sa</flyway.datasource.username>
      <flyway.datasource.password>password</flyway.datasource.password>
      <flyway.locations>classpath:db/migration/sqlserver</flyway.locations>

      <security.provider>AtlasRegularSecurity</security.provider>
      <security.enabled>true</security.enabled>
      <security.origin>*</security.origin>
      <security.maxLoginAttempts>3</security.maxLoginAttempts>
      <security.duration.initial>10</security.duration.initial>
      <security.duration.increment>10</security.duration.increment>
      <security.db.datasource.url>jdbc:sqlserver://localhost:1433;databasename=synpuf52</security.db.datasource.url>
      <security.db.datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</security.db.datasource.driverClassName>
      <security.db.datasource.schema>webapi</security.db.datasource.schema>
      <security.db.datasource.username>sa</security.db.datasource.username>
      <security.db.datasource.password>password</security.db.datasource.password>
      <security.db.datasource.authenticationQuery>select password from webapi.demo_security where email = ?</security.db.datasource.authenticationQuery>
     </properties> 
  </profile>  
</profiles>
</settings>

demo_security Table:

SEC_USER table:

SEC_ROLE table:

SEC_USER_ROLE table (Tried with every possible role):

http://localhost:8080/WebAPI/info {"version":"2.4.2"}

Please look into above issue and let me know if I have missed anything? Is it related to error 'LDAP naming error while attempting to retrieve authorization for user [ohdsi]'? why it's showing?

Thanks, Rohan

@ambuj369 @anthonysena @chrisknoll @ChavanShweta @ssuvorov-fls

[ambuj369]

Hello @rsurdikar ,

Please check the following comment on one of the similar issue I raised earlier.

https://github.com/OHDSI/WebAPI/issues/1099#issuecomment-505424385

You need to create a new schema within OHDSI database and store the credentials there first , ensure that passwords are bcrypted using bcrypt utility https://www.browserling.com/tools/bcrypt

Login to Atlas with the username and password after giving a user admin privileges role_id 2 in webapi.sec_user table.

[rsurdikar]

Thanks @ambuj369 for quick reply.

Currently I have created 'demo_security' table under 'webapi' schema. Let me try moving it to new schema.

Also, you have mentioned following changes in that comment, let me try those as well.

• In demo_security table I have used the column "username" instead of "email" because username is mentioned within the JDBCAuthRealm.Java code
• Update the settings.xml for authentication query section - select password,firstName,middleName,lastName from atlas_security.demo_security where email = ?

[rsurdikar]

@ambuj369 I did all the above suggested changes (separated security schema, updated settings.xml) but still I am facing the same issue.

REST API http://localhost:8080/WebAPI/user/me throwing 403 error.

Also, I observed that, REST API http://localhost:8080/WebAPI/user/login/db always returns me permissions related to 'public' role, even though I have assigned 'admin' role to my user.

Note: I tried to give all admin permission to 'public' role but still no luck.

Here is my DB screen-shot:

My settings.xml

<settings>
<profiles>
  <profile>
    <id>webapi-mssql</id>
    <properties>
      <datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</datasource.driverClassName>
      <datasource.url>jdbc:sqlserver://localhost:1433;databasename=synpuf52</datasource.url>
      <datasource.username>sa</datasource.username>
      <datasource.password>password</datasource.password>
      <datasource.dialect>sql server</datasource.dialect>
      <datasource.ohdsi.schema>webapi</datasource.ohdsi.schema>
      <flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
      <flyway.datasource.url>${datasource.url}</flyway.datasource.url>
      <flyway.datasource.username>sa</flyway.datasource.username>
      <flyway.datasource.password>password</flyway.datasource.password>
      <flyway.locations>classpath:db/migration/sqlserver</flyway.locations>

      <security.provider>AtlasRegularSecurity</security.provider>
      <security.enabled>true</security.enabled>
      <security.origin>*</security.origin>
      <security.ssl.enabled>false</security.ssl.enabled>
      <security.cors.enabled>true</security.cors.enabled>
      <security.maxLoginAttempts>3</security.maxLoginAttempts>
      <security.duration.initial>10</security.duration.initial>
      <security.duration.increment>10</security.duration.increment>
      <security.db.datasource.url>jdbc:sqlserver://localhost:1433;databasename=synpuf52</security.db.datasource.url>
      <security.db.datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</security.db.datasource.driverClassName>
      <security.db.datasource.schema>webapi</security.db.datasource.schema>
      <security.db.datasource.username>sa</security.db.datasource.username>
      <security.db.datasource.password>password</security.db.datasource.password>
      <security.db.datasource.authenticationQuery>select password,firstname,middlename,lastname from security.atlas_security.demo_security where username = ?</security.db.datasource.authenticationQuery>
     </properties> 
  </profile>  
</profiles>
</settings>

[ambuj369]

@rsurdikar

1. You are on SQL , do not alter webapi database. (delete any extra tables you added)
2. Create a new database for security and a table within to store credentials and use that database name here. jdbc:sqlserver://localhost:1433;databasename=synpuf52
3. Change the schema from webapi to the new database created in step 2 webapi
4. Enter username and password for that schema below sa password Recompile webapi , deploy and test.

[rsurdikar]

Thanks @ambuj369 for the comments.

My role issue is resolved now, It's picking up permissions correctly based on the given role but still REST API http://localhost:8080/WebAPI/user/me throws 403 error.

My updated settings.xml

<settings>
<profiles>
  <profile>
    <id>webapi-mssql</id>
    <properties>
      <datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</datasource.driverClassName>
      <datasource.url>jdbc:sqlserver://localhost:1433;databasename=synpuf52</datasource.url>
      <datasource.username>sa</datasource.username>
      <datasource.password>password</datasource.password>
      <datasource.dialect>sql server</datasource.dialect>
      <datasource.ohdsi.schema>webapi</datasource.ohdsi.schema>
      <flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
      <flyway.datasource.url>${datasource.url}</flyway.datasource.url>
      <flyway.datasource.username>sa</flyway.datasource.username>
      <flyway.datasource.password>password</flyway.datasource.password>
      <flyway.locations>classpath:db/migration/sqlserver</flyway.locations>

      <security.provider>AtlasRegularSecurity</security.provider>
      <security.enabled>true</security.enabled>
      <security.origin>*</security.origin>
      <security.ssl.enabled>false</security.ssl.enabled>
      <security.cors.enabled>true</security.cors.enabled>
      <security.maxLoginAttempts>3</security.maxLoginAttempts>
      <security.duration.initial>10</security.duration.initial>
      <security.duration.increment>10</security.duration.increment>
      <security.db.datasource.url>jdbc:sqlserver://localhost:1433;databasename=security</security.db.datasource.url>
      <security.db.datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</security.db.datasource.driverClassName>
      <security.db.datasource.schema>atlas_security</security.db.datasource.schema>
      <security.db.datasource.username>sa</security.db.datasource.username>
      <security.db.datasource.password>password</security.db.datasource.password>
      <security.db.datasource.authenticationQuery>select password,firstname,middlename,lastname from atlas_security.demo_security where username = ?</security.db.datasource.authenticationQuery>
     </properties> 
  </profile>  
</profiles>
</settings>

[ambuj369]

@rsurdikar Please try to login with the credentials you've stored in security database.

[rsurdikar]

@ambuj369

yes, I have used same credentials i.e. sa/password from security database and also, I see REST API http://localhost:8080/WebAPI/user/login/db is returning 200 response.

But on tomcat console, I see following logs.

2019-10-09 16:02:56.054 INFO http-nio-8080-exec-1 org.apache.shiro.realm.AuthorizingRealm -  - No cache or cacheManager properties have been set.  Authorization cache cannot be obtained.
2019-10-09 16:02:56.054 INFO http-nio-8080-exec-1 org.apache.shiro.realm.AuthorizingRealm -  - No cache or cacheManager properties have been set.  Authorization cache cannot be obtained.
2019-10-09 16:02:56.054 INFO http-nio-8080-exec-1 org.apache.shiro.realm.AuthorizingRealm -  - No cache or cacheManager properties have been set.  Authorization cache cannot be obtained.
2019-10-09 16:02:56.054 INFO http-nio-8080-exec-1 org.apache.shiro.realm.AuthorizingRealm -  - No cache or cacheManager properties have been set.  Authorization cache cannot be obtained.
2019-10-09 16:02:56.054 WARN http-nio-8080-exec-1 org.ohdsi.webapi.shiro.ADRealm -  - LDAP naming error while attempting to retrieve authorization for user [sa].

Not sure, If above error is related ?

[ambuj369]

@rsurdikar from the first comment, the screenshot says the username you added is "ohdsi", please use that login name with its respective password.

[rsurdikar]

@ambuj369

yes, you are right previously, I used 'ohdsi/ohdsi' credential but now, I changed to 'sa/password' (thinking it should be same as that of database user/password).

Also, If login credential is the issue then REST /user/login/db would have failed, but it passes.

[ambuj369]

@rsurdikar Good to hear that. We need to mention security DB password in settings.xml so that webapi could get access to the security database and its tables. However, the credentials entered in security database, is the key to Atlas.

[rsurdikar]

Thanks @ambuj369 for the help.

Sorry, but REST API http://localhost:8080/WebAPI/user/me still throws 403 error, I guess I need to debug webapi source code to understand what's exactly happening.

I will post my findings.

[ChavanShweta]

@rsurdikar Yes Basic security still has this issue. I am using my old findings to fix this issue.

Also just FYI, bcrypt link that in mentioned in the wiki is not working for me any more I tried other links and WebAPI is not able to verify the hash refer #1350. I am not sure which bcrypt algorithm was used in the link that got deprecated.

[rsurdikar]

Thanks @ChavanShweta for the reply. I guess, I need to customise source code for now to fix this issue.

[deebowden]

Thanks @ambuj369 for the help.

Sorry, but REST API http://localhost:8080/WebAPI/user/me still throws 403 error, I guess I need to debug webapi source code to understand what's exactly happening.

I will post my findings.

FWIW, I found the following SQL statement resolved the 403 error for me.

  DECLARE @next_sec_role_permission_id INT, @permission_user_me_get INT
  SELECT @next_sec_role_permission_id=max(id)+1 
  FROM [TrioOMOP].[ohdsi].[SEC_ROLE_PERMISSION]
  SELECT @permission_user_me_get=id FROM  [TrioOMOP].[ohdsi].[SEC_PERMISSION] WHERE [value]='user:me:get'
  -- add user:me:get permissions to admin user role
  INSERT INTO [TrioOMOP].[ohdsi].[SEC_ROLE_PERMISSION] (id,[ROLE_ID],[PERMISSION_ID])
  VALUES (@next_sec_role_permission_id,2,@permission_user_me_get)

[anthonysena]

Wiki updated to reflect this information.

[shailender1984]

@anthonysena could you please help to fix below ldap/AD issue. https://forums.ohdsi.org/t/ldap-authentication-is-not-working/14954

[shailender1984]

root@:~/OHDSI/Broadsea/postgresql# cat docker-compose.yml version: '2'

services:

broadsea-methods-library: image: ohdsi/broadsea-methodslibrary ports:

• "8787:8787"
• "6311:6311" environment:

• PASSWORD=mypass

  broadsea-webtools: image: ohdsi/broadsea-webtools ports:

• "443:8443" volumes:
  • .:/tmp/drivers/:ro
  • ./config-local.js:/usr/local/tomcat/webapps/atlas/js/config-local.js:ro
  • ./cacerts:/usr/local/openjdk-8/jre/bin/cacerts:rw
  • ./create_keystore.sh:/usr/local/openjdk-8/jre/bin/create_keystore.sh:rw
  • ./atlas-keystore.jks:/usr/local/openjdk-8/sample/jmx/jmx-scandir/atlas-keystore.jks:rw
  • ./server.xml:/usr/local/tomcat/conf/server.xml:rw environment:
• WEBAPI_URL=https://10.175.158.228:443
• env=webapi-postgresql
• security_enabled=true
• security_cors_enabled=true
• security_origin=*
• datasource_driverClassName=org.postgresql.Driver
• datasource_url=jdbc:postgresql://10.175.158.228:5432/OHDSI
• datasource_ohdsi_schema=ohdsi
• datasource_username=ohdsi_app_user
• datasource_password=3547647f10f7484ad5a7003ffbb95570
• spring_jpa_properties_hibernate_default_schema=ohdsi
• spring_jpa_properties_hibernate_dialect=org.hibernate.dialect.PostgreSQLDialect
• spring_batch_repositorytableprefix=ohdsi.BATCH
• flyway_datasource_driverClassName=org.postgresql.Driver
• flyway_datasource_url=jdbc:postgresql://10.175.158.228:5432/OHDSI
• flyway_schemas=ohdsi
• flyway_placeholders_ohdsiSchema=ohdsi
• flyway_datasource_username=ohdsi_admin_user
• flyway_datasource_password=3547647f10f7484ad5a7003ffbb95570
• flyway_locations=classpath:db/migration/postgresql
• security_provider=AtlasRegularSecurity
• security_maxLoginAttempts=3
• security_duration_initial=10
• security_duration_increment=10
• security_db_datasource_url=jdbc:postgresql://10.175.158.228:5432/Security
• security_db_datasource_driverClassName=org.postgresql.Driver
• security_db_datasource_schema=atlas_security
• security_db_datasource_username=ohdsi_app_user
• security_db_datasource_password=3547647f10f7484ad5a7003ffbb95570
• security_db_datasource_authenticationQuery=select password from atlas_security.demo_security where username = ?
• security_ssl_enabled=true
• server_port=443
• security_auth_ad_enabled=true
• security.ad.url=ldaps://xxxxxxxxxxxx:636
• security.ad_basedn='ou=internal,ou=Users,dc=science,dc=roche,dc=com'
• security.ad.principalSuffix=@science.roche.com
• security.ad.system.username=CN=SVC-phcaa-T1,OU=ServiceAccounts,DC=science,DC=roche,DC=com
• security.ad.system.password=#########

• security_ad_searchFilter="(&(objectClass=person)(uid=*))" root@:~/OHDSI/Broadsea/postgresql# cat config-local.js define([], function () { var configLocal = {};

  // clearing local storage otherwise source cache will obscure the override settings
  localStorage.clear();
  
  var getUrl = window.location;
  var baseUrl = getUrl.protocol + "//" + getUrl.host;
  
  // WebAPI
  configLocal.api = {
          name: 'OHDSI',
          url: 'https://10.175.158.228:443/WebAPI/'
  };
  
  configLocal.cohortComparisonResultsEnabled = false;
  configLocal.userAuthenticationEnabled = true;
  configLocal.plpResultsEnabled = false;
  
  configLocal.authProviders = [{
                  "name": "Local Security Test DB",
                  "url": "user/login/db",
                  "ajax": true,
                  "icon": "fa fa-database",
                  "isUseCredentialsForm": true
                },
               {
                  "name": "AD",
                  "url": "user/login/ad",
                  "ajax": true,
                  "icon": "fa fa-cubes",
                  "isUseCredentialsForm": true
                }];
  return configLocal;

  }); root@:~/OHDSI/Broadsea/postgresql#