2 Nov
2018
2 Nov
'18
11 a.m.
I'm working on a new setup where we would like to used stored procedures
for an
extra layer of security, in addition to having a where-clause to limit
who can use
a perdition-server.
Has anyone made any patches to perdition / perditiondb_mysql to allow
for either
of these scenarios?
Examples:
1) Use a stored procedure to return a result instead of giving the
perdition mysql user
access to the whole database.
If I would have a table called "users" with the fields "email" and
"mailserver" I could map
these into perdition expected fields
CREATE PROCEDURE perdition_lookup (parameter_user VARCHAR(128))
BEGIN
SELECT email AS user, mailserver AS servername FROM users WHERE email
= parameter_user LIMIT 1
END;
Then perdition_mysql could be set up with CALL perdition_lookup(\U) or
something similar.
2) We will use multiple perdition servers and only some users should be
allowed to use
one of the servers (while all of them are allowed to use the second
one).
The addition of a WHERE-clause in the configuration would help make this
possible.
Example:
map_library_opt
dbserver:3306:dbPerdition:tblPerdition:perdition:perditionpassword:mailserver:email:NULL:SomeExtraField='Y'
Or for full flexibility allow overriding of the query like postfix or
dovecot:
# Perdition expects the fields "user", "servername" and
"port" to be
retured by the query. %u will be substituted
# for what the user provided as login information
map_library_query SELECT email AS user, mailserver AS servername, NULL
as port FROM users WHERE email = '%u' AND SomeExtraField='Y'
As an alternative, could I cheat with using query_key?
query_key \U-Y
map_library_opt
dbserver:3306:dbPerdition:tblPerdition:perdition:perditionpassword:mailserver:CONCAT(email,'-',SomeExtraField):NULL
--
Harald
5 Nov
5 Nov
3:23 p.m.
Am 02.11.18 um 12:00 schrieb Harald Paulsen:
2) We will use multiple perdition servers and only some users should
be allowed to use
one of the servers (while all of them are allowed to use the second one).
The addition of a WHERE-clause in the configuration would help make
this possible.
Example:
map_library_opt
dbserver:3306:dbPerdition:tblPerdition:perdition:perditionpassword:mailserver:email:NULL:SomeExtraField='Y'
If I was forced to implement something like this, I would create a
good-named VIEW in the database with the extra WHERE clause (given that
it is static). Of course it depends on where you prefer to have the
"business logic" of the decision who may use which server. This gives
some additional obstacle that the perdition database user cannot
accidentally be granted with write privilege to the actual table, and if
you ever have to change something in the table, you do not need to adapt
the perdition config.
Regards
Matthias
6 Nov
6 Nov
12:50 p.m.
New subject: [SOLVED] MySQL/MariaDB stored procedure or sql where clause
On 2018-11-05 16:23, Matthias Hunstock wrote:
If I was forced to implement something like this, I
would create a
good-named VIEW in the database with the extra WHERE clause (given that
it is static). Of course it depends on where you prefer to have the
"business logic" of the decision who may use which server. This gives
some additional obstacle that the perdition database user cannot
accidentally be granted with write privilege to the actual table, and
if
you ever have to change something in the table, you do not need to
adapt
the perdition config.
Thank you Matthias!
I can't believe I had a blind spot for this feature.. A view would solve
most
of my worries, and keep the logic in the server where it belongs.
Something as simple as this:
CREATE VIEW tblPerdition (user,servername) AS SELECT email,mailserver
FROM users WHERE somecondition
Then perdition can use its defaults for tablename and column names.
kind regards,
Harald
2001
days inactive
2005
days old
2 comments
2 participants
participants (2)
-
Harald Paulsen -
Matthias Hunstock