The users management package has three procedures and a function:

• procedure add menu item that adds a menu item in the menu items table
• procedure set role permission that sets a permission per given role and menu item in the menu roles table
• procedure set user permission that sets a permission per given user and menu item in the menu users table
• function get menu permissions that returns a comma separated list of permissions on menu items for the given user

Procedure add menu item

Parameters:

• pi_menu_item_label in varchar2 – label of the menu item
• pi_menu_parent_id in integer – parent menu item id
• pi_menu_item_order in integer – order of the menu items under the same parent

Call example:

• begin menu_management.add_menu_item('Sales', null, 1); end;

Procedure set role permission

Parameters:

• pi_menu_item_id in integer – foreign key to menu item id in the menu items table
• pi_role_id in integer – foreign key to role id in the roles table

Call example:

• begin menu_management.set_role_permission(2, 1); end;

Procedure set user permission

Parameters:

• pi_menu_item_id in integer – foreign key to menu item id in the menu items table
• pi_user_id in integer – foreign key to user id in the users table

Call example:

• begin menu_management.set_user_permission(8, 4); end;

Function get menu permissions

Parameters:

• pi_user_id in integer – user id

Call example:

• select user_name, menu_management.get_menu_management(user_id) from users;

Internally, function get menu permissions calls the following functions:

• get_roles_permissions - returns a comma separated list of permissions on menu items set per role for the roles of the given user
• get_role_permissions - returns a comma separated list of permissions on menu items set per role for the given role
• get_user_permissions - returns a comma separated list of permissions on menu items set per user for the given user
• get_upstream_permissions - returns a comma separated list of permissions on parent menu items for the given menu item
• get_downstream_permissions - returns a comma separated list of permissions on child menu items for the given menu item