Idea ID: 2829894

Create mechanism to separate DB user used by SM to connect to DB and DB schema owner

Status : Waiting for Votes
Waiting for Votes
See status update history
8 months ago

Customer is looking for mechanism to separate DB user used by SM to connect to DB and DB schema owner where SM data is stored. In Oracle it is possible to achieve it using proxy user. It allows to connect with user SMAPP and define schema SMPRIMARY as if we ran alter session set current_schema=SMPRIMARY.

User can be created with the script:

CREATE USER SMAPP

  IDENTIFIED BY <password>

  DEFAULT TABLESPACE SM_DATA

  TEMPORARY TABLESPACE TEMP

  PROFILE TECHNO_PROFILE_PCIDSS

  ACCOUNT UNLOCK;

 

-- 2 Roles for SMAPP

GRANT CONNECT TO SMAPP;

GRANT DBA TO SMAPP;

ALTER USER SMAPP DEFAULT ROLE ALL;

 

-- 3 System Privileges for SMAPP

GRANT SELECT ANY DICTIONARY TO SMAPP;

GRANT SELECT ANY TABLE TO SMAPP;

GRANT UNLIMITED TABLESPACE TO SMAPP;

 

-- 4 Tablespace Quotas for SMAPP

ALTER USER SMAPP QUOTA UNLIMITED ON SM_DATA;

ALTER USER SMAPP QUOTA UNLIMITED ON SM_DATA_INDX;

ALTER USER SMAPP QUOTA UNLIMITED ON USERS;

ALTER USER SMAPP QUOTA UNLIMITED ON USERS_NEW;

 

-- 5 Proxy for SMPRIMARY

ALTER USER SMPRIMARY

  GRANT CONNECT THROUGH SMAPP;

 

In sqlplus such connection string works fine:

>sqlplus SMAPP[SMPRIMARY]/password@SMTCPSI

SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 29 14:43:32 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Sep 29 2020 14:43:31 +03:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> select incident_id from incidentsm1 where rownum=1;

 

INCIDENT_ID

--------------------------------------------------------------------------------

SD0121171314

 

SQL>

However if we'll try to add same connection string to SM:

sm -encrypt:"sqllogin:SMAPP[SMPRIMARY]/Pa$$w0rd_Pa$$w0rd" we'll get error

RTE E Failure connecting to dbtype [oracle10], using Oracle TNS 'SMPSI2', user ''

The problem here is currently what SM support is the basic sql function, which is different from sql plus client.

So it is possible that the operation done by sqlplus can't be accomplished by SM.

We are looking for alternative or enhancement in SM either to allow such connection string or an option to separate DB credentials for SM and DB schema owner

Tags: