Professional Web Applications Themes

How to enforce disk quotas for MySQL users? - Linux / Unix Administration

Hi all. I am reposting this as it was never replied to and has gotten lost in the realm of old posts by now... I would crosspost to alt.linux and/or alt.os.linux but those groups are all but useless anymore as they become swamped in the mire of usenet troll-osity. So here is my problem: I want to be able to enforce disk quotas on my MySQL users. Specifically, I have disk quotas working fine for normal users but for those users with MySQL accounts there are (obvious) problems. * MySQL has no "quota" system * MySQL process runs as user:group ...

  1. #1

    Default How to enforce disk quotas for MySQL users?

    Hi all. I am reposting this as it was never replied to and has
    gotten lost in the realm of old posts by now... I would crosspost to
    alt.linux and/or alt.os.linux but those groups are all but useless anymore
    as they become swamped in the mire of usenet troll-osity.

    So here is my problem:

    I want to be able to enforce disk quotas on my MySQL users. Specifically,
    I have disk quotas working fine for normal users but for those users with
    MySQL accounts there are (obvious) problems.
    * MySQL has no "quota" system
    * MySQL process runs as user:group mysql:mysql and thus enforcing quotas
    on this user will affect *all* MySQL users.

    I tried this, which *almost* worked, (with example user "x"):
    * move data files to directory in user x's home directory
    * chown all data fils to x:mysql
    * simlink to the data directory from the MySQL default data directory

    I say this almost works because it does, in fact, enforce quotas for those
    files owned by x. However, new tables create new .frm, .MYD, and .MYI
    files which are owned by mysql:mysql! Thus the quota is not enforced on
    any newly created database tables.

    I also tried setting the SUID bit to force the ownership of files in that
    directory but that does not work either. After some research, I found that
    it may work on *BSD systems, but I am using Linux.

    Setting SGID worked -- it forced group ownership on new files -- but I can't
    see how that will help me enforce *user* quotas.

    Things that are *not* options (so please don't suggest them):
    * using another operating system
    * I had another one but i forgot it, darnnit. :)

    And I almost forgot, relevant system details:
    * RHL9/kernel 2.4.20-8
    * MySQL 3.23.56

    Anyways, any help is always appreciated! Thanks in advance...
    --
    Jeffrey D. Silverman | jeffrey AT jhu DOT edu
    Johns Hopkins University | Baltimore, MD
    Website | [url]http://www.wse.jhu.edu/newtnotes/[/url]

    Jeffrey Silverman Guest

  2. #2

    Default Re: How to enforce disk quotas for MySQL users?

    "Jeffrey Silverman" <jeffreyjhu.edu> wrote in
    news:pan.2003.07.07.16.56.14.196407jhu.edu:

    [...]
    >
    > I tried this, which *almost* worked, (with example user "x"):
    > * move data files to directory in user x's home directory
    > * chown all data fils to x:mysql
    > * simlink to the data directory from the MySQL default data directory
    >
    > I say this almost works because it does, in fact, enforce quotas for
    > those files owned by x. However, new tables create new .frm, .MYD, and
    > .MYI files which are owned by mysql:mysql! Thus the quota is not
    > enforced on any newly created database tables.
    >
    > I also tried setting the SUID bit to force the ownership of files in
    > that directory but that does not work either. After some research, I
    > found that it may work on *BSD systems, but I am using Linux.
    >
    > Setting SGID worked -- it forced group ownership on new files -- but I
    > can't see how that will help me enforce *user* quotas.
    SUID has nothing to do with enforcing ownership of files, it is used to
    run a program as superuser no matter who you are.
    GUID has nothing to do with enforcing group ownership of files, it is
    used to run a program as superuser no matter who you are if you are in
    the same group as the program is chown'ed
    > Things that are *not* options (so please don't suggest them):
    > * using another operating system
    > * I had another one but i forgot it, darnnit. :)
    >
    You could run multiple mysql daemons foreach user with -u user, listening
    on a different port and having a seperate data directory.


    P.Krumins
    Peteris Krumins Guest

  3. #3

    Default Re: How to enforce disk quotas for MySQL users?

    On Mon, 07 Jul 2003 19:05:16 +0000, Peteris Krumins wrote:

    <snip!>
    > SUID has nothing to do with enforcing ownership of files, it is used to
    > run a program as superuser no matter who you are.
    > GUID has nothing to do with enforcing group ownership of files, it is
    > used to run a program as superuser no matter who you are if you are in
    > the same group as the program is chown'ed
    <snip!>

    Thanks. I know that is true for SUID on programs and executables, but I
    was under the impression that for *directories*, SUID will force files
    created in that directory to the owner of the directory.

    I have read this is true on FreeBSD. Is it true on Linux? Is it a security
    hole?

    later...

    --
    Jeffrey D. Silverman | jeffrey AT jhu DOT edu
    Johns Hopkins University | Baltimore, MD
    Website | [url]http://www.wse.jhu.edu/newtnotes/[/url]

    Jeffrey Silverman Guest

  4. #4

    Default Re: How to enforce disk quotas for MySQL users?

    In comp.unix.admin Jeffrey Silverman <jeffreyjhu.edu> wrote:
    > On Mon, 07 Jul 2003 19:05:16 +0000, Peteris Krumins wrote:
    > <snip!>
    >> SUID has nothing to do with enforcing ownership of files, it is used to
    >> run a program as superuser no matter who you are.
    >> GUID has nothing to do with enforcing group ownership of files, it is
    >> used to run a program as superuser no matter who you are if you are in
    >> the same group as the program is chown'ed
    > <snip!>
    > Thanks. I know that is true for SUID on programs and executables, but I
    > was under the impression that for *directories*, SUID will force files
    > created in that directory to the owner of the directory.
    SGID will cause files in a directory to be associated with the
    same group as the directory itself *if* the creator is a member
    of that group (if she could chgrp the file thereto).

    This should not be a security issue (the user could have manually
    chgrp'd the file). However, it is dependent on your mount options.
    So you should read you mount man page and do some tests before
    relying on these semantics in any particular case.

    As for as I know SUID has no meaning on directories. On the other hand,
    the sticky bit now has semantics associated with directories and
    none on files. Perhaps someone will come up with a truly clever use for
    these stray bits. It'll be amusing to check in again on that in about
    20 years. :)

    (About 12 years ago I predicted that Microsoft would store "symlinks"
    and meta data (long filenames, permissions, etc) in "volume labels."
    That's because every node in a FAT filesystem has a "volume label"
    bit that says that it's NOT *the* volume label. Every node except
    for one --- which really is the VL. I figured they adopt a policy
    that one (the first v-flagged entry in the root directory) would
    be the volume label, and the rest would be put to other users. I drew
    this conclusion based on experiments with various utility suites (I worked
    at the Peter Norton Group at the time) and noting that nothing broke when
    I put a number of extra volume labels with extraneous junk in them into
    my directories, using a disk editor).
    > I have read this is true on FreeBSD. Is it true on Linux? Is it a security
    > hole?
    I think you misread it. I think it only applies to SGID and group
    association.
    > later...
    --
    Jim Dennis,
    Starshine: Signed, Sealed, Delivered

    James T. Dennis Guest

  5. #5

    Default Re: How to enforce disk quotas for MySQL users?

    Hi Jeff...

    I read your first post a week ago(?) and found it an interesting problem.
    I also discussed it with a friend who deals with this sort of thing all
    the time. There are several solutions (not neccessarilly listed in the
    order of simplicity):

    1. My friend uses OpenAFS ([url]http://www.openafs.org[/url]) and can enforce quotas
    by using the built in quota support in OpenAFS.

    2. Create a disk partition for each user and symlink the MySQL database
    files to that partition. That is to say /home/someuser is mounted as a
    partition and their /var/lib/mysql/databasename/ folder is symlinked to
    something like /home/someuser/db/databasename. This would prevent their
    database from growing without bound (until their home partition is
    filled). Other advantage is that it won't crpwd the other users.
    Disadvantage is that it will make adding new users a tad more complicated
    and if you have a lot of users it can make it a LOT more complicated.

    3. Create a logical volume (LVM) for each user. See:
    [url]http://tldp.org/HOWTO/LVM-HOWTO/[/url]
    This would create a logical partition (really a volume) that can be easily
    expanded (but NOT contracted). Use symlinks as in 2. above.

    Hope that helps.

    -DU-...etc...
    David Utidjian Guest

  6. #6

    Default Re: How to enforce disk quotas for MySQL users?

    In comp.unix.admin David Utidjian <utidjiannospamremarque.org> wrote:
    > Hi Jeff...
    ....
    > 3. Create a logical volume (LVM) for each user. See:
    > [url]http://tldp.org/HOWTO/LVM-HOWTO/[/url]
    > This would create a logical partition (really a volume) that can be easily
    > expanded (but NOT contracted). Use symlinks as in 2. above.
    Whether or not a filesystem can be contracted is dependent on which
    filesystem you use. LVM creates logical volumes which act like
    block devices (partitions) --- and thus can be used to hold any
    Linux fs type (using the normal mkfs commands). After you lvextend;
    then you have to call on a resizing you utility to expand the fs
    that's on the LV. (I gather that one can must mount JFS with an
    option to have it automatically resize itself, to fill it's volume).
    Conversely, when you want to lvreduce a volume's sizes you *first*
    have to call a resize utility (usually have unmounting and fsck'ing it).
    > Hope that helps.
    > -DU-...etc...
    --
    Jim Dennis,
    Starshine: Signed, Sealed, Delivered

    James T. Dennis Guest

  7. #7

    Default Re: How to enforce disk quotas for MySQL users?

    in comp.unix.admin i read:
    >I want to be able to enforce disk quotas on my MySQL users.
    >I tried this, which *almost* worked, (with example user "x"):
    > * move data files to directory in user x's home directory
    > * chown all data fils to x:mysql
    > * simlink to the data directory from the MySQL default data directory
    >Setting SGID worked -- it forced group ownership on new files -- but I can't
    >see how that will help me enforce *user* quotas.
    one group per user, with on that user as a member, the db directory set
    sgid that group with a *group* quota applied.

    --
    a signature
    those who know me have no need of my name Guest

  8. #8

    Default Re: How to enforce disk quotas for MySQL users?

    On Sat, 12 Jul 2003 02:53:54 +0000, those who know me have no need of my
    name wrote:
    > in comp.unix.admin i read:
    >
    >>I want to be able to enforce disk quotas on my MySQL users.
    >
    >>I tried this, which *almost* worked, (with example user "x"):
    >> * move data files to directory in user x's home directory * chown all
    >> data fils to x:mysql
    >> * simlink to the data directory from the MySQL default data directory
    >
    >>Setting SGID worked -- it forced group ownership on new files -- but I
    >>can't see how that will help me enforce *user* quotas.
    >
    > one group per user, with on that user as a member, the db directory set
    > sgid that group with a *group* quota applied.
    Now that sounds like a good idea.

    except...

    hard to manage/not scalable, maybe. but I'll try it, thanks.
    --
    Jeffrey D. Silverman | jeffrey AT jhu DOT edu
    Johns Hopkins University | Baltimore, MD
    Website | [url]http://www.wse.jhu.edu/newtnotes/[/url]

    Jeffrey Silverman Guest

  9. #9

    Default Re: How to enforce disk quotas for MySQL users?

    Hi Jeff...

    I read your first post a week ago(?) and found it an interesting problem.
    I also discussed it with a friend who deals with this sort of thing all
    the time. There are several solutions (not neccessarilly listed in the
    order of simplicity):

    1. My friend uses OpenAFS ([url]http://www.openafs.org[/url]) and can enforce quotas
    by using the built in quota support in OpenAFS.

    2. Create a disk partition for each user and symlink the MySQL database
    files to that partition. That is to say /home/someuser is mounted as a
    partition and their /var/lib/mysql/databasename/ folder is symlinked to
    something like /home/someuser/db/databasename. This would prevent their
    database from growing without bound (until their home partition is
    filled). Other advantage is that it won't crpwd the other users.
    Disadvantage is that it will make adding new users a tad more complicated
    and if you have a lot of users it can make it a LOT more complicated.

    3. Create a logical volume (LVM) for each user. See:
    [url]http://tldp.org/HOWTO/LVM-HOWTO/[/url]
    This would create a logical partition (really a volume) that can be easily
    expanded (but NOT contracted). Use symlinks as in 2. above.

    Hope that helps.

    -DU-...etc...
    David Utidjian Guest

Similar Threads

  1. how to see users logged into mysql?
    By nousernospam in forum MySQL
    Replies: 4
    Last Post: December 13th, 05:42 PM
  2. Disk Quotas
    By josh in forum Windows Server
    Replies: 1
    Last Post: June 17th, 07:31 PM
  3. setting quotas for 200 users
    By Tony in forum Linux / Unix Administration
    Replies: 2
    Last Post: February 21st, 03:07 AM
  4. MySQL and 80,000+ users
    By Mohamed Hosam in forum ASP Database
    Replies: 13
    Last Post: December 15th, 03:38 PM
  5. php.ini causes disk thrashing in mysql
    By Marin in forum PHP Development
    Replies: 0
    Last Post: June 27th, 03:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139