Some questions and problems evaluating Securich 0.2.0
lenz.g...@googlemail.com
I'm currently toying around with Securich and I have stumbled over
some quirks and have a few questions. I am currently using MySQL 5.5.0-
m2 on OpenSolaris (using MySQL Sandbox for testing). I will try to
reproduce this with 5.1.41 as well, just to make sure it's not a
version issue.
The Securich install script downloaded version 0.2.0, which seems to
have changed a number of commands. Looks like the Tutorial and Docs
still need to be updated to reflect these changes? Also, what's the
rationale behind having this staged installation process? I'd prefer
to download both the installation script and the latest version of
securich in a single tarball. This makes it easier to perform the
installation on hosts that can't establish Internet connections by
themselves (e.g. in a DMZ).
First of all a basic question: how do I delete a user account? There
does not seem to be a dedicated routine for this task. Is there a
recommended way? Or do you recommend to just revoke all privileges but
keep the user account? Do I need to use Securich for this at all?
Even though I was able to create a new role and assign it to a new
user, I don't see it when using the show_full_user_entries() routine:
mysql> CALL create_update_role('add','standard','select');
Query OK, 1 row affected (0.06 sec)
mysql> CALL create_update_role('add','standard','insert');
Query OK, 1 row affected (0.06 sec)
mysql> CALL create_update_role('add','standard','update');
Query OK, 1 row affected (0.06 sec)
mysql> CALL create_update_role('add','standard','delete');
Query OK, 1 row affected (0.06 sec)
mysql> CALL show_privileges_in_roles('standard');
+-----------+
| PRIVILEGE |
+-----------+
| DELETE |
| INSERT |
| SELECT |
| UPDATE |
+-----------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL grant_privileges('benutzer1', 'localhost', 'db1', 'all',
'', 'standard', 'benu...@example.com');
+--------------------------------------------------------------------------------------------------------
+
|
USER_PASSWORD
|
+--------------------------------------------------------------------------------------------------------
+
| Password for user -- benutzer1 -- contactable at --
benu...@example.com -- is – e06aa6912b0ce3 -- |
+--------------------------------------------------------------------------------------------------------
+
1 row in set (0.44 sec)
mysql> CALL show_user_list();
+-----------+-----------+
| USERNAME | HOSTNAME |
+-----------+-----------+
| benutzer1 | localhost |
+-----------+-----------+
1 row in set (0.00 sec)
mysql> call show_full_user_entries('benutzer1');
Empty set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
I can log in as this user, but MySQL does not permit access to the
"db1" database. I assume the role hasn't been assigned properly, or am
I doing anything wrong?
(I just realized that I was performing these tasks using the msandbox
user (not root) - I will try to reproduce it using root... Strange
that I didn't get an error!)
I am also having some trouble in changing a user password. I created
the user "benutzer1" as above and gave him execute privileges on the
set_password() routine included in the securich database:
mysql> CALL create_update_role('add','pwadm','execute');
Query OK, 1 row affected (0.08 sec)
mysql> CALL show_privileges_in_roles('pwadm');
+-----------+
| PRIVILEGE |
+-----------+
| EXECUTE |
+-----------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> CALL grant_privileges('benutzer1', 'localhost', 'securich',
'set_password', 'storedprocedure', 'pwadm', 'benu...@example.com');
Query OK, 0 rows affected (0.27 sec)
(I wonder if it would make sense to create dedicated routines that
allow a DBA to quickly assign the privileges of changing one's
password or running my_privileges() to individual users?)
When logging in as this user, I can call the routine, but it does not
allow me to change my password, even though it matches the
requirements. The error message is a bit confusing:
mysql> CALL set_password
('benutzer1','localhost','e06aa6912b0ce3','12345_abcde');
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
| CONCAT("Invalid password - Password must be at least " ,
PASSWORDLENGTH , " characters long and include at least a number, a
character and one of the following !\"$%^&*()-_=+[]{}\'@;:#~,.<>/
\?|") |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
| Invalid password - Password must be at least 10 characters long and
include at least a number, a character and one of the following !"$
%^&*()-_=+[]{}'@;:#~,.<>/?| |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
1 row in set (0.00 sec)
Running the same command as the root user works fine, however.
Darren Cassar
I'm currently toying around with Securich and I have stumbled over
some quirks and have a few questions. I am currently using MySQL 5.5.0-
m2 on OpenSolaris (using MySQL Sandbox for testing). I will try to
reproduce this with 5.1.41 as well, just to make sure it's not a
version issue.
The Securich install script downloaded version 0.2.0, which seems to
have changed a number of commands. Looks like the Tutorial and Docs
still need to be updated to reflect these changes?
Also, what's the
rationale behind having this staged installation process? I'd prefer
to download both the installation script and the latest version of
securich in a single tarball. This makes it easier to perform the
installation on hosts that can't establish Internet connections by
themselves (e.g. in a DMZ).
First of all a basic question: how do I delete a user account? There
does not seem to be a dedicated routine for this task. Is there a
recommended way? Or do you recommend to just revoke all privileges but
keep the user account? Do I need to use Securich for this at all?
(I just realized that I was performing these tasks using the msandbox
user (not root) - I will try to reproduce it using root... Strange
that I didn't get an error!)
I am also having some trouble in changing a user password. I created
the user "benutzer1" as above and gave him execute privileges on the
set_password() routine included in the securich database:
mysql> CALL create_update_role('add','pwadm','execute');
Query OK, 1 row affected (0.08 sec)
mysql> CALL show_privileges_in_roles('pwadm');
+-----------+
| PRIVILEGE |
+-----------+
| EXECUTE |
+-----------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> CALL grant_privileges('benutzer1', 'localhost', 'securich',
'set_password', 'storedprocedure', 'pwadm', 'benu...@example.com');
Query OK, 0 rows affected (0.27 sec)
(I wonder if it would make sense to create dedicated routines that
allow a DBA to quickly assign the privileges of changing one's
password or running my_privileges() to individual users?)
When logging in as this user, I can call the routine, but it does not
allow me to change my password, even though it matches the
requirements. The error message is a bit confusing:
mysql> CALL set_password
('benutzer1','localhost','e06aa6912b0ce3','12345_abcde');
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
| CONCAT("Invalid password - Password must be at least " ,
PASSWORDLENGTH , " characters long and include at least a number, a
character and one of the following !\"$%^&*()-_=+[]{}\'@;:#~,.<>/
\?|") |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
| Invalid password - Password must be at least 10 characters long and
include at least a number, a character and one of the following !"$
%^&*()-_=+[]{}'@;:#~,.<>/?| |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
1 row in set (0.00 sec)
Running the same command as the root user works fine, however.
Lenz Grimmer
thanks a lot for your reply! This was very helpful.
Darren Cassar wrote:
> Yes indeed .... tutorials mostly are still as per version 0.1.2 (need to
> update them) but documentation should be updated (done last week). If
> you have any specific pages where documentation is outdated, pointing
> them out would be great so I can correct the problems.
This one here, for example:
http://www.securich.com/downloads/securich_tutorial.txt
It's linked from the www.securich.com download page:
http://www.securich.com/downloads.html (the title of this page says
"Security - Downloads" - maybe a typo?).
> You are right ... I will add a third file which includes the latest
> install script and package.
That would be great. Thanks!
> The suggested way, as you correctly deduced is to revoke all rights.
> That way you can re-use a user anytime and it would only show on
> securich (not on mysql). If you think there should be a (delete_user)
> command, we can discuss it and proceed. You could track all the steps
> taken to grant, revoke or suspend rights to any user using the audit
> tables as well. Having a user in securich's tables without it being
> visible in mysql doesn't harm (IMHO), but as I said, we can discuss.
I see. I feel that it might create confusion: "Hmm, the account is still
listed in Securich. Is it really disabled?". One would have to carefully
check the privileges in order to find out if the user is disabled or not.
I think it would make sense to have a dedicated routine that completely
removes a user account and related privileges. It would also reduce
clutter when looking at the output of show_user_list().
Alternatively, show_user_list() should accept an option to only show
blocked/unblocked accounts.
> > Even though I was able to create a new role and assign it to a new
> > user, I don't see it when using the show_full_user_entries() routine:
> > mysql> CALL grant_privileges('benutzer1', 'localhost', 'db1', 'all',
> > '', 'standard', 'benu...@example.com <mailto:benu...@example.com>');
>
> It doesn't matter who you use to grant privileges as long as the user
> has the grant privilege.
I assume the msandbox user does not have that privilege. I would have
expected an error message in that case.
> The error here is the order of the parameters entered ....
> mysql [localhost] {msandbox} (securich) > CALL
> grant_privileges('benutzer1', 'localhost', 'db1', '','all', 'standard',
> 'benu...@example.com <mailto:benu...@example.com>');
>
> My fault here because:
> 1. Documentation might be lacking and old
> 2. No error messages were supplied to you ... but the above is the solution.
Indeed that fixed it - my bad (I should have checked more carefully).
The online help is correct. But I think it's confusing that it explains
the parameters in a different order than how the routine expects them:
[SNIP]
DESCRIPTION:
grant_privileges('username','hostname','databasename','tablename','tabletype','rolename','emailaddress');
(version 0.1.1)
[...]
table type / tablename can be:
tabletype - tablename - description
[SNIP]
As you can see, "table name" and "table type" are the other way around.
It might be worth to consider putting it in the same order.
> (I wonder if it would make sense to create dedicated routines that
> allow a DBA to quickly assign the privileges of changing one's
> password or running my_privileges() to individual users?)
>
> This task is actually in my to do list to grant a user immediate right
> on set_password once created as it makes sense that a user CAN change
> his password by default. It is a future feature though.
Great, good that it's on your radar already! I think it makes sense.
Now I noticed something else when adding that role:
mysql [localhost] {root} (securich) > CALL
show_full_user_entries('benutzer1');
+-----------+-----------+--------------+-----------+----------+-----------+-------+
| USERNAME | HOSTNAME | DATABASENAME | TABLENAME | ROLE |
PRIVILEGE | STATE |
+-----------+-----------+--------------+-----------+----------+-----------+-------+
| benutzer1 | localhost | db1 | | standard | DELETE
| A |
| benutzer1 | localhost | db1 | | standard | INSERT
| A |
| benutzer1 | localhost | db1 | | standard | SELECT
| A |
| benutzer1 | localhost | db1 | | standard | UPDATE
| A |
+-----------+-----------+--------------+-----------+----------+-----------+-------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected, 4 warnings (0.00 sec)
mysql [localhost] {root} (securich) > CALL create_update_role('add',
'pwadm', 'execute');
Query OK, 1 row affected (0.56 sec)
mysql [localhost] {root} (securich) > CALL show_roles();
+----+----------+
| ID | ROLE |
+----+----------+
| 1 | read |
| 2 | write |
| 3 | standard |
| 4 | pwadm |
+----+----------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} (securich) > CALL
show_privileges_in_roles('pwadm');
+-----------+
| PRIVILEGE |
+-----------+
| EXECUTE |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} (securich) >
mysql [localhost] {root} (securich) > CALL grant_privileges('benutzer1',
'localhost', 'securich', 'set_password', 'storedprocedure', 'pwadm',
'benu...@example.com');
Query OK, 0 rows affected (0.27 sec)
mysql [localhost] {root} (securich) > CALL
show_full_user_entries('benutzer1');
+-----------+-----------+--------------+-----------+----------+-----------+-------+
| USERNAME | HOSTNAME | DATABASENAME | TABLENAME | ROLE |
PRIVILEGE | STATE |
+-----------+-----------+--------------+-----------+----------+-----------+-------+
| benutzer1 | localhost | db1 | | standard | DELETE
| A |
| benutzer1 | localhost | db1 | | standard | INSERT
| A |
| benutzer1 | localhost | db1 | | standard | SELECT
| A |
| benutzer1 | localhost | db1 | | standard | UPDATE
| A |
+-----------+-----------+--------------+-----------+----------+-----------+-------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Somehow the new role is not listed. However, the user can call the
routine just fine.
> Running set_password by the root user doesn't restrict the password to
> anything really as a root password is normally temporary (we always want
> a password set by a dba to be changed by the user ASAP.
Ah, good to know. Might make sense to add that to the docs then :)
Or did I miss it?
> version 0.2.0 had a very ugly regex (which is faulty from what I can
> understand --- select newpasswordin REGEXP
> "[[[:alpha:]+][[:digit:]+][[:punct:]+]|[[:alpha:]+][[:punct:]+][[:digit:]+]|[[:punct:]+][[:alpha:]+][[:digit:]+]|[[:punct:]+][[:digit:]+][[:alpha:]+]|[[:digit:]+][[:alpha:]+][[:punct:]+]|[[:digit:]+][[:punct:]+][[:alpha:]+]]")
> = 0 OR (select length(newpasswordin)) < 10 ) and ROOTUSER <> 1
> /*newpasswordin = ''*/ THEN
>
> but in 0.2.1 it worked fine. Feel free to just grab stored procedure
> set_password from the trunk (o.2.1) and run it on securich so you have
> the latest finest checks but you'd have to set up a few more changes
> that are a few entries in the sec_config:
>
> INSERT INTO `sec_config` (`PROPERTY`,`VALUE`) values
> ('password_length_check',1); INSERT INTO `sec_config`
> (`PROPERTY`,`VALUE`) values ('password_dictionary_check',1); INSERT INTO
> `sec_config` (`PROPERTY`,`VALUE`) values ('password_lowercase_check',1);
> INSERT INTO `sec_config` (`PROPERTY`,`VALUE`) values
> ('password_uppercase_check',1); INSERT INTO `sec_config`
> (`PROPERTY`,`VALUE`) values ('password_number_check',1); INSERT INTO
> `sec_config` (`PROPERTY`,`VALUE`) values
> ('password_special_character_check',1); INSERT INTO `sec_config`
> (`PROPERTY`,`VALUE`) values ('password_username_check',1);
>
> and:
> DROP TABLE IF EXISTS `securich`.`sec_dictionary` ;
>
> CREATE TABLE IF NOT EXISTS `securich`.`sec_dictionary` (
> `WORD` VARCHAR(255) )
> ENGINE = MyISAM;
Thanks, I'll look into that next time I have a moment!
> The new set_password can also enable a dictionary check, where the
> password supplied by the user / root is checked against a dictionary for
> common passwords. It is far more configurable and versatile, but still
> needs to be released.
Very cool, I look forward to that!
> Thank you so much for your detailed email and hope the above helps.
My pleasure, thank *you* for the support!
> Email me if you find further problems / have other suggestions.
> PS I'd like to urge you to list any feature requests like the single
> download tar containing both install and latest binaries as well as the
> drop user if you still think it's an important to have.
Will do for sure! I will toy around with it some more and will get back
to you (maybe even with concrete patches).
Happy Holidays!
Bye,
LenZ
--
Lenz Grimmer <le...@grimmer.com> - http://www.lenzg.net/
Lenz Grimmer
a small followup/update:
Darren Cassar wrote:
> but in 0.2.1 it worked fine. Feel free to just grab stored procedure
> set_password from the trunk (o.2.1) and run it on securich so you have
> the latest finest checks but you'd have to set up a few more changes
> that are a few entries in the sec_config:
>
> INSERT INTO `sec_config` (`PROPERTY`,`VALUE`) values
> ('password_length_check',1); INSERT INTO `sec_config`
> (`PROPERTY`,`VALUE`) values ('password_dictionary_check',1); INSERT INTO
> `sec_config` (`PROPERTY`,`VALUE`) values ('password_lowercase_check',1);
> INSERT INTO `sec_config` (`PROPERTY`,`VALUE`) values
> ('password_uppercase_check',1); INSERT INTO `sec_config`
> (`PROPERTY`,`VALUE`) values ('password_number_check',1); INSERT INTO
> `sec_config` (`PROPERTY`,`VALUE`) values
> ('password_special_character_check',1); INSERT INTO `sec_config`
> (`PROPERTY`,`VALUE`) values ('password_username_check',1);
>
> and:
> DROP TABLE IF EXISTS `securich`.`sec_dictionary` ;
>
> CREATE TABLE IF NOT EXISTS `securich`.`sec_dictionary` (
> `WORD` VARCHAR(255) )
> ENGINE = MyISAM;
That indeed fixed the problem I was experiencing. Now the user was able
to update his password just fine. Thanks! I'd suggest to do a new
release ASAP, so other users won't stumble over these issues (e.g. the
readers of my article ;) )
> The new set_password can also enable a dictionary check, where the
> password supplied by the user / root is checked against a dictionary for
> common passwords. It is far more configurable and versatile, but still
> needs to be released.
And documented ;)
Darren Cassar
lenz.g...@googlemail.com
thanks a lot for your reply.
On Dec 26, 10:51 pm, Darren Cassar <photomalta....@gmail.com> wrote:
> I'll do my best to release 0.2.1 by the end of new year or the first week of
> January.
That would be awesome :)
> So many meals, so many people to catch up with and so little time makes it
> very hard to work whilst back home :).
I undertand, no worries! I'm on Christmas vacation myself, so my time
on the PC
is quite limited these days.
> When is your article going to be released Lenz?
They plan to go to print on January 5th. No idea when it will be
available, probably a week
afterwards.
Thanks!
Bye,
LenZ
Darren Cassar
This one here, for example:
http://www.securich.com/downloads/securich_tutorial.txt
It's linked from the www.securich.com download page:
http://www.securich.com/downloads.html (the title of this page says
"Security - Downloads" - maybe a typo?).
> You are right ... I will add a third file which includes the latestThat would be great. Thanks!
> install script and package.
> The suggested way, as you correctly deduced is to revoke all rights.I see. I feel that it might create confusion: "Hmm, the account is still
> That way you can re-use a user anytime and it would only show on
> securich (not on mysql). If you think there should be a (delete_user)
> command, we can discuss it and proceed. You could track all the steps
> taken to grant, revoke or suspend rights to any user using the audit
> tables as well. Having a user in securich's tables without it being
> visible in mysql doesn't harm (IMHO), but as I said, we can discuss.
listed in Securich. Is it really disabled?". One would have to carefully
check the privileges in order to find out if the user is disabled or not.
I think it would make sense to have a dedicated routine that completely
removes a user account and related privileges. It would also reduce
clutter when looking at the output of show_user_list().
Alternatively, show_user_list() should accept an option to only show
blocked/unblocked accounts.
> > Even though I was able to create a new role and assign it to a new
> > user, I don't see it when using the show_full_user_entries() routine:
> > mysql> CALL grant_privileges('benutzer1', 'localhost', 'db1', 'all',> > '', 'standard', 'benu...@example.com <mailto:benu...@example.com>');
>I assume the msandbox user does not have that privilege. I would have
> It doesn't matter who you use to grant privileges as long as the user
> has the grant privilege.
expected an error message in that case.
> The error here is the order of the parameters entered ....> mysql [localhost] {msandbox} (securich) > CALL
> grant_privileges('benutzer1', 'localhost', 'db1', '','all', 'standard',> 'benu...@example.com <mailto:benu...@example.com>');
>Indeed that fixed it - my bad (I should have checked more carefully).
> My fault here because:
> 1. Documentation might be lacking and old
> 2. No error messages were supplied to you ... but the above is the solution.
The online help is correct. But I think it's confusing that it explains
the parameters in a different order than how the routine expects them:
[SNIP]
DESCRIPTION:
grant_privileges('username','hostname','databasename','tablename','tabletype','rolename','emailaddress');
(version 0.1.1)
[...]
table type / tablename can be:
tabletype - tablename - description
[SNIP]
As you can see, "table name" and "table type" are the other way around.
It might be worth to consider putting it in the same order.
Great, good that it's on your radar already! I think it makes sense.
> (I wonder if it would make sense to create dedicated routines that
> allow a DBA to quickly assign the privileges of changing one's
> password or running my_privileges() to individual users?)
>
> This task is actually in my to do list to grant a user immediate right
> on set_password once created as it makes sense that a user CAN change
> his password by default. It is a future feature though.
> Running set_password by the root user doesn't restrict the password toAh, good to know. Might make sense to add that to the docs then :)
> anything really as a root password is normally temporary (we always want
> a password set by a dba to be changed by the user ASAP.
Or did I miss it?
Darren Cassar
> The error here is the order of the parameters entered ....> mysql [localhost] {msandbox} (securich) > CALL
> grant_privileges('benutzer1', 'localhost', 'db1', '','all', 'standard',> 'benu...@example.com <mailto:benu...@example.com>');
>Indeed that fixed it - my bad (I should have checked more carefully).
> My fault here because:
> 1. Documentation might be lacking and old
> 2. No error messages were supplied to you ... but the above is the solution.
The online help is correct. But I think it's confusing that it explains
the parameters in a different order than how the routine expects them:
[SNIP]
DESCRIPTION:
grant_privileges('username','hostname','databasename','tablename','tabletype','rolename','emailaddress');
(version 0.1.1)
[...]
table type / tablename can be:
tabletype - tablename - description
[SNIP]
As you can see, "table name" and "table type" are the other way around.
It might be worth to consider putting it in the same order.Agreed and fixed (thanks for pointing it out);
Great, good that it's on your radar already! I think it makes sense.
> (I wonder if it would make sense to create dedicated routines that
> allow a DBA to quickly assign the privileges of changing one's
> password or running my_privileges() to individual users?)
>
> This task is actually in my to do list to grant a user immediate right
> on set_password once created as it makes sense that a user CAN change
> his password by default. It is a future feature though.
Created set_my_password procedure for 0.2.1 for the purpose. It basically permits only setting of one's own password not anybody elses which makes it more secure hence any user can be granted privileges on that stored procedure by default.
> The new set_password can also enable a dictionary check, where the
> password supplied by the user / root is checked against a dictionary for
> common passwords. It is far more configurable and versatile, but still
> needs to be released.
Very cool, I look forward to that!
Lenz Grimmer
On 01/01/2010 07:21 PM, Darren Cassar wrote:
> This issue is fixed through a check in the procedure (will advise if the
> parameter is wrong.
Good idea! I'll take a look.
One more thing about the online help - it sometimes also refers to what
parameters older versions of the routines expected. I'd suggest to remove
them, to avoid confusion. IMHO, the docs should refer to the most current
versions of the functions only.
> > Created set_my_password procedure for 0.2.1 for the purpose. It
> > basically permits only setting of one's own password not anybody
> > elses which makes it more secure hence any user can be granted
> > privileges on that stored procedure by default.
Excellent. Thanks!
> This feature has been created and will be released in 0.2.2 (but can
> grant_privileges.sql can be imported directly into 0.2.1 with:
> INSERT INTO `sec_roles` VALUES (3,'execute');
> INSERT INTO `sec_ro_pr` VALUES (6,3,17);
> to make it work immediately.
Will have to check this out, too. Cheers!