2

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?

  • 2
    YAGNI. Don't code for "what if". Code for things that are actually likely. If that happens - there should be backups. – Oded Apr 2 '14 at 19:04
  • How are the passwords hashed? There should be some library doing this and you could easily write a small script that allows you to generate a new password so you can insert it with SQL or write a script that inserts a admin user when needed. Otherwise of course you can always add some business rules that test if an admin is deleted and count the number of admins before doing so. But what if one of the two last admins leaves the company the other wants to delete his account and deletes his own (another business rule: no admin user can delete himself). But there should always be add ab admin. – thorsten müller Apr 2 '14 at 19:06
  • 2
    I would argue that going an manually running destructive queries gets into the warranty voiding part of software delivery. – Wyatt Barnett Apr 2 '14 at 21:04
  • 1
    @Oded This isn't YAGNI, it happens on web-apps all the time. – Ross Patterson Apr 3 '14 at 0:07
  • 1
    I realize this question is old, but I can't help asking: How is the situation after the deletion of all users different than the situation just after installing the application for the first time? How do you create the first admin user in the first place? – Hulk Nov 29 '16 at 13:17
1

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?

  • I like this. This would mean that anyone with access to my server could make an admin account, but if I'm assuming someone with malicious intent has access to my server and can create an admin account I'm probably already hosed, right? – TruthOf42 Apr 3 '14 at 12:25
  • 2
    Pretty much -- if they have console access the battle is pretty much over. You might be able to work in some os-specific security as well like requiring the user be a member of the Administrators group if you are on Windows. – Wyatt Barnett Apr 3 '14 at 13:18
  • I decided to go with this approach though slightly modified. I create a page on the site that is only accessible if the site is run locally, and the only thing it does it create an admin account – TruthOf42 Apr 3 '14 at 15:02
  • @TruthOf42: If you're not doing a constraint, make sure the fix is at least as accessible as the destructive behavior. No one should be able to delete users without also accessing that "add a default user" command. – DougM Apr 3 '14 at 15:36
7

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.)

  • Why anyone would maintain their users via direct database access is beyond me (that seems prone to error), but the OP did offer that as an example, and your solution actually prevents it. A simple and elegant solution. – Brian Apr 2 '14 at 20:45
  • +1 When your users are in the database, a constraint is the perfect tool. – Ross Patterson Apr 3 '14 at 0:08
  • @Brian What are the other alternatives for maintaining users? – TruthOf42 Apr 3 '14 at 12:17
  • @TruthOf42: Creating a web interface which is only accessible to admins and having that web interface talk to the database. This interface can then encode rules like, "admins cannot remove themselves." – Brian Apr 3 '14 at 12:53
  • 2
    @Phoshi "You probably want another implementation in your web UI for better error messages, as well, but you may as well make sure." -- these days, I'm finding maintaining a map of constraint name => error message if the constraint is violated is much easier. – Jules Apr 3 '14 at 13:27
2

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).
  • I like option 1, as I don't have to do anything to implement that solution. But there still is a problem that if someone deletes all users you can't add another user. SPs don't create the hashes of passwords, but merely receive the hash. Should I assume the user can just create a hash of a password themselves (with some tool online?) and submit it to the DB? – TruthOf42 Apr 2 '14 at 19:24
  • You can create a special page (or application) which calls your "create user" function without requiring authentication. It can be hardcoded with the username and password. E.g., in C#/Membership the page's codebehind would merely be something like: Membership.CreateUser("adminhack","te^mp*pa~ss_wordfkjl;SDKL"); – Brian Apr 2 '14 at 19:27
  • You have unit tests for your code, right? Including your password code? So in your tests, you probably have a pregenerated hash which maps to a password you know. Alternatively, you can always install on a different server or write a trivial program to call your password hashing library directly to get a hash, then copy it into the database. – user9876 Apr 2 '14 at 22:24
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.

  • From the OP: "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." – Brian Apr 2 '14 at 20:41
  • @Brian - but that doesn't make any sense. A database user with the ability to delete records in the user table also has the ability to put them back. – Scott Whitlock Apr 3 '14 at 10:38
  • Not if he doesn't remember the password hashes. The full quote, "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." Is this concern a bit silly? Probably. Does the concern at least make enough sense to be answerable? Yes. – Brian Apr 3 '14 at 12:51
  • Isn't this turning into a "but what if the user smashes the database server to bits with a sledgehammer". If someone uninstalls or otherwise trashes the DB tables, then its restore from backup, or reinstall time. – gbjbaanb Apr 3 '14 at 13:52
  • @gbjbaanb - I have to agree. I'm not sure about the validity of the question. – Scott Whitlock Apr 3 '14 at 14:17
0
  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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.