How To: Generate Auto Permissions or Synonyms for Oracle Using a Custom Script
Overview
There may be situations where the built-in Auto Permission and Auto Synonym rules for Oracle cannot handle the complex logic required by an application. For example, the requirement may be to grant SELECT for all tables beginning with a certain prefix. This functionality can be accomplished by creating a custom script that will generate the grants according to the requirements. Then create a changeset that is executed at the end of every deployment to run the script.
Step 1 – Create a Script to Perform Grant Operations
Create a script that will generate the desired grant or synonym statements. The script can utilize change log properties (https://datical-cs.atlassian.net/wiki/spaces/DDOC/pages/896569871 ) to customize values for different steps in the pipeline. The following is an example of a script to grant privileges to an Application User for all tables in a specified schema that are named “ABC_%”. The criteria for APPUSER will be specified by properties from the project changelog.
Save the script to a location outside the SQL SCM for the Datical Project since we will manually add the changeset to the changelog.
declare
v_statement varchar2(512);
errbuf varchar2(2400);
cursor grants is
select o.owner, o.object_name
from dba_objects o
where o.owner = '${TAB_OWNER}'
and o.object_type = 'TABLE'
and object_name like 'ABC_%'
minus
select owner, table_name /* exclude objects that have grants */
from dba_tab_privs
where grantee = '${APPUSER}'
and owner = '${TAB_OWNER}'
and privilege='SELECT';
begin
-- Create Grants
for obj in grants loop
v_statement := 'grant select on '||obj.owner || '.' ||obj.object_name ||' to ${APPUSER}';
begin
execute immediate v_statement;
exception
when others then
errbuf := sqlerrm;
dbms_output.put_line('Error occurred during grant');
dbms_output.put_line(v_statement);
dbms_output.put_line(errbuf);
end;
end loop;
end;
/
Step 2 – Create Changelog Properties for Replacement Variables
If the script requires different values based upon context or labels, create the required properties. Go the Project Settings in the Datical GUI and select Manage Properties.
Add the properties to be used by the script along with the Context or Label where the property value should be applied. Within the script specify the Property Key using the syntax ${property_key}.
Step 3 – Add the ChangeSet to the ChangeLog
From the Datical GUI, Select “Add a Change Set”.
Scroll down and select the “Execute a SQL script file using JDBC”/“Custom SQL (External File)” change type. Note that “Execute a SQL script file using JDBC” is the newer name in versions 7.9 or higher, and “Custom SQL (External File)” is the older name for the same change type in versions 7.8 or lower.
Browse to select the file. Deselect Split Statements.
On the next screen select Finalize.
NOTE: If your script contains statements that are specific to sqlplus, it may be better to use the “Execute with SQL Plus” change set type, instead of the “Execute a SQL script file using JDBC”/“Custom SQL (External File)” change set type.
Next on the Create Change Set screen:
Enter the ChangeSet ID (any text string) and the Author.
Set the Execution Settings to:
“Redeploy this Change Set every time the contents are modified” – Allows modification of the SQL file saved to the resources directory. Without this there will be “checksum” errors if the file is modified.
“Deploy this Change Set every time a deployment is performed” – Changeset will be run for every Deployment.
Set Deploy Order to “After Other Change Sets”.
Click Finish.
Add, Commit, and Push the changes to the SCM.
Step 5 – Test the New Change Set
Package and deploy a simple “CREATE TABLE” script. Verify that the grants are applied to the new table.
Copyright © Datical 2012-2020 - Proprietary and Confidential