Main Tables Views Indexes Constraints Triggers Procedures Functions Packages Sanity check Index

ACS3 Data Model

Package source Package body source

AD_GENERAL_PERMISSIONS

Package source

Source
     1: package ad_general_permissions
     2: as
     3:  -- Returns 't' if the specified user has the specified permission on
     4:  -- the specified database row.
     5:  --
     6:  function user_has_row_permission_p (
     7:   v_user_id		general_permissions.user_id%TYPE,
     8:   v_permission_type	general_permissions.permission_type%TYPE,
     9:   v_on_what_id		general_permissions.on_what_id%TYPE,
    10:   v_on_which_table	general_permissions.on_which_table%TYPE
    11:  )
    12:  return char;
    13: 
    14:  function grant_permission_to_user (
    15:   v_user_id		general_permissions.user_id%TYPE,
    16:   v_permission_type	general_permissions.permission_type%TYPE,
    17:   v_on_what_id		general_permissions.on_what_id%TYPE,
    18:   v_on_which_table	general_permissions.on_which_table%TYPE
    19:  )
    20:  return general_permissions.permission_id%TYPE;
    21: 
    22:  function grant_permission_to_role (
    23:   v_group_id		general_permissions.group_id%TYPE,
    24:   v_role		general_permissions.role%TYPE,
    25:   v_permission_type	general_permissions.permission_type%TYPE,
    26:   v_on_what_id		general_permissions.on_what_id%TYPE,
    27:   v_on_which_table	general_permissions.on_which_table%TYPE
    28:  )
    29:  return general_permissions.permission_id%TYPE;
    30: 
    31:  function grant_permission_to_group (
    32:   v_group_id		general_permissions.group_id%TYPE,
    33:   v_permission_type	general_permissions.permission_type%TYPE,
    34:   v_on_what_id		general_permissions.on_what_id%TYPE,
    35:   v_on_which_table	general_permissions.on_which_table%TYPE
    36:  )
    37:  return general_permissions.permission_id%TYPE;
    38: 
    39:  function grant_permission_to_reg_users (
    40:   v_permission_type	general_permissions.permission_type%TYPE,
    41:   v_on_what_id		general_permissions.on_what_id%TYPE,
    42:   v_on_which_table	general_permissions.on_which_table%TYPE
    43:  )
    44:  return general_permissions.permission_id%TYPE;
    45: 
    46:  function grant_permission_to_all_users (
    47:   v_permission_type	general_permissions.permission_type%TYPE,
    48:   v_on_what_id		general_permissions.on_what_id%TYPE,
    49:   v_on_which_table	general_permissions.on_which_table%TYPE
    50:  )
    51:  return general_permissions.permission_id%TYPE;
    52: 
    53:  procedure revoke_permission (
    54:   v_permission_id	general_permissions.permission_id%TYPE
    55:  );
    56: 
    57:  function user_permission_id (
    58:   v_user_id		general_permissions.user_id%TYPE,
    59:   v_permission_type	general_permissions.permission_type%TYPE,
    60:   v_on_what_id		general_permissions.on_what_id%TYPE,
    61:   v_on_which_table	general_permissions.on_which_table%TYPE
    62:  )
    63:  return general_permissions.permission_id%TYPE;
    64: 
    65:  function group_role_permission_id (
    66:   v_group_id		general_permissions.group_id%TYPE,
    67:   v_role		general_permissions.role%TYPE,
    68:   v_permission_type	general_permissions.permission_type%TYPE,
    69:   v_on_what_id		general_permissions.on_what_id%TYPE,
    70:   v_on_which_table	general_permissions.on_which_table%TYPE
    71:  )
    72:  return general_permissions.permission_id%TYPE;
    73: 
    74:  function group_permission_id (
    75:   v_group_id		general_permissions.group_id%TYPE,
    76:   v_permission_type	general_permissions.permission_type%TYPE,
    77:   v_on_what_id		general_permissions.on_what_id%TYPE,
    78:   v_on_which_table	general_permissions.on_which_table%TYPE
    79:  )
    80:  return general_permissions.permission_id%TYPE;
    81: 
    82:  function reg_users_permission_id (
    83:   v_permission_type	general_permissions.permission_type%TYPE,
    84:   v_on_what_id		general_permissions.on_what_id%TYPE,
    85:   v_on_which_table	general_permissions.on_which_table%TYPE
    86:  )
    87:  return general_permissions.permission_id%TYPE;
    88: 
    89:  function all_users_permission_id (
    90:   v_permission_type	general_permissions.permission_type%TYPE,
    91:   v_on_what_id		general_permissions.on_what_id%TYPE,
    92:   v_on_which_table	general_permissions.on_which_table%TYPE
    93:  )
    94:  return general_permissions.permission_id%TYPE;
    95: 
    96:  procedure copy_permissions (
    97:   v_old_on_what_id	general_permissions.on_what_id%TYPE,
    98:   v_new_on_what_id	general_permissions.on_what_id%TYPE,
    99:   v_on_which_table	general_permissions.on_which_table%TYPE,
   100:   v_user_id1		general_permissions.user_id%TYPE,
   101:   v_user_id2		general_permissions.user_id%TYPE
   102:  );
   103: end ad_general_permissions;

Package body source

Source
     1: package body ad_general_permissions
     2: as
     3:  function user_has_row_permission_p (
     4:   v_user_id		general_permissions.user_id%TYPE,
     5:   v_permission_type	general_permissions.permission_type%TYPE,
     6:   v_on_what_id		general_permissions.on_what_id%TYPE,
     7:   v_on_which_table	general_permissions.on_which_table%TYPE
     8:  )
     9:  return char
    10:  is
    11:   user_has_row_permission_p char(1) := 'f';
    12:  begin
    13: 
    14:   -- Return true if the user is a system administrator
    15:   -- or if the permission has been granted to at least one of:
    16:   --
    17:   -- * all users
    18:   -- * registered users if the user is logged in
    19:   -- * the user directly
    20:   -- * a role in a user group that the user plays
    21:   -- * an entire user group of which the user is a member
    22:   --
    23:   select ad_group_member_p(v_user_id, system_administrator_group_id)
    24:   into user_has_row_permission_p
    25:   from dual;
    26: 
    27:   if user_has_row_permission_p = 'f' then
    28:    select decode(count(*), 0, 'f', 't')
    29:    into user_has_row_permission_p
    30:    from general_permissions gp
    31:    where gp.on_what_id = v_on_what_id
    32:    and gp.on_which_table = lower(v_on_which_table)
    33:    and gp.permission_type = lower(v_permission_type)
    34:    and ((gp.scope = 'all_users')
    35:         or (gp.scope = 'registered_users'
    36:             and v_user_id > 0)
    37:         or (gp.scope = 'group'
    38:             and exists (select 1
    39:                         from user_group_map ugm
    40:                         where ugm.user_id = v_user_id
    41:                         and ugm.group_id = gp.group_id))
    42:         or (gp.scope = 'group_role'
    43:             and exists (select 1
    44:                         from user_group_map ugm
    45:                         where ugm.user_id = v_user_id
    46:                         and ugm.group_id = gp.group_id
    47:                         and ugm.role = gp.role))
    48:         or (gp.scope = 'user'
    49:             and gp.user_id = v_user_id))
    50:    and rownum < 2;
    51:   end if;
    52: 
    53:   return user_has_row_permission_p;
    54:  end user_has_row_permission_p;
    55: 
    56:  function grant_permission_to_user (
    57:   v_user_id		general_permissions.user_id%TYPE,
    58:   v_permission_type	general_permissions.permission_type%TYPE,
    59:   v_on_what_id		general_permissions.on_what_id%TYPE,
    60:   v_on_which_table	general_permissions.on_which_table%TYPE
    61:  )
    62:  return general_permissions.permission_id%TYPE
    63:  is
    64:   v_permission_id	general_permissions.permission_id%TYPE;
    65:  begin
    66:   select gp_id_sequence.nextval into v_permission_id from dual;
    67: 
    68:   insert into general_permissions
    69:    (permission_id, on_what_id, on_which_table,
    70:     scope, user_id, permission_type)
    71:   values
    72:    (v_permission_id, v_on_what_id, v_on_which_table,
    73:     'user', v_user_id, v_permission_type);
    74: 
    75:   return v_permission_id;
    76:  end grant_permission_to_user;
    77: 
    78:  function grant_permission_to_role (
    79:   v_group_id		general_permissions.group_id%TYPE,
    80:   v_role		general_permissions.role%TYPE,
    81:   v_permission_type	general_permissions.permission_type%TYPE,
    82:   v_on_what_id		general_permissions.on_what_id%TYPE,
    83:   v_on_which_table	general_permissions.on_which_table%TYPE
    84:  )
    85:  return general_permissions.permission_id%TYPE
    86:  is
    87:   v_permission_id	general_permissions.permission_id%TYPE;
    88:  begin
    89:   select gp_id_sequence.nextval into v_permission_id from dual;
    90: 
    91:   insert into general_permissions
    92:    (permission_id, on_what_id, on_which_table,
    93:     scope, group_id, role, permission_type)
    94:   values
    95:    (v_permission_id, v_on_what_id, v_on_which_table,
    96:     'group_role', v_group_id, v_role, v_permission_type);
    97: 
    98:   return v_permission_id;
    99:  end grant_permission_to_role;
   100: 
   101:  function grant_permission_to_group (
   102:   v_group_id		general_permissions.group_id%TYPE,
   103:   v_permission_type	general_permissions.permission_type%TYPE,
   104:   v_on_what_id		general_permissions.on_what_id%TYPE,
   105:   v_on_which_table	general_permissions.on_which_table%TYPE
   106:  )
   107:  return general_permissions.permission_id%TYPE
   108:  is
   109:   v_permission_id	general_permissions.permission_id%TYPE;
   110:  begin
   111:   select gp_id_sequence.nextval into v_permission_id from dual;
   112: 
   113:   insert into general_permissions
   114:    (permission_id, on_what_id, on_which_table,
   115:     scope, group_id, permission_type)
   116:   values
   117:    (v_permission_id, v_on_what_id, v_on_which_table,
   118:     'group', v_group_id, v_permission_type);
   119: 
   120:   return v_permission_id;
   121:  end grant_permission_to_group;
   122: 
   123:  function grant_permission_to_reg_users (
   124:   v_permission_type	general_permissions.permission_type%TYPE,
   125:   v_on_what_id		general_permissions.on_what_id%TYPE,
   126:   v_on_which_table	general_permissions.on_which_table%TYPE
   127:  )
   128:  return general_permissions.permission_id%TYPE
   129:  is
   130:   v_permission_id	general_permissions.permission_id%TYPE;
   131:  begin
   132:   select gp_id_sequence.nextval into v_permission_id from dual;
   133: 
   134:   insert into general_permissions
   135:    (permission_id, on_what_id, on_which_table,
   136:     scope, permission_type)
   137:   values
   138:    (v_permission_id, v_on_what_id, v_on_which_table,
   139:     'registered_users', v_permission_type);
   140: 
   141:   return v_permission_id;
   142:  end grant_permission_to_reg_users;
   143: 
   144:  function grant_permission_to_all_users (
   145:   v_permission_type	general_permissions.permission_type%TYPE,
   146:   v_on_what_id		general_permissions.on_what_id%TYPE,
   147:   v_on_which_table	general_permissions.on_which_table%TYPE
   148:  )
   149:  return general_permissions.permission_id%TYPE
   150:  is
   151:   v_permission_id	general_permissions.permission_id%TYPE;
   152:  begin
   153:   select gp_id_sequence.nextval into v_permission_id from dual;
   154: 
   155:   insert into general_permissions
   156:    (permission_id, on_what_id, on_which_table,
   157:     scope, permission_type)
   158:   values
   159:    (v_permission_id, v_on_what_id, v_on_which_table,
   160:     'all_users', v_permission_type);
   161: 
   162:   return v_permission_id;
   163:  end grant_permission_to_all_users;
   164: 
   165:  procedure revoke_permission (
   166:   v_permission_id	general_permissions.permission_id%TYPE
   167:  )
   168:  is
   169:  begin
   170:   delete from general_permissions
   171:   where permission_id = v_permission_id;
   172:  end revoke_permission;
   173: 
   174:  function user_permission_id (
   175:   v_user_id		general_permissions.user_id%TYPE,
   176:   v_permission_type	general_permissions.permission_type%TYPE,
   177:   v_on_what_id		general_permissions.on_what_id%TYPE,
   178:   v_on_which_table	general_permissions.on_which_table%TYPE
   179:  )
   180:  return general_permissions.permission_id%TYPE
   181:  is
   182:   v_permission_id	general_permissions.permission_id%TYPE;
   183:  begin
   184:   select permission_id
   185:   into v_permission_id
   186:   from general_permissions
   187:   where on_what_id = v_on_what_id
   188:   and on_which_table = lower(v_on_which_table)
   189:   and scope = 'user'
   190:   and user_id = v_user_id
   191:   and permission_type = lower(v_permission_type);
   192: 
   193:   return v_permission_id;
   194: 
   195:  exception when no_data_found then
   196:   return 0;
   197:  end user_permission_id;
   198: 
   199:  function group_role_permission_id (
   200:   v_group_id		general_permissions.group_id%TYPE,
   201:   v_role		general_permissions.role%TYPE,
   202:   v_permission_type	general_permissions.permission_type%TYPE,
   203:   v_on_what_id		general_permissions.on_what_id%TYPE,
   204:   v_on_which_table	general_permissions.on_which_table%TYPE
   205:  )
   206:  return general_permissions.permission_id%TYPE
   207:  is
   208:   v_permission_id	general_permissions.permission_id%TYPE;
   209:  begin
   210:   select permission_id
   211:   into v_permission_id
   212:   from general_permissions
   213:   where on_what_id = v_on_what_id
   214:   and on_which_table = lower(v_on_which_table)
   215:   and scope = 'group_role'
   216:   and group_id = v_group_id
   217:   and role = v_role
   218:   and permission_type = lower(v_permission_type);
   219: 
   220:   return v_permission_id;
   221: 
   222:  exception when no_data_found then
   223:   return 0;
   224:  end group_role_permission_id;
   225: 
   226:  function group_permission_id (
   227:   v_group_id		general_permissions.group_id%TYPE,
   228:   v_permission_type	general_permissions.permission_type%TYPE,
   229:   v_on_what_id		general_permissions.on_what_id%TYPE,
   230:   v_on_which_table	general_permissions.on_which_table%TYPE
   231:  )
   232:  return general_permissions.permission_id%TYPE
   233:  is
   234:   v_permission_id	general_permissions.permission_id%TYPE;
   235:  begin
   236:   select permission_id
   237:   into v_permission_id
   238:   from general_permissions
   239:   where on_what_id = v_on_what_id
   240:   and on_which_table = lower(v_on_which_table)
   241:   and scope = 'group'
   242:   and group_id = v_group_id
   243:   and permission_type = lower(v_permission_type);
   244: 
   245:   return v_permission_id;
   246: 
   247:  exception when no_data_found then
   248:   return 0;
   249:  end group_permission_id;
   250: 
   251:  function reg_users_permission_id (
   252:   v_permission_type	general_permissions.permission_type%TYPE,
   253:   v_on_what_id		general_permissions.on_what_id%TYPE,
   254:   v_on_which_table	general_permissions.on_which_table%TYPE
   255:  )
   256:  return general_permissions.permission_id%TYPE
   257:  is
   258:   v_permission_id	general_permissions.permission_id%TYPE;
   259:  begin
   260:   select permission_id
   261:   into v_permission_id
   262:   from general_permissions
   263:   where on_what_id = v_on_what_id
   264:   and on_which_table = lower(v_on_which_table)
   265:   and scope = 'registered_users'
   266:   and permission_type = lower(v_permission_type);
   267: 
   268:   return v_permission_id;
   269: 
   270:  exception when no_data_found then
   271:   return 0;
   272:  end reg_users_permission_id;
   273: 
   274:  function all_users_permission_id (
   275:   v_permission_type	general_permissions.permission_type%TYPE,
   276:   v_on_what_id		general_permissions.on_what_id%TYPE,
   277:   v_on_which_table	general_permissions.on_which_table%TYPE
   278:  )
   279:  return general_permissions.permission_id%TYPE
   280:  is
   281:   v_permission_id	general_permissions.permission_id%TYPE;
   282:  begin
   283:   select permission_id
   284:   into v_permission_id
   285:   from general_permissions
   286:   where on_what_id = v_on_what_id
   287:   and on_which_table = lower(v_on_which_table)
   288:   and scope = 'all_users'
   289:   and permission_type = lower(v_permission_type);
   290: 
   291:   return v_permission_id;
   292: 
   293:  exception when no_data_found then
   294:   return 0;
   295:  end all_users_permission_id;
   296: 
   297: 
   298:  procedure copy_permissions (
   299:   v_old_on_what_id	general_permissions.on_what_id%TYPE,
   300:   v_new_on_what_id	general_permissions.on_what_id%TYPE,
   301:   v_on_which_table	general_permissions.on_which_table%TYPE,
   302:   v_user_id1		general_permissions.user_id%TYPE,
   303:   v_user_id2		general_permissions.user_id%TYPE
   304:  )
   305:  is
   306:  begin
   307:   insert into general_permissions
   308:     (permission_id, on_what_id, on_which_table, scope, user_id,
   309:      group_id, role, permission_type)
   310:   select gp_id_sequence.nextval, v_new_on_what_id, lower(v_on_which_table),
   311:     scope, user_id, group_id, role, permission_type
   312:   from general_permissions
   313:   where on_what_id = v_old_on_what_id and
   314:     on_which_table = lower(v_on_which_table) and
   315:     (user_id is null or not user_id in (v_user_id1, v_user_id2));
   316:  end copy_permissions;
   317: end ad_general_permissions;


Generated by OraSchemaDoc, (c) Aram Kananov, 2002