Under Review
over 7 years ago

Enhancement request to allow Generation Schema selection to actually connect to the database

We have a lot of database objects that are owned by different schemas, especially for the EBS application. Having STAT use only one account to connect to the database makes it difficult to maintain the security of different application schemas by having to do extras grants to the account used by STAT. If we have the ability to select the generation schema to have the object created in then it should also connect to the database with that generation schema so we can apply grants as needed at the same time the new database object is created.  The way STAT is setup now is we have to split out our grants statements into a separate file and have the DBAs manually log into the database to execute the grants. Or just use STAT to copy the sql script and then have the DBA log into the database and manually execute the script in the correct schema to own the database object to create it and do the grants in the same script file. Not having the ability to connect to the database using different schema account reduces the concept of automation for STAT.

branchbj
  • over 7 years ago

    I presume you are using the APPS user in Stat.  This is common.  One thing to keep in mind, the Apps user password is stored encrypted in Stat.  Consequently, developers and DBAs do not need the password thereby mitigating the common risks associated with the APPS user.  Tom's solution to use a custom Stat EBS file objects is a good solution and has been adopted by other Stat users.  If, for some reason, this is not suitable, you may want to consider using Stat for Generic applications.  You would define the custom schema as a custom application.  In the application connection setup you would use the schema user and password for database connections.  This would require Stat for Generic application server license and would involve additional setup (Application, workflow, migration path, etc..)

  • over 7 years ago

    Hi...  

    Realize that the EBS Apps database user has the Security Role of DBA, so what that enable us to do is create objects in other DB schemas such as AR or Custom EBS schemas.   The DDL would be "create table AR.CUSTOM_TABLE ....."  The table will be owned by AR.  We do it all of time in our Custom EBS schemas. You can handle most grants in the same script.   You do not need to grant Apps any privileges since it has the DBA role.

    create table AR.CUSTOM_TABLE....  ;

    grant select on AR.CUSTOM_TABLE to Developer_Role ;

    One challenge with grants is with views that need to have the "WITH GRANT OPTION".

    I automated that with a Custom Stat EBS file object that connects to the database as system and executes the grant statement as system.  Search this site for my solution.

    Regards.

    Tom