Datical DB provides mechanisms for automatically manage object permissions and synonyms in Oracle database.

Auto Generate Permissions for Oracle

Oracle doesn't support granting permissions from one schema to another schema.

Instead, permissions are granted on an object-by-object basis to a role or schema. While this provides significant granularity for permissions, it is yet another manual step that must be remembered as database changes are developed in support of application development. Each time a new object is added, the appropriate permissions have to be granted to any other schema that needs access to the new objects.

Datical DB includes a specialized change type that allows you to define schemes to automatically manage object permissions. This removes the manual aspects of privilege management while ensuring that the mandated permission scheme is adhered to with every Datical DB deployment. Auto Generated Permissions allows you to:

• Specify a permission scheme based on types of objects or specific instances of object types. For instance, "All new tables created could have READ/WRITE access for the application user."
• Specify exceptions to permission schemes for specific objects for cases where an individual table, view or other object should be handled differently.
• Enforce revoking of permissions that were applied outside of the deployment process and don't fit into your defined permission schemes. This capability can provide a defense against threats of unauthorized access and resulting down time.
• Validate the current permissions configuration of a database every time you deploy to that database.  Each deployment reports on and remediates any out of process permission changes that violate the defined schemes.

Including or Excluding Individual Objects

To include or exclude individual objects for a rule, you need to specify the object using the following syntax:

<object type>:<object name>

The list of valid object types is as follows:

Configuring Auto Generated Permission Schemes

You can create an Auto Generated Permission Scheme via the Change Set Wizard when using the UI.

1. Start the Change Set Wizard,  then select Oracle Utilities > Create Auto Generated Permissions.
   
   
   
   
2. In Provide Change Detail, fill in the required settings of Granting Schema and Grantees (comma-separated list), then click Add Rule. 
   
   
   
   
3.  In Add Auto Permissions Rule, click Add/Modify to select objects. 
   
   
   
   
4.  In Select Objects, use the controls to select the object types and object instances to use, then click OK to return to Add Auto Permissions Rule. 
   • Filter This input allows you to filter the available object types and instances in the list selector on the left as you enter a search term.
   • List The left list selector contains 2 types of entry: object types and specific object instances. You can expand entries denoted by a folder to get to sub-types or specific instances of objects. If you select and move an entry with a folder icon, the rule will apply to all objects contained in the entry. For example, if you select and move the top-level 'tables' entry, the rule will be applied to all tables in the specified schema.
   • Unsupported Objects Datical DB does not include change set support for Materialized Views (mviews), User Defined Types (types), Operators (operators), or Directories. You can use the options available in the list selector to specify rules against all instances of those object types.
     
     If you need to include an object instance of an unsupported type, enter it Unsupported Objects, then and click Add Unsupported Objects.  It is added to the list. You can add as many as you need. Use the format objectType:objectName. For example, given a Materialized View (mview) with a name of MyOldMView, enter mviews:MyOldMView.

     
     
     

5. Complete the Add Auto Permissions Rule dialog, then click OK. 
   
   1. Objects - This section now shows the objects you selected. Click Add/Modify to change the list. 
   2. Permissions - Select checkboxes for for the permissions to apply to the objects. 
   3. Excluded Objects - Click Add/Modify to define exceptions to this definition. For example, if you want to grant SELECT on all tables except a table named HIDE add the HIDE table to this section.
      
      
      
      
      
      
6. In the Objects section, click Add/Modify. 
   

Datical DB automatically sets additional attributes on a change set for automatic permissions. 

• dbms="oracle" – apply the change set only when the platform is Oracle.
• runAlways="true" – apply the change set with every deploy.
• runOrder="last" – apply the change set last, after all other change sets have been applied.
• <rollback/> - do not apply this change set for a rollback.

Example Auto Generated Permissions Change Sets

<changeSet author="Joe User" dbms="oracle" id="Perms_Exmpl" runAlways="true" runOrder="last">
<appdba:autoPermissionForObjects grantees="DATICAL_APP_USR" schemaName="DATICAL_OBJECT_OWNER">
<appdba:rule excludeObjects="TABLE:T_FILM2" objects="TABLE" permissions="all privileges"/>
<appdba:rule objects="FUNCTION" permissions="EXECUTE" withGrant="true"/>
</appdba:autoPermissionForObjects>
<rollback/>
</changeSet>
Revoking Permissions 2 - Automatically revokes any permissions given to DATICAL_PERF_USR on Tables & Views in the DATICAL_PROD_SCHEMA<changeSet author="Joe User" dbms="oracle" id="Prevent_Grants_Example" runAlways="true" runOrder="last">
<appdba:autoPermissionForObjects grantees="VIDEO_TEST" schemaName="VIDEO_DEV">
<appdba:rule objects="TABLE,VIEW" permissions="none"/>
</appdba:autoPermissionForObjects>
<rollback/>
</changeSet>Granting Permissions 1 - Automatically generates permissions to grant all privileges on all tables except table T_FILM2 to DATICAL_APP_USR. Also grants the 'EXECUTE' privilege the same user on all functions with the GRANT privilege

Auto Generated Permissions Deployment Behavior

You can observe the behavior of change sets that generate permissions. 

Actions Taken During Deployment

Once you have defined your permission schemes, they will be enacted on every subsequent deploy. (NOTE: You can use Labels and Context to control execution like you would with any other Change Sets.) During Deployment, the Datical DB engine will evaluate the current permissions configuration on the database, compare it to the schemes defined for this deployment, and perform the following actions:

• Grant permissions to roles and users on objects if they are defined in the scheme but missing from the database.
• Revoke permissions from roles and users if they are NOT defined in the scheme but are present in the database.
• Revoke permissions from roles and users if a rule exists where the permissions to be granted are set to 'none'

Enhanced Security Provided by Auto Generated Permissions

The actions described above are performed on all objects in a database whether or not they were affected by the deployment. This ensures that unauthorized and undesirable out of process permission activity is rectified with every Deployment. Even if you have no other change sets to deploy, you can perform a deployment that only executes the permissions routines in order to maintain compliance. This should prevent unauthorized database access and unexpected downtime that can be the result of permissions configuration errors.

Reporting of Auto Generated Permissions Activity

Once you are managing your permissions configuration automatically in Datical, the Deployment Report contains entries for all permissions activity taken for that ddeployment. A sample report entry is below.

Auto Generate Synonyms for Oracle

Application users might not be given direct access to database objects. They might have access to separate schema containing synonyms for the objects. The reasons for using synonyms are:

• Object invisibility:  Keep the original object hidden from the user.
• Location invisibility: Use aliases for objects that are not part of the local database.

Synonym management can quickly become complicated as it is another manual step in the database development process to create and update synonyms and the grants that control access to them as your database evolves. To ease the burden of this type of synonym management, Datical DB provides a special change type that allows the user to define rules and exceptions that control the automatic creation of synonyms in this workflow.

Including or Excluding Individual Objects

To include or exclude individual objects for a rule, you need to specify the object using the following syntax:

<object type>:<object name>

The list of valid object types is as follows:

Configuring Auto Generated Synonym Rules

You can create an Auto Generated Permission Scheme via the Change Set Wizard when using the UI.

1. Start the Change Set Wizard, then select Oracle Utilities > Create Auto Generated Synonyms. 
   
   
   
   
2. The first form for Auto Generated Synonyms contains a summary of defined rules and the controls to create new rules or edit/remove existing rules.
   
   
   
   
3. Click Add Rule. 
   
   
   
   
4. Click Add/Modify to add objects.  When the objects are selected, you can fill in the rest of the information. 
   • Type - Public or Private
   • Synonym Schemas - Comma-separated list of schema in which synonyms should be created. This is a required field when creating Private synonyms.
   • Object Schemas - Allows you to filter the schemas in a multiple schema deployment plan to specify where the rule applies.
   • Excluded Objects - Allows you to specify objects to this definition.
5. When you select an object to include or exclude, a dialog provides controls for selection. 
   • Filter This input allows you to filter the available object types and instances in the list selector on the left as you enter a search term.
   • List The left list selector contains 2 types of entry: object types and specific object instances. You can expand entries denoted by a folder to get to sub-types or specific instances of objects. If you select and move an entry with a folder icon, the rule will apply to all objects contained in the entry. For example, if you select and move the top-level 'tables' entry, the rule will be applied to all tables in the specified schema.
   • Unsupported Objects Datical DB does not include change set support for Materialized Views (mviews), User Defined Types (types), Operators (operators), or Directories. You can use the options available in the list selector to specify rules against all instances of those object types.
     
     
     
     

Datical DB automatically sets additional attributes on a change set for automatic synonyms. 

• dbms="oracle" – apply the change set only when the platform is Oracle.
• runAlways="true" – apply the change set with every deploy.
• runOrder="last" – apply the change set last, after all other change sets have been applied.
• <rollback/> - do not apply this change set for a rollback.

Example Auto Generated Synonym Change Set

<changeSet author="Joe User" dbms="oracle" id="Perms_Exmpl" runAlways="true" runOrder="last">
<appdba:autoSynonymForObjects>
<appdba:rule excludeObjects="TABLE:T_FILM" objects="TABLE, VIEW"/>
</appdba:autoSynonymForObjects>
<rollback/>
</changeSet>
Generating Synonyms 2 - Automatically generates public synonyms for tables and views except table T_FILM

Auto Generated Synonyms Deployment Behavior

Actions Taken During Deployment

Once you have defined your Auto Generated Synonyms rules, they will be enacted on every subsequent Deploy. (NOTE: You can use Labels and Context to control execution like you would with any other Change Sets.) During Deployment, the Datical DB engine will evaluate the existing synonyms on the database, compare them to the rules defined for the deployment and perform the following actions:

• Create synonyms for new objects as prescribed
• Create synonyms for existing objects that should have synonyms but do not

Reporting of Auto Generated Synonyms Activity

Once you are managing your synonyms configuration automatically in Datical, the Deployment Report will contain entries for all permissions activity taken for that Deployment. A sample report entry is below.