Database authentication in CORAL and COOL

1. Database authentication in CORAL and COOL Giacomo Govi CERN IT/PSS On behalf of the CORAL team

2. CORAL status A new release has been published: 1_7_0 Highlights: • New IConnection interface split from ISession. Allows more sessions to share the same physical connection (when the back-end supports it: es. Oracle). Users based on the ISessionProxy are only marginally affected by this change. • New system test exercising CORAL in a multithreaded environment corresponding to some experiment use cases. • Several minor new features and bug fixes

3. Authentication for the CORAL supported DB’s DB Authentication is part of the connection protocol for most of the native DB connectivity libraries: • Username/password credential passed explicitly • Hard-coded in the application code • Possibly transmitted in clear over the network • Few other options: • Kerberos: • supported by Oracle • complicate to integrated in the existing Kerberos-based realm (AFS) • not applicable to the main use case • Certificates: • X509 supported by Oracle and MySQL • Proxy certificates (used for the Grid) not supported

4. Architecture context User Application DB user Schema SELF-CONTAINED User Role Application DB user Schema User Role Application DB user Schema User TWO extreme models: 1) Application deployed in a dedicate computing infrastructure SINGLE USER DB SERVER 2) Applications running in a shared, distributed comp. infrastructure SHARED DISTRIBUTED DB SERVER MULTI-USER

5. Architecture context (cont.) Case 1) Deployment not critical: connection and authentication parameters in the application configuration. File based database catalogue + credential store acceptable. Case 2) Large scale deployment critical: connection and authentication parameters have to be propagated with each jobs running in a remote cpu. File based database and credential store ruled out, no minimal security. Mapping between DB user and roles required. Real use cases are less ‘extreme’: • Online applications, application-server based applications are close to model 1) • Offline, applications running on the Grid are close to model 2) • Aim: satisfy case 2) with some acceptable level of security

6. Using LFC as a backend Strategy: use the LCG File Catalogue Advantage: • No extra deployment required • Experiments may already depends on it LFC has a built-in Access Permission Mechanism • Authentication is based on the grid certificates • File access permission similar to standard file system • Role (group) based • ACL based Database-specific entries can be added to the catalogue • Mapping logical connection strings to physical database replicas • Logical connection string + Role >> LFN • Replica Connect. String >> PFN • Authentication Credentials are attached as PFN metadata • A role granted to the user will allow to access a specific file associated to the logical connection, providing replicas and their authentication parameters

7. Storing DB replicas in LFC LFC LFN LFC PFN Coral Role Coral Role Logical Connection String (LCS) Coral Role Replica 0 Physical Connection String (PCS)+ DB username+password For each File-role Replica … Physical Connection String (PCS)+ DB username+password Folder in LFC Name server [LCS] File in LFC [LCS]/[Role] File in LFC [LCS]/[Role] File in LFC [LCS]/[Role] [GUID] Replica N Physical Connection String (PCS)+ DB username+password Example: LCS = “/Atlas/ECAL_calib/” Role = “calib_reader” LFC file = /database/Atlas/ECAL_calib/calib_reader Replica PCS= oracle:/atlas_ecal_calib/calib_writer Replica username=calib_reader password=xxxxxx

8. Integrating LFC Security Folder in LFC Name server [LCS] File in LFC [LCS]/[Role] Owned (R/W/X Access granted) by VO DB/application administrators (VO-Admin) Aim: - define replica sets - maintain passwords Owned by VO-Admin R-only access granted to specific LFC groups mapping VOMS roles Implemented with LFC ACL’s • Command-Line tool available for VO-Admins: • coral_replica_manager –add –l lcs –r role –h host –c pcs –u username –p passowrd • coral_replica_manager –set_perm –g groupname –l lcs –r role [permission] • More commands are available for: • List available replicas • Delete specific replicas • Set and validate replicas password • Set replica status

9. Mapping DB params to LFC fields • Table Cns_file_replica (LFC PFN’s)

10. Integrating in CORAL • A new component: LFCReplicaService • Implements ILookupService and IAuthenticationService interfaces • Replica information (PCS + credentials) is retrieved in one go. • Replica looking up from logical connection string also retrieves authentication parameters • Authententication cannot be used without Lookup • Usage through coral::ConnectionService completely transparent • Only the loading of LFCReplicaService has to be configured: • Env variable LFC_HOST (not CORAL specific) has to be set • No other change to user code required • Files dblookup.xml and authentication.xml no longer required

11. Deployment Enable an LFC server to be used as a CORAL lookup+authentication service • A site-related LFC super user has to create the /database folder in the LFC root (to keep separate the logical files mapped to database connections from logical files mapped to real files) • In /database, a specific folder can be created for each VO (es. /database/atlas, /database/lhcb) • An experiment specific LFC super user has to be entitled with full permission on the related folder Insert the replica entries: • Run the command: coral_replica_manager –add –l lcs –r role –h host –c pcs –u username –p password Set the access permission to the replica for the different roles: coral_replica_manager –set_perm –g groupname –l lcs –r role [permission]

12. Still to finalize… • Ordering of replicas • Current implementation: the original insertion order is maintained • For site-specific ordering, a client callback mechanism could be introduced. • Still to ensure: is the required information to implement specific algorithm available? • The HOST parameter in the PFN record is currently not available for defining the “site” • Coral role-based authentication • In coral authentication role name has to be explicitly passed as a parameter when related credentials are requested • Role is implicitly defined in the VOMS schema • Coral default role will be mapped to the first valid role file accessible (unique in most of the use cases) • What else is missing? • Tests with real use pattern have to start!

13. Summary The CORAL team has developed a new component addressing the database authentication and connection string indirection for the applications running in the Grid environment • The LFC catalogue is used as a repository for the replica information • The access to the authentication parameters is protected by the Grid-certificates based access permission system of the LFC. Some benefit of this choice: • LFC widely deployed in the Grid – no changes is required for the storage of db connection strings • Little operation required for deployment The software has been released and is ready for real-use testing.