Our quarterly access review is a 9,800 row Excel file that we email to 140 managers. I need help.
Posted by Careless_Passage8487@reddit | sysadmin | View on Reddit | 49 comments
That is the whole post. 9,800 rows. 140 managers. Due in 10 days. Completion rate last quarter was 34%. The 66% who did not complete it got chased for two weeks and then we closed the review anyway because the auditor needed the evidence package.
The managers who do complete it approve everything. Every single row. Because they have no idea what half the entitlements mean and approving is faster than asking.
We have flagged this to leadership three times. We are told to find a way to make the spreadsheet easier to use.
What are other people actually doing for this. We cannot afford Sailpoint. We have Okta and Entra and a lot of patience that is running very thin.
Ghelderz@reddit
Entra ID does have automated access reviews
pelzer85@reddit
TIL. WOW. Thank you. We have a travel group that we except from certain conditional access policies, but remembering to check on membership is a pain. This is at least a good way to get multiple people reminders to check on that group.
SageAudits@reddit
Do access reviews but as others said you can do PIM around CA exclusion groups and it’s a game changer. Hate the UI but it works
snipazer@reddit
We have a similar group for travel but we use PIM to set start and end times for it's membership so they're automatically removed.
pelzer85@reddit
Oh that’s an interesting way to use that. I had really only thought of PIM for standard roles. Nice!
Internet-of-cruft@reddit
Just remember you need P2 licensing for all those folks in those PIM enabled groups, whereas admin only usage is typically far less.
pelzer85@reddit
Ah, man. Foiled my plan!
iama_bad_person@reddit
When our auditors found out we had this turned on it was like we told them they were getting free pizza for life. Pushed a lot of our access burdon onto the Site and Collection admins instead of us.
pakman82@reddit
I bet your Sharepoint team was a little under the gun at first.. but once they get their users trained .. ahhhh delegation! Or it forced ppl to maybe reconsider 10 million little sites for every fanciful idea .
whiskeyandfries@reddit
Do you have the name of the tool or feature I should read up on? I am trying to get the orgs we look over to start caring about access governance.
Ghelderz@reddit
Entra ID Access Reviews
NoCream2189@reddit
what sort of audit ?
what sort of access
typically compliance audits are only looking at and for people with privileged access. reviewing they still need it and justification of why?
surely you don’t have 9800 people with privileged access??
Internet-of-cruft@reddit
Depends on what is considered "privileged".
Some high compliance requirement frameworks care about every user, for differing reasons, which can mean privileged (admin) and regular users.
AcidBuuurn@reddit
It could be 100 people with access to 98 things each. I read it like SharePoint or folder access but I’m not sure.
BeilFarmstrong@reddit
Okta now supports access campaigns from purely csv imports. You just need to make an app to import the data into. It's been an absolute game changer for us. But also I'm assuming by your post that you don't currently have licenses to access campaigns. It's extortion prices of course
Confident_Cry_9363@reddit
You have a huge mess on your hands, no doubt.
1. Make a job code matrix. Start with the permissions common to each job code (or department or however you classify your employees.)
2. Create a validation process that separates the exceptions from the rule. Don't make your manager approve access that is already considered birthright access. That's already approved. The easiest *free* way is to use Excel or whatever scripting language you are familiar with. Export the deltas (as compared to the job code matrix) and send only the personnel to each manager that they need to approve. Color code them Red for assigned access not previously authorized as compared to the rest of the job code, color it yellow for authorized, but not assigned. Again, this is doable in either Excel or whatever script you like.
3. Review the Birthright access with the manager to ensure that baseline is still accurate. That should be MUCH less than reviewing every person and every permission.
I've been in a similar position to what you are dealing with. You don't have to fix it all at once (or at least you didn't allude to that in your post). Make it a little better every year. The current pencil whipping doesn't make anyone happy, not you, not the managers, and not your auditors.
Let me know if you need some examples to get you started.
Good luck!
NNBNNB@reddit
Take a look at https://www.syskit.com/ if you are MS365.
Wolf_in_SheepsHoodie@reddit
If you have Entra P2 licensing then start using access reviews. If not, which sounds like the the more probable scenario, back up and document group membership, Remove access for non responders, once they cry and complete the review then use powershell to run through adding access back. Lots of examples of scripts/modules to add/remove group membership in bulk.
This is all provided Entra/AD groups are actually what provides access and not just local access at the application.
LeadershipSweet8883@reddit
Upload the spreadsheet to SharePoint, create a PowerApp with the spreadsheet as a back end that creates a streamlined view for the managers and makes it easy to check the boxes. Have the users use the PowerApp rather than the spreadsheet directly.
Managers just approving everything is a separate issue, but mostly the manager's issue rather than yours.
smith0112358@reddit
Has anyone tried asking Copilot to review the spreadsheet and help you bring back the $1 slice?
xSchizogenie@reddit
That’s not a tech problem. That’s a C/Management problem.
Garble7@reddit
Seems like you could easily create an in house application that people could go to to validate the permissions on a schedule.
OneSeaworthiness7768@reddit
Another “jr. sysadmin” with hidden post history full of not IT but rather AI, UX, and web dev posts. Most of their comments manage to work in mentioning Miro. So wild guess, this is a bogus marketing account.
Sunsparc@reddit
A single 9,800 row file is nuts.
I've been involved in three separate IAM projects now. First with Sailpoint, then Entra, and currently a custom built in-house app. Sailpoint and Entra both function similarly, it came down to cost and ease of integration. With both, each manager would be sent an email that allowed them to access a webpage portal that showed individual employee access with group name, description, and action (approve/deny). Once access reviews were completed, we would run something on our side that would make the necessary deletions.
The custom built app does pretty much the same thing except we added a few quality of life features and also reorganized the layout based on feedback from all managers.
I highly, highly recommend going with Entra ID if you're a Microsoft shop. It's easy to set up and automate with Powershell.
rack_and_stack_42@reddit
9,800 rows in one spreadsheet sent to 140 people is not an access review. It is a homework assignment nobody wants to do. The 34% completion rate is not surprising. The surprising part is that 34% actually did it.
The core problem is not the spreadsheet. It is that you are asking 140 people to do something with no accountability, no deadline enforcement, and no way to verify they actually looked at what they approved. Approving everything in 2 minutes and approving nothing look the same in the spreadsheet. Both produce a completed row.
What fixed this for us:
Break it up by manager. Each manager gets only their direct reports and only the entitlements those reports have. Nobody should be looking at 9,800 rows. They should be looking at 15-40 rows for their team. That alone changes the completion rate because the task goes from "impossible homework" to "5 minute review."
Add a "what changed" column. Instead of showing every entitlement and asking the manager to approve or revoke, highlight what is new since the last review. Most entitlements have not changed. The manager only needs to think about the ones that were added, removed, or look unusual. Everything else is "confirm no change."
Make the review a task with a deadline and escalation, not an email with an attachment. If the manager does not complete it in 5 business days, their manager gets notified. If it is still incomplete at day 8, it escalates again. The 66% who did not complete it last quarter did not complete it because there were no consequences for ignoring the email.
Stop closing the review with incomplete data. If the auditor is getting an evidence package where 66% of the reviews were never completed, that is not evidence. That is a finding waiting to happen. It is better to have a smaller scope that is fully reviewed than a full scope that is two thirds rubber-stamped or missing.
You do not need SailPoint for this. You need a system that assigns the review to each manager with their specific scope, tracks completion, and escalates when they ignore it. That can run on top of Okta and Entra without replacing either.
bakugo@reddit
Thanks, ChatGPT
flummox1234@reddit
If you have Okta you have oauth. This is at best a simple web application with an oauth front end. Use a finite state machine to track the state on line items. Honestly a decent web dev could roll out a MVP in about a week. Use the oauth2 and statesman gems, turn on database backed state changes and you'll have your paper trail. Postgres for database. If you use a cloud based db then your backups should be built into the platform.
Financial_Instance23@reddit
The issue with the spreadsheet is that managers have no incentive to actually audit when they're just looking at cryptic entitlements. I've seen that building a lightweight web interface that pulls directly from Okta/Entra can solve this by only showing managers the rows they actually own. It replaces the 9,800-row file with a simple 'Approve/Deny' portal with human-readable descriptions. Would you be open to seeing how a custom interface like that could work without the Sailpoint price tag?
CeC-P@reddit
For every row where you're not sure if they're supposed to be a member or not because of incomplete documentation but former IT or the people who made the group, put "not enough info" and then turn that in. That is the answer to their question, technically. Then they'll see "Oh wow, the report says we have a problem"
...and then fail to do anything about it ever.
But at least the business operations problem will go back to being a business operations problem instead of an IT problem.
mat-ferland@reddit
A 9,800-row spreadsheet is not an access review, it’s audit theater with attachments. I’d start with exceptions and high-risk roles in Entra/Okta so managers approve meaning, not rows.
mixduptransistor@reddit
are you just sending this one spreadsheet to everyone?
You should be generating a specific report for each manager who is reviewing so that they don't have to go through 9800 rows
You have Entra, use the built in access reviews there
If someone doesn't finish by the deadline, revoke access. You're not the one revoking access, the manager who didn't confirm these people need access is the one who revoked it
squatfarts@reddit
Won't help you with this short term issue but long term get a proper IGA tool for this.
sgtpepper78@reddit
I would try ingesting the data into powerBI
Sylogz@reddit
If you have a clear structure with who own the access then it should be easier.
We have 2 owners for each access and they are the ones that approve/deny the access.
Then make a excel sheet per owner and have the owner only get his part. If you have a ticket system then create a ticket per owner so you have a trace of that.
Then escalate up when tickets are not completed in time.
This should be easy enough to automate so you don't have to create this manually.
jfdirfn@reddit
Scream test for non responders
Igot1forya@reddit
Sounds like a great use case for converting this into a database with a web front end that you can automate much of this with an audit trail for when people log into the site for data entry and search. Then there is no sending anything to anyone except a bookmark to the secured portal.
OkEmployment4437@reddit
34% completion is the real answer here. The spreadsheet sucks, sure, but the bigger problem is you're asking 140 managers to review way too much access with no consequence for punting, so they either ignore it or rubber stamp it. I'd cut the first pass down to privileged and other high-risk access only, map raw entitlements into business roles/packages, then send each manager a tiny review for just their people. Once it's small enough to finish, make non-response an escalation and then revocation path. Entra and Okta can get you a decent version of that without buying full IGA.
tantricengineer@reddit
As someone who reviews spreadsheets like this every quarter (hundreds to a thousand or two rows):
Make it due in a month or two. Ten days means it is treated without respect.
Managers should be delegating the review to people managing those assets. Ensure managers assign the spreadsheets to people who care about their toys being taken away if the review is incomplete.
Teams should be figuring out tooling for themselves to complete the review. Make them compete with each other for innovating on the process in the Age of AI.
reol7x@reddit
Like a review of access to critical systems? Maybe have a talk with the auditor and whoever is in charge of security.
New company policy...Anything not approved is revoked.
AndyceeIT@reddit
Yeah 9800 rows of access is pretty insane. And nonsensical, unless you have 9500 staff? What differentiates access?
OP you're not going to win this round. If you can't feed into the process, you're SOL.
Given you're looking at software, presumably you do have some authority.
Some suggestions: - having no idea what the access boundaries are, can you group them into "roles"? - similarly, can you remove or group the "lower" or common access lines as standard for the org?
SimpleSysadmin@reddit
Use group based membership or normalise the database to sets of access and who has what, thats shouldn’t down volume.
PanicAdmin@reddit
Without using specialized tools, this is the only way, op
Havi_40@reddit
I don't know what you should do. Here's what I would based on a lot of assumptions because we don't have the details.
Managers are like children on acid: zero attention span if it's boring, so you'll need to adapt accordingly.
Organise the spreadsheet in a way I can see things better. I use colour coding to make it pop. A. Critical access - only admins should have. B. Essencial access - the absolute minimum a position must have to perform their duties. C. Extra access - whatever extra access a person might have because of taking on extra work/changing positions/specific project folders, etc.
Do my own due diligence and collect as much information as I can on all employees (to know B. above), such as getting sign-in reports, audit reports that show everything they've used, OKTA might give you some pretty good ones too (don't know, never used it). This will give me grounds to remove access later.
After that, I'd have enough info to remove everything I know from the spreadsheet, which would leave me with only C. Extra access.
Now I would separate the Extra Access spreadsheet into smaller, more manageable chunks. Like, using Last Accessed as basis to divide it into A. Need access (last accessed <1 month ago) B. Check access (2 mo>last access>1 mo) C. This will be remove if you can't justify why you need it.
That's assuming that you're also gonna send one of these to each manager, otherwise, it's just a bunch of people doing whatever they want with a joined spreadsheet.
Cubewood@reddit
Create power automate flows which emails owners of the corresponding controls, and let them approve them in the form/email. They still might not read it, but at least you got an audit trail of their confirmation and at that point they own the outcome when the data is wrong.
theoriginalharbinger@reddit
Okta has access reviews.
Okta also has workflows, which enables you to push your choice of notification (Slack / Email / etc.) to your choice of user (app owner / group owner / user's manager / user's manager's manager / whatever) to make your choice of update (Tables in Workflows / Google Spreadsheet / Excel / etc.) without the reviewer having to do anything besides tick a box / write "Yes" in Slack / etc.
While Okta Workflows shouldn't be pushed into service as an ad-hoc access review tool, it's gonna be better at the job than a spreadsheet.
WideAwakeNotSleeping@reddit
Can you not split the master sheet into 140 manager-specific sheets? We were in Google, and it had some great functions (importrange) and appsceipts you could write yourself.
How large is the org? Why not implement an IGA solution. Most should have a review / recertification built in.
Ultimately, if no action then remove access. Start with the managers themselves 😇
wey0402@reddit
Concentrate on the most important rows an review dem properly (10-30%) or split it to each quarter then different managers get hit at different times maybe?
TrippTrappTrinn@reddit
This is the managers/data owners responsibility. If they cannot be bothered to check it, then any issue is on them.
Then again, I assume the data is filtered by owner/manager so that any reviewer only need to review what is relevant for them.
dedjedi@reddit
if the auditors sign off, what's the problem? you're not a lawyer or a biz strategist.