One issue I have heard customers ask me over the years is how can we do Grants with Stat. Grants are challenging as even when defined as a SQL script most Stat commands are executed as ‘APPS’ and grants cannot usually be executed via ‘APPS’. I was recently working with a customer and we came have a solution that involves creating a custom object type for grant scripts and then using the ‘System’ account to execute. The grant commands are then created by you and stored as a ‘.grant’ (or whatever extension you want) and then can be migrated and executed along with all other Oracle objects. In the following post I will discuss step-by-step how to add and execute Grants in Stat.
Note: You need to be on Stat 5.6.3 or higher to see all the functionality outlined below.
First step is to define a new object type for the type grant
- Within Stat Navigate to Maintenance/General/Object Types. This will open the Object Types box.
- Select ‘Oracle Apps File Objects’ from the ‘Object Class’ dropdown as seen below:
- Create a ‘New’ Object type as seen below. Name the file type, choose type as ‘Custom with Prod’ (therefore you will be prompted to select a Product like with other file objects), put a file extension (I did .grant but you could do anything like .gnt) and check For DB.
Note: If you check Database Script it will try to execute it as APPS, so in this instance do not check.
Define where the ‘File Location’ is for this type file
- Highlight the ‘Grants’ file type and select the ‘File Location’ button (as seen below)
- In the ‘Stat File Locations - Grants’ box select ’New’ to create a new line.
- You may choose any subfolder but in this example, I will choose ‘bin’ (as seen below).
- Select ‘Apply’ and close out of the Window.
Add the Generate Command
- Select the Templates/Generate box to open up the Generate command box. Navigate to the ‘Define Generate Command’ box.
- Navigate to the ‘Define Generate Command’ box.
- Type in ‘sqlplus’ and press return
- Type in ‘system/’ and then from the Predefined Parameter Names select ‘TARGET_SYSTEM_PASSWORD’ and then put an ‘@’ and then select ‘TARGET_DB_NAME’ and press return
- Type ‘@” and select ‘TARGET_OBJECT_NAME_FULL_PATH’ and hit return.
- You may select ‘Interpret Command’ (as I did above) to see the actual command.
Note the syntax will appear as this:
- Select ‘Apply’ to save your work.
Notes: Using the ‘Predefined Parameter Names’ will dynamically pass in the appropriate parameters as per the Target instance and therefore work for each environment. You can create individual commands, but unless the syntax is different there is no need.
Bounce all the Stat for Oracle Agents.
- There is no need to bounce the Central Stat Agent.
Test the New Object
- Open a new CSR
- Navigate to the OraApps tab and click on the Advanced button in the lower right corner
Note: Depending on your workflow you may need to advance to a proper Status in order to add objects.
- Generate a ‘Migration Path’
- Navigate to the ‘Objects’ tab
- From the Environment dropdown select the environment you put the file in, the working directory, or your 3rd party version control.
Note: You will either need to create the ‘.grant’ script you want to test and seed in a Dev environment, the working directory, or 3rd party version control system. I have placed mine in my custom_top/bin directory.
- From the ‘Object Type’ tab. You will now see ‘Grants’ in the list. Select ‘Grants’ which will prompt you to select a ‘Product.’
- You will now see the object you seeded into the environment
- Highlight and select the ‘Add Object’ to add to the CSR and check out.
- Save the CSR
- Navigate to the ‘Object Management’ tab
- Create a new ‘Archive Set’ as you would any other object
- Add the new object and select ‘OK’
- Migrate as you would any other ‘Archive Set’ and select ‘Next’
- After successful migration, you will see the ‘Migration Completed’ message.
- In order to see the Grant succeeded navigate to the ‘Process Request’ tab and highlight the ‘Migrate Object’ event and select ‘View Log’.
- You will see the Generate log, which will show as an FNDLOAD log.
- Select ‘View’ to see the log.
- You will see the grant succeeded!
Please feel free to reach out on this topic if you have additional questions.