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
Define where the ‘File Location’ is for this type file
Add the Generate Command
Note the syntax will appear as this:
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.
Test the New Object
Note: Depending on your workflow you may need to advance to a proper Status in order to add objects.
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.
Please feel free to reach out on this topic if you have additional questions.