Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23729

INFORMATION_SCHEMA Table info. about user locked due to max_password_errors

Details

    Description

      Currently with the max_password_errors configured, we don't really see any tables having a flag about the blocked user account due to this reason. (i.e., user blocked with the max attempt of trying to connect db with wrong password).

      https://mariadb.com/kb/en/server-system-variables/#max_password_errors

      One of our customers seems like in requirement of getting the list of blocked users, due to wrong password attempts.

      Like for the "Account Locking", whenever a user is locked, will have "account_lock:false or true" in JSON object of the table "mysql.global_priv table", can we implement the similar type of info available for the blocked users for the max_password_errors case?

      Attachments

        Issue Links

          includes

          New Feature - A new feature of the product, which has yet to be developed. MDEV-32218 message to notify end-user N-days prior the password get expired

          • Major - Major loss of function.
          • Closed
          is caused by

          Task - A task that needs to be done. MDEV-7598 Block user accounts after failed login attempts

          • Major - Major loss of function.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-27205 MariaDB user last login time details at DB level

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled

          Task - A task that needs to be done. MDEV-30045 settable message to notify end-user N-days prior the password get expired

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-31186 Provide information about a user via an information schema table

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-32649 Add INFORMATION_SCHEMA Table with user related details

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-29209 Implement connection response delay after a number of failed login attempts

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled

          New Feature - A new feature of the product, which has yet to be developed. MDEV-32218 message to notify end-user N-days prior the password get expired

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 12 older comments
            ralf.gebhardt Ralf Gebhardt added a comment - - edited

            nikitamalyavin, if we add a separate table it could make sense to address MDEV-27205 at the same time. And it could make sense to use a JSON field for the details.

            Finding a good name is always tricky. Ideas are USER_DETAILS, USER_USAGE, USER_CONNECT_INFO, USER_STATUS

            ralf.gebhardt Ralf Gebhardt added a comment - - edited nikitamalyavin , if we add a separate table it could make sense to address MDEV-27205 at the same time. And it could make sense to use a JSON field for the details. Finding a good name is always tricky. Ideas are USER_DETAILS, USER_USAGE, USER_CONNECT_INFO, USER_STATUS
            nikitamalyavin Nikita Malyavin added a comment - - edited

            Thanks for another reference, ralf.gebhardt! Yes, MDEV-27205 absolutely makes sense to be put in the same table. As I can see by the code, we currently don't store last login time internally, so an extra effort will be required.

            Since all these fields bend around user login details, my ideas are:
            AUTH, USER_ACCESS, USER_LOGIN, LOGON

            INFORMATION_SCHEMA.LOGON sounds the most decent to me, but we won't be able to add more general information about users there.

            nikitamalyavin Nikita Malyavin added a comment - - edited Thanks for another reference, ralf.gebhardt ! Yes, MDEV-27205 absolutely makes sense to be put in the same table. As I can see by the code, we currently don't store last login time internally, so an extra effort will be required. Since all these fields bend around user login details, my ideas are: AUTH , USER_ACCESS , USER_LOGIN , LOGON INFORMATION_SCHEMA.LOGON sounds the most decent to me, but we won't be able to add more general information about users there.
            nikitamalyavin Nikita Malyavin added a comment - - edited

            Finally I came across with the following implementation:

            • The view will iterate through the acl_users array under a single critical section. Given the fact that nearly the same is done during other operations (like login, etc), it should be fine, and will not disrupt already logged in users' performance.
            • The unprivileged user will see their own info. The access is optimized with binary search.
            • The table name is LOGON.
            • At the same time, password expiration timestamp has been added, so MDEV-32218 can be closed at once.

            serg please review 9e5d4dfc, branch bb-11.5-password-errors

            nikitamalyavin Nikita Malyavin added a comment - - edited Finally I came across with the following implementation: The view will iterate through the acl_users array under a single critical section. Given the fact that nearly the same is done during other operations (like login, etc), it should be fine, and will not disrupt already logged in users' performance. The unprivileged user will see their own info. The access is optimized with binary search. The table name is LOGON. At the same time, password expiration timestamp has been added, so MDEV-32218 can be closed at once. serg please review 9e5d4dfc , branch bb-11.5-password-errors
            alice Alice Sherepa added a comment - - edited

            bb-11.5-password-errors d1ec5274161db821de7ae9ce9be2dd583ef23c2d is ok to push into 11.5.

            Minor problem- if user has SELECT privileges to mysql.global_priv, then he should be able to see also all users after "select * from information_schema.users;" (similar to the situation when the user has SELECT privileges to the whole mysql.* database )

            create user a@localhost;
            		 
            connect(con1, localhost, a);
            		 
             
            		 
            select * from information_schema.users ;
            		 
             
            		 
            --connection default
            		 
            grant select on mysql.global_priv to a@localhost;
            		 
             
            		 
            --connection con1
            		 
            select * from information_schema.users ;
            		 
            select * from mysql.global_priv;
            		 
             
            		 
            --connection default
            		 
            grant select on mysql.* to a@localhost;
            		 
             
            		 
            --connection con1
            		 
            select * from information_schema.users ;
            		 
             
            		 
            --connection default
            		 
            drop user a@localhost;

            alice Alice Sherepa added a comment - - edited bb-11.5-password-errors d1ec5274161db821de7ae9ce9be2dd583ef23c2d is ok to push into 11.5. Minor problem- if user has SELECT privileges to mysql.global_priv, then he should be able to see also all users after "select * from information_schema.users;" (similar to the situation when the user has SELECT privileges to the whole mysql.* database ) create user a@localhost; connect (con1, localhost, a);   select * from information_schema.users ;   --connection default grant select on mysql.global_priv to a@localhost;   --connection con1 select * from information_schema.users ; select * from mysql.global_priv;   --connection default grant select on mysql.* to a@localhost;   --connection con1 select * from information_schema.users ;   --connection default drop user a@localhost;
            nikitamalyavin Nikita Malyavin added a comment -

            serg, it seems that an access to mysql.global_priv is not done as we thought – not through show_global_privileges, which was a common sense, but rather as to a usual table...

            Anyway, I don't see a problem. It's not consistent across implementations (SHOW GRANTS requires select on mysql.*), and we can require a privilege different from mysql.global_priv.

            nikitamalyavin Nikita Malyavin added a comment - serg , it seems that an access to mysql.global_priv is not done as we thought – not through show_global_privileges , which was a common sense, but rather as to a usual table... Anyway, I don't see a problem. It's not consistent across implementations (SHOW GRANTS requires select on mysql.* ), and we can require a privilege different from mysql.global_priv .

            People

              nikitamalyavin Nikita Malyavin
              suresh.ramagiri@mariadb.com suresh ramagiri
              Votes:
              0 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.