none
Recursion in SELECT statement RRS feed

  • Question

  •  Hi,

    Given the following tables ( with corresponding values ) :

    Table name  :   P_DEFS

    Description : This is a basic table used for storing permission names, with their unique permission id and description.

    Sample rows :

    PERMISSION_ID   PERMISSION_NAME
    =============   ===============
    100             pmnAdmin
    101             pmnResearch
    102             pmnTheoretical
    103             pmnND
    104             pmnEngineering
    105             pmnLaboratory
    106             pmnAllAccess
    107             pmnRestricted

    Table name  :   P_MEMBERSHIP

    Description : This is the table where employees are assigned permissions from the P_DEFS table.  In the sample below, Authorized Personnel A was given permission 100, which is pmnAdmin.  In a sense, this table contains permission assignments.

    Sample rows :

    PERMISSION_ID   ASSIGNED_NAME                   UNIQUECODE
    =============   ===========================     ==========
    100             Authorized Personnel A          1212351
    100             Authorized Personnel B          3988101
    100             Authorized Personnel C          5611098
    100             Authorized Personnel D          8818230
    100             pmnRestricted                   9999999
    107             Restricted Personnel A          4406581
    107             Restricted Personnel B          6521134
    107             Restricted Personnel C          2558401

    In this table, a permission may also be assigned another permission.  In the above example, the permission <pmnRestricted> was also assigned to pmnAdmin.

    Therefore, the reading application should be able to return a list of Assigned Names for permission membership for pmnAdmin, as follows :

    Authorized Personnel A
    Authorized Personnel B
    Authorized Personnel C
    Authorized Personnel D

    In addition to the above list, since pmnRestricted was also assigned to pmnAdmin, the members of pmnRestricted should also be added to the SELECT statement list, thus the final list would be :

    Authorized Personnel A
    Authorized Personnel B
    Authorized Personnel C
    Authorized Personnel D
    Restricted Personnel A
    Restricted Personnel B
    Restricted Personnel C

    Problem :  The permission construct for table P_MEMBERSHIP can be nested, thus, even if pmnRestricted is assigned to pmnAdmin, other permission MAY also be assigned to pmnRestricted;  so, as an example, pmnEngineering may be assigned to pmnRestricted, and if so, the membership of pmnEngineering should also be listed.

    The level of permissions within another permissions assignment is not limited, but a StackOverflow must be prevented if a permission membership continuously refers to itself.  However, this constraint is already handled by the calling application.

    Problem now would be : how can I construct the necessary recursive SELECT statement in an Access MDB so the permission membership list would be generated properly ?  This is assuming that both tables are in an Access .mdb file.

    Lastly, the tables are to stay the same, no additional fields necessary.  It looks like a college assignment, huh ... but under the current restrictions, I cannot think of a better way to do this.

    Again, any theoretical advise is greatly appreciated.

     


    • Moved by Helen Zhou Thursday, April 7, 2011 9:34 AM (From:Windows Forms General)
    Thursday, April 7, 2011 2:43 AM

Answers

  • Unlike SQL Server, Microsoft Access does not support recursion in SQL. What you will probably need to do is generate the list hierarchy of PERMISSION_IDs from a given Permission in .NET code, using recursion (with multiple SQL calls of course), and once you have the list, execute a SELECT statement to retrieve all personnel from the P_MEMBERSHIP table that match (are IN) one of the PERMISSION_ID values.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by MegumiK Friday, April 8, 2011 2:05 AM
    Thursday, April 7, 2011 7:02 PM

All replies

  • I assume you have a question about Directory Services.

    http://msdn.microsoft.com/en-us/library/system.directoryservices.aspx

    Be aware that in this forum are persons active busy around the Windows Forms namespace.

    Maybe a moderator can move your question to a forum related to that directory service or you can find another one.

    You have here the chance on an extremely well answer, but the chance in a dedicated forum is probably much higher.

    If there is no one than post this in the general forum of your program language.

     

     


    Success
    Cor
    Thursday, April 7, 2011 5:52 AM
  • Unlike SQL Server, Microsoft Access does not support recursion in SQL. What you will probably need to do is generate the list hierarchy of PERMISSION_IDs from a given Permission in .NET code, using recursion (with multiple SQL calls of course), and once you have the list, execute a SELECT statement to retrieve all personnel from the P_MEMBERSHIP table that match (are IN) one of the PERMISSION_ID values.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by MegumiK Friday, April 8, 2011 2:05 AM
    Thursday, April 7, 2011 7:02 PM
  • No, the question is not about directory services.  It is what it is : a simple permissions scheme utilizing only 2 tables designed to provide a simple permissions listing that may be applied to an application.  This is in effect an abstract concerning the viability of a structured query language in it's capability to provide a solution to what seems to be a simple logical problem.

    We do not use the normal permissions proposed or provided for by operating systems or current database constructs.  However, Paul Clement, whose answer was given below, seemed to have a pretty good grasp of the situation.  What he proposed below is what we ended up doing anyway, since neither SQL Server nor Access supports the CONNECT BY functionality which can be found in Oracle, used for hierarchical data.  Again, we are not using any database platform currently in the market.  We built our own database from the ground up, please do not ask what or why.

    T-SQL does support recursion, but like I said, we are meant to use only SELECT statements; Big Boss is really hard-headed and old-fashioned, you see.   He wants to challenge us with a few issues, but with severe restrictions.  He frequently boasted that he knew Dr. Codd, do not know if it is true.

    In any case, Paul, thank you for your advice;  it is is exactly what we are going to do.



    Friday, April 8, 2011 2:03 AM