How to handle or prevent all Admins and users being removed

Question

I have a website that stores all users in a database. A user can log on to the website and if they have sufficient privileges can add/delete users, as well as change their privileges.

How do I ensure there is always at least one Administrator (to create/edit/delete users) in the system or that an administrator can easily be added to the system.

Surely I can put rules in place so that the Admin can't delete himself, or other rules like that. But what if someone deletes all the users in the SQL table. The passwords are hashed so it's not as if the DBA can just go in and add a new user with Admin rights.

Do I hardcode some special username/password that automatically gets added to the system if no Admin exists?

Answer 1

So, you're wondering how to prevent a user of your Application from deleting all of the Application Users, which are stored in your database on a simple Users table?

Implement the exact same strategy you would use for any other data-shape requirement. Assuming you don't have a framework that can do it better, you could add a whole-table constraint on Users to require there to be at least one Admin user, like follows:

ALTER TABLE Users
ADD CONSTRAINT Chk_MinOneAdminUser 
CHECK (SELECT SUM(Users) WHERE Type = 'Admin') > 0

(Assuming you are using SQL Server, don't mix multiple apps in the same database, don't have replication issues to worry about, can stand the performance hit of an additional select on all users for each table update, and can't think of something better.)

Answer 2

I have just a rule that an admin user cannot unmake themselves an administrator. So if Joe and Sue are administrators, Sue can make Joe a regular user, but then she's still left and can't make herself not an administrator. Similarly an administrator can't delete or disable their own account.

Now if the last administrator forgets their password, that's a problem.

Answer 3

what if someone deletes all the users in the SQL table. The passwords are hashed so it's not as if the DBA can just go in and add a new user with Admin rights.

Well, if someone deletes all the users in the SQL table, you load a backup? Why are they doing such things!?

There are various ways to recover an admin account:

1. Create a regular user, then connect to the DB directly and mark them as an admin.
2. Manually run whatever SPs are run when an admin is created.
3. Create a special page (or special version of the admin pages) which can be run without admin privileges (IP restricted, firewalled, etc.), login, and use them to create an admin.

These various strategies have several things in common:

1. They require you to be a server admin or DB admin.
2. They're dangerous and hacky. You won't want to keep them on and you'll want to make sure regular users can't trigger them. All such functionality should be deleted (not merely disabled) by default.
3. They're bypassing authentication, but are otherwise leveraging your existing code.
4. You don't need to code them until the problem actually happens (and in fact, should not do so; unmaintained hacks tend to become obsolete quickly).

Answer 4

Rather than focus on preventing people from circumventing the system I would focus on giving users a way to right the ship. In this case is it as simple as a command line app that lets them create an admin user in a pinch?

Answer 5

1. Someone deletes all users directly from the DB. You can simply check whether the dedicated / at least one Admin is available on the application startup:

   using System.Linq;
   using Dapper;
   
   public interface IAppDbInitializer {
       void Seed();
   }
   
   protected void Application_Start() {
       this.SeedApplicationDatabase();
   }
   
   public class AppDbInitializer : IAppDbInitializer {
   
       private IAppDbConnectionFactory appDbConnectionFactory;
       private IPasswordHasher passwordHasher;
   
       public AppDbInitializer(IAppDbConnectionFactory appDbConnectionFactory, IPasswordHasher passwordHasher) {
           this.appDbConnectionFactory = appDbConnectionFactory;
           this.passwordHasher = passwordHasher;
       }
   
       public void Seed() {
           this.CreateUser(username: "Admin");
           this.CreateUser(username: "User");
       }
   
       private void CreateUser(string username) {
   
           using(var dbConnection = this.appDbConnectionFactory.CreateConnection()) {
   
               dbConnection.Open();
   
               var entry = dbConnection.Query(
                   sql: "SELECT TOP 1 1 FROM Users WHERE Username = @Username", 
                   param: new { Username = username }
               ).SingleOrDefault();
   
               if(entry == null) {
   
                   var affectedRows = dbConnection.Execute(
                       sql: "INSERT INTO Users (Username, [Password]) SELECT @Username, @Password", 
                       param: new {
                           Username = username,
                           Password = this.passwordHasher.CreateHash(username)
                       }
                   );
   
                   if(affectedRows == 0) {
                        throw new System.Exception("No rows affected");
                   }
   
               }
   
           }
   
       }
   
   }
   
   private void SeedApplicationDatabase() { 
       DependencyResolver.Current.GetService<IAppDbInitializer>().Seed();
   }
   

2. Why not adding rules into your application so that Admin can't be deleted?