Security Auditing - How to Enumerate User Access

rmalt

Hi All,
I find this site very helpful so I thought I'd give back to the community. I wanted to write a script that would allow me to see which users had update permissions on any given table. I'm using Windows Security in NAV 6. The code below is not all original work. The section that creates the user table was found on this site.

I plan on creating a web form on our intranet that will prompt for the table name and possibly the access type (Read/Update/Insert) and plugs the values in the appropriate spots before executing the SQL code.

Anyways, thought this might be useful for some poor soul trying to perform SOX auditing.

Declare 
@Tablename varchar(50),
@ObjectID int

-- This can be modified to see access for any given table
Select @Tablename = 'Item'

-- Get the object ID of the selected table
-- NAV company name can be changed below
Select @ObjectID = ID 
From [Object] 
Where Name = @Tablename 
and [Company Name] = 'EPW - LIVE'

-- Start of Create User Table
-- The following section just creates a table containing
-- Windows user names and SIDs
Declare @Users table (Name varchar(30), NAVSID varchar(255))

Declare
@SID varchar(255),
@OrigSID varchar(255),
@A bigint,
@B bigint,
@C bigint,
@D bigint,
@Value varbinary(255)

Declare PrintUser INSENSITIVE CURSOR FOR
Select SID From [Windows Login]
Open PrintUser

Fetch Next From PrintUser Into @SID

While (@@fetch_status <> -1)
Begin
      Select @OrigSID = @SID
      SET @SID = REVERSE(@SID)
      SET @D = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))

      SET @SID = SUBSTRING(@SID,CHARINDEX('-',@SID)+1,255)
      SET @C = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))

      SET @SID = SUBSTRING(@SID,CHARINDEX('-',@SID)+1,255)
      SET @B = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))

      SET @SID = SUBSTRING(@SID,CHARINDEX('-',@SID)+1,255)
      SET @A = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))

      -- Prefix the binary value with S-1-5-21 and reverse the byte order for each group.
      Select @Value = 
            0x010500000000000515000000
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A))) 
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B))) 
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))

      Insert into @Users
      select Name, @OrigSID as 'NAV SID' from master..syslogins where SID = @Value

      Fetch Next from PrintUser into @SID
End
Deallocate PrintUser
-- End of Create User Table

-- After we have the list of users, and the object ID,
-- it's pretty easy to find out who can modify it
Select Name From @Users Where NAVSID in (
Select Distinct [Login SID] from [Windows Access Control]
Where [Role ID] in (
	Select [Role ID] 
	From [Permission] 
	Where [Object ID] = @ObjectID 
	and [Modify Permission] = 1
)
)