What is the best way to grant access to "Database View"?

nobutomo
Kilo Explorer

‎01-07-2015 08:51 PM

I'd like to let some IT folks to use the "Database View" to create reports which refer multiple tables.

I'm not sure how I should grant ACL to them.

Is there any simple and easy way to do it?

0 Helpfuls
  • 10,606 Views
11 REPLIES 11

tombialkowski
Giga Expert

‎10-04-2016 02:36 PM

Create the 'database_view_manager' role:


  • Create a new role called database_view_manager.
  • Add the ITIL and report_admin roles to the u_database_view_manager role.


Expose the Database Views and Tables modules:


  • Edit the 'System Definition > Database Views' module, add the u_database_view_manager role and check the box that reads 'Override application menu roles'.
  • Edit the 'System Definition > Tables' module, add the u_database_view_manager role and check the box that reads 'Override application menu roles'.


Setup ACLs for Database Views [sys_db_view], View Table [sys_db_view_table] and View Field [sys_db_view_table_field] - Allows members of u_database_view_manager to create, read, update and delete 'custom' database views, while preventing damage to core ServiceNow database views:


  • Database View [sys_db_view]:
    • Roles: u_database_view_manager
    • Field ACLs:
      • Database View [sys_db_view].* - Write
    • Table ACLs:
      • Database View [sys_db_view].none - Create (Set Condition: 'Name is empty')
      • Database View [sys_db_view].none - Write (Set Conditions: 'Name is empty' or 'Customer Update is true')
      • Database View [sys_db_view].none - Delete (Set Condition: 'Customer Update is true')
  • View Table [sys_db_view_table]:
    • Roles: u_database_view_manager
    • Field ACLs:
      • View Table [sys_db_view_table].* - Write
    • Table ACLs:
      • View Table [sys_db_view_table].none - Create (Set Conditions: 'Variable Prefix is empty' or 'View.Customer Update is true')
      • View Table [sys_db_view_table].none - Write (Set Conditions: 'Variable Prefix is empty' or 'Customer Update is true')
      • View Table [sys_db_view_table].none - Delete (Set Condition: 'Customer Update is true')
  • View Field [sys_db_view_table_field]:
    • Roles: u_database_view_manager
    • Field ACLs:
      • View Field [sys_db_view_table_field].* - Write (Set Condition: 'Customer Update is true')
    • Table ACLs:
      • View Field [sys_db_view_table_field].none - Create (Set Conditions: 'View Table.Customer update = true OR Customer Update is empty')
      • View Field [sys_db_view_table_field].none - Write (Set Conditions: 'Customer Update is true')
      • View Field [sys_db_view_table_field].none - Delete (Set Condition: 'Customer Update is true')


Granting Reporting Access to 'custom' database views: According to the wiki, "Non-admin users do not have access to database view records unless a read ACL on the database view record allows access". So, when someone creates a new database view, an Administrator would be tasked with setting up a read ACL before it can be used in reporting. To automate this I put together the following 3 business rules on the Database View [sys_db_view] table:


  • Create an after INSERT business rule 'Database View Created' which creates a read ACL when a new database view is created.
    • Set Filter Conditions: Customer Update is true.
    • Set Role Conditions: u_database_view_manager
    • Script:
function onAfter(current, previous) {

   //This function will be automatically called when this rule is processed.

   var gr = new GlideRecord('sys_security_acl');

   gr.initialize();

   gr.type = 'record';

   gr.operation = 'read';

   gr.admin_overrides = true;

   gr.name = current.name;

   var sys_id = gr.insert();

}


  • Create a before UPDATE business rule 'Database View Renamed' which renames the respective read ACL when the database view is renamed.
    • Set Filter Conditions: Customer Update is true.
    • Set Role Conditions: u_database_view_manager
    • Script:
function onBefore(current, previous) {

   //This function will be automatically called when this rule is processed.

   var gr = new GlideRecord('sys_security_acl');

   gr.addQuery('name','=',previous.name);

   gr.addQuery('operation','=','read');

   gr.addQuery('sys_customer_update','=',true);

   gr.query();

   if(gr.next()) {

   gr.name = current.name;

   gr.update();

   }

}


  • Create a before DELETE business rule - 'Database View Deleted': Deletes the read ACL when the database view is deleted.
    • Set Filter Conditions: Customer Update is true.
    • Set Role Conditions: u_database_view_manager
    • Script:
function onBefore(current, previous) {

   //This function will be automatically called when this rule is processed.

   var gr = new GlideRecord('sys_security_acl');

   gr.addQuery('name','=',current.name);

   gr.addQuery('operation','=','read');

   gr.addQuery('sys_customer_update','=',true);

   gr.query();

   if(gr.next()) {

       gr.deleteRecord();

   }

}


Add u_database_view_manager role to existing read ACLs (The script loops through every read ACL and add's u_database_view_manager role):


  • Elevate to security_admin
  • Navigate to System Definition > Scripts - Background
  • Copy the following script into the 'Run script' box and click the Run script button.
addRoleToACLsByOperation('abb326950f522200ba0ee498b1050e6d', 'read'); // <--- REPLACE WITH YOUR u_database_view_manager ROLE sys_id PRIOR TO EXECUTING   

   

function addRoleToACLsByOperation(role, operation) { 

/*

   Get the list of all Read ACLs

*/ 

   var grACLs = new GlideRecord('sys_security_acl');   

   grACLs.addQuery('operation','=',operation);   

   grACLs.query();   

   var i = 0;   

   var grAclRole = new GlideRecord('sys_security_acl_role');   

   grAclRole.initialize();   

   grAclRole.sys_user_role = role;   

/*

   Loop through the Read ACLs

*/

   while(grACLs.next()) {

   /*

   Does the Read ACL already have our role associated with it?

   */

       var gr = new GlideAggregate('sys_security_acl_role');

       gr.addQuery('sys_security_acl','=',grACLs.sys_id);   

       gr.addQuery('sys_user_role','=',role);   

       gr.query();   

       if(gr.next()) {

     /*

     It does, so skip updating.

     */

           gs.print("Skipping - Name=" + grACLs.name + ". Operation=" + grACLs.operation + " because it already has the role assigned.");   

       }   

       else {

     /*

     It does not, so make sure there is at least one role assigned before adding ours (If there are none then "anyone" already has read access and adding the role would only restrict.

     */

     var gr2 = new GlideAggregate('sys_security_acl_role');

     gr2.addQuery('sys_security_acl','=',grACLs.sys_id);

     gr2.addQuery('sys_user_role','!=',role);

           gr2.query();

     if(gr2.next()) {

     /*

   There is at least 1 role assigned to the current Read ACL, so assign our role as well.

     */

     gs.print("Role isn't assigned, but there is at least one other role assigned. Updating - Name=" + grACLs.name + ". Operation=" + grACLs.operation);   

     //gs.print("Updating - Name=" + grACLs.name + ". Operation=" + grACLs.operation);   

     grAclRole.sys_security_acl = grACLs.sys_id;   

     grAclRole.insert();   

     }

     else {

   gs.print("No roles are assigned to the read ACL " + grACLs.name + ". Skipping update.");   

     }

           i++;   

       }   

   }   

   gs.print(i + " total");   

}


Grant access to the Schema Map:



Wrapping up:


  • You should now have a new role called database_view_manager. Users with this role should have:
    • Read access to every table in the system.
    • Access to the 'System Definition > Database Views' and 'System Definition > Tables' modules.
    • The ability to create, update and delete custom database views, while the Out Of The Box database views remain read only.
    • The ability to build reports using the custom database views (Without having to request that a read ACL be created).
    • Access to the Schema map..


Do you 'like'?:)


Abdul13
Tera Contributor

‎12-20-2021 09:54 PM


1) Create a new Role.
       Navigation: User Administration > Roles > click on new. enter the name and under contains roles add any role which you want (ex: ITIL).

2) Add the above created role to user.
       User Administration > Users.

3) Edit the 'System Definition > Database Views' module, add the role (ex: database_view_manager) role          and check the box that reads 'Override application menu roles'.

    Edit the 'System Definition > Tables' module, add the role (database_view_manager role) and check the        box that reads 'Override application menu roles'.

4) Add newly created role in ACL.
     i) System security > Acess control> under 'name' search as sys_db_view and under 'operation' select                 'Read'.
     ii) select any record and under 'Require role' add the 'role which created in 1st point'. similarly add the                role to remaining records (only 'read' operation records).

 

I hope this will help.

 

Regards,

Rajak