By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,402 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

Table privilege

100+
P: 332
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Function GetTablePrivilage()
  5. Dim cat As ADOX.Catalog
  6. Dim perm As Long
  7. Dim Ws As Workspace
  8. Dim i As Integer
  9. Dim t As Integer
  10. Dim db As DAO.Database
  11. Dim mytbl As TableDef
  12. Set db = CurrentDb
  13. Open "C:\privileges.html" For Output As #1
  14.  
  15. Print #1, "<html><style>"
  16. Print #1, "body{margin:0; padding:0;}"
  17. Print #1, "table{border:1px solid black; border-collapse:collapse;}"
  18. Print #1, "td {border:1px solid black; padding:0 4px 0 4px;}"
  19. Print #1, "</style>"
  20. Print #1, "<table>"
  21. Set Ws = DBEngine.Workspaces(0)
  22. Set cat = New ADOX.Catalog
  23. With cat
  24.     For i = 0 To Ws.Groups.Count - 1
  25.         Print #1, "<tr><th colspan='3'>"
  26.         Print #1, "Group " & Ws.Groups(i).Name
  27.         Print #1, "</tr>"
  28.         For Each mytbl In db.TableDefs
  29.             If Left(mytbl.Name, 4) <> "MSys" Then
  30.                 .ActiveConnection = CurrentProject.Connection
  31.                 perm = .Groups(Ws.Groups(i).Name).GetPermissions(mytbl.Name, adPermObjTable)
  32.                 If perm <> 0 Then
  33.                     Print #1, "<tr><td>" & mytbl.Name & "</td><td>" & perm & "</td><td>" & privilegeToText(perm) & "</td></tr>"
  34.                 End If
  35.             End If
  36.         Next mytbl
  37.     Next i
  38. End With
  39. Print #1, "<html><table>"
  40. Close #1
  41. End Function
  42.  
  43. Function privilegeToText(privilege As Long) As String
  44. Select Case privilege
  45.     Case -2147482624
  46.         privilegeToText = "read data"
  47.     Case -1073740800
  48.         privilegeToText = "update data"
  49.     Case -2147449856
  50.         privilegeToText = "insert data"
  51.     Case -2147417088
  52.         privilegeToText = "delete data"
  53.     Case 1024
  54.         privilegeToText = "read design"
  55.     Case -1073672960
  56.         privilegeToText = "modify design"
  57.     Case -1072853760
  58.         privilegeToText = "administer"
  59.     Case -1073675264
  60.         privilegeToText = "delete & update data"
  61.     Case -1073642496
  62.         privilegeToText = "read, update, insert, delete"
  63.     Case -2147384320
  64.         privilegeToText = "read, insert, delete data"
  65.     Case -1072706304
  66.         privilegeToText = "Administer"
  67.     Case -1073708032
  68.         privilegeToText = "read, update, insert"
  69.     Case 147456
  70.         privilegeToText = "No privilege"
  71.     Case Else
  72.         privilegeToText = "NOT DEFINED"
  73. End Select
  74.  
  75. End Function
Jun 19 '12 #1
Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,287
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.getobjectowner(strObjectName, 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:
http://books.google.com/books?id=TdJ...0group&f=false

-z
Aug 27 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.