Table privilege

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Mariostg
    Contributor
    • Sep 2010
    • 332

    #1

    Table privilege

    In Access 2003, is there a way to get a list of table privileges (read, update, insert, delete) for a given group as defined in the security wizard.

    All I can find around is security relationships between users and groups. That's the easy part.

    Well, this is what I quickly hacked as a starting point.
    Code:
    Option Compare Database
    Option Explicit
    
    Function GetTablePrivilage()
    Dim cat As ADOX.Catalog
    Dim perm As Long
    Dim Ws As Workspace
    Dim i As Integer
    Dim t As Integer
    Dim db As DAO.Database
    Dim mytbl As TableDef
    Set db = CurrentDb
    Open "C:\privileges.html" For Output As #1
    
    Print #1, "<html><style>"
    Print #1, "body{margin:0; padding:0;}"
    Print #1, "table{border:1px solid black; border-collapse:collapse;}"
    Print #1, "td {border:1px solid black; padding:0 4px 0 4px;}"
    Print #1, "</style>"
    Print #1, "<table>"
    Set Ws = DBEngine.Workspaces(0)
    Set cat = New ADOX.Catalog
    With cat
        For i = 0 To Ws.Groups.Count - 1
            Print #1, "<tr><th colspan='3'>"
            Print #1, "Group " & Ws.Groups(i).Name
            Print #1, "</tr>"
            For Each mytbl In db.TableDefs
                If Left(mytbl.Name, 4) <> "MSys" Then
                    .ActiveConnection = CurrentProject.Connection
                    perm = .Groups(Ws.Groups(i).Name).GetPermissions(mytbl.Name, adPermObjTable)
                    If perm <> 0 Then
                        Print #1, "<tr><td>" & mytbl.Name & "</td><td>" & perm & "</td><td>" & privilegeToText(perm) & "</td></tr>"
                    End If
                End If
            Next mytbl
        Next i
    End With
    Print #1, "<html><table>"
    Close #1
    End Function
    
    Function privilegeToText(privilege As Long) As String
    Select Case privilege
        Case -2147482624
            privilegeToText = "read data"
        Case -1073740800
            privilegeToText = "update data"
        Case -2147449856
            privilegeToText = "insert data"
        Case -2147417088
            privilegeToText = "delete data"
        Case 1024
            privilegeToText = "read design"
        Case -1073672960
            privilegeToText = "modify design"
        Case -1072853760
            privilegeToText = "administer"
        Case -1073675264
            privilegeToText = "delete & update data"
        Case -1073642496
            privilegeToText = "read, update, insert, delete"
        Case -2147384320
            privilegeToText = "read, insert, delete data"
        Case -1072706304
            privilegeToText = "Administer"
        Case -1073708032
            privilegeToText = "read, update, insert"
        Case 147456
            privilegeToText = "No privilege"
        Case Else
            privilegeToText = "NOT DEFINED"
    End Select
    
    End Function
    Last edited by NeoPa; Jun 25 '12, 11:15 PM. Reason: Merged posts together.
  • zmbd
    Recognized Expert Moderator Expert
    • Mar 2012
    • 5501

    #2
    Mariostg:

    This was a fairly difficult one one for me to find...

    I think that you should be able to step thru the object using something along the lines of cat.getobjectow ner(strObjectNa me, adpermobjtable) which is what I've done when checking users against a table...

    for a fairly techie level of information on the security model try here: http://www.grahamwideman.com/gw/tech...ssec/index.htm

    You should also take a look at what you can read here... look about page 356/357:
    This book is designed to take Microsoft Access users to the next step in programming. Its five parts cover an intro-duction to VBA programming, manipulating databases with ADO, using DDL, event programming, and using ASP and XML. With more than 300 hands-on examples and 11 custom projects, users can quickly build the toolset required for developing their own database solutions. Learn how to write and debug your programming code with the Visual Basic Editor, and understand and use common VBA programming structures such as conditions, loops, arrays, and collections. Learn how to create and manage databases with ActiveX Data Objects (ADO), and perform database tasks with Jet/Access Structured Query Language (SQL) and its Data Definition Language (DDL) component. Learn how to query and manipulate your database from a web browser with Active Server Pages (ASP) and export and import Access data to and from XML both manually and programmatically.


    -z

    Comment

    Working...