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
Parents
  • 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

Comment
  • 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

Children
No Data