MySQL Permissions – Default Privileges to Execute Function

MySQLpermissions

I have a user that has select only access but was granted execute on a single function in our mysql db. The issues I'm running into is other more privileged users will drop and create this function periodically which causes my less privileged user to lose the execute privilege they were granted. Is there a way to grant a default privilege just to the one function the user needs so that when it's dropped and created I don't have to re-grant their permissions?

Best Answer

I find no MySQL functionality to achieve what you need, you have to create what you need.

If you use a statement like:

GRANT EXECUTE
ON FUNCTION `yourdatabase`.`yourfunction`
TO 'youruser'@'localhost';

will apply:

14.7.1.4 GRANT Syntax

...

Important

MySQL does not automatically revoke any privileges when you drop a database or table. However, if you drop a routine, any routine-level privileges granted for that routine are revoked.

...

One option is to use a statement such as:

GRANT EXECUTE
ON `yourdatabase`.*
TO 'youruser'@'localhost';

of course, considering that possibly will be giving more privileges to the user really wants to give.

Another option is to have two user (internal and external) and two functions (real and mock), the real function that periodically removed and the mock function that does not eliminate. The external user only has the privilege to EXECUTE mock function and internally mock function call real function and will execute with internal user permissions.

STEP #1

Add these lines just under the [mysqld] group header in my.ini

[mysqld]
skip-grant-tables
skip-networking

STEP 2

Open Windows Command Line as Administrator, and reboot MySQL Service

C:\> net stop mysql
C:\> net start mysql

STEP 3

You can login to MySQL

C:\> mysql <Hit Enter>

STEP 4

From the mysql prompt, run this

SET @PasswordHash = PASSWORD('whateverpasswordiwant');
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = @PasswordHash,
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = '127.0.0.1',
              Password = @PasswordHash,
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
exit

STEP 5

Remove skip-grant-tables and skip-networking from my.ini

STEP 6

Restart MySQL

C:\> net stop mysql
C:\> net start mysql

STEP 7

Try logging into MySQL

GIVE IT A TRY !!!

Postgresql – Reg: Default Privileges on PostgreSQL Schemas

The documentation about ALTER DEFAULT PRIVILEGES tells you a possible reason - it is not that clearly described, though.

Let's see, what is said:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

This means that the default privileges defined by this statement applies only to objects that are created by you (or the role you are a member of). Let's see this in action!

`alice`'s schema

First, we are logged in as alice. Then, in a newly created schema, we create a table and grant some rights to bob:

SELECT current_user;
 current_user 
──────────────
 alice


SHOW search_path ;
 search_path  
──────────────
 test, public

CREATE SCHEMA alicetest;

ALTER DEFAULT PRIVILEGES 
    FOR ROLE alice 
    IN SCHEMA alicetest 
    GRANT ALL ON TABLES TO alice;

GRANT SELECT ON alicetest.a TO bob; 
-- this I do only for showing the privileges - 
-- the owner has by default ALL and is not shown by \dp

\dp alicetest.a
                             Access privileges
  Schema   │ Name │ Type  │  Access privileges  │ Column access privileges 
───────────┼──────┼───────┼─────────────────────┼──────────────────────────
 alicetest │ a    │ table │ alice=arwdDxt/alice↵│ 
           │      │       │ bob=r/alice         │

alice now has all rights on her table, as expected.

`bob`' table in the same schema

Now, after obtaining access to this schema, bob tries to create a table:

SELECT current_user;
 current_user 
──────────────
 bob

CREATE TABLE alicetest.b (id integer);

GRANT SELECT ON alicetest.b TO alice;

\dp alicetest.b
                            Access privileges
  Schema   │ Name │ Type  │ Access privileges │ Column access privileges 
───────────┼──────┼───────┼───────────────────┼──────────────────────────
 alicetest │ b    │ table │ bob=arwdDxt/bob  ↵│ 
           │      │       │ alice=r/bob       │

As you can see, despite creating the table in alice's schema where she set the default privileges, bob's table doesn't have all those permissions. This happens because alice is not a member of bob.

Let's check this membership thing, too, and try to define default privileges by alice again, this time for another role:

ALTER DEFAULT PRIVILEGES 
    FOR ROLE charlie 
    IN SCHEMA alicetest 
    GRANT ALL ON TABLES TO alice;
ERROR:  must be member of role "charlie"

So, some mighty enough user grants her a membership in charlie, then she tries again, with success:

ALTER DEFAULT PRIVILEGES 
    FOR ROLE charlie 
    IN SCHEMA alicetest 
    GRANT ALL ON TABLES TO alice;
ALTER DEFAULT PRIVILEGES

`charlie`'s round

Then charlie creates a new table:

CREATE TABLE alicetest.c (id integer);

And the privileges:

\dp alicetest.c
                               Access privileges
  Schema   │ Name │ Type  │    Access privileges    │ Column access privileges 
───────────┼──────┼───────┼─────────────────────────┼──────────────────────────
 alicetest │ c    │ table │ charlie=arwdDxt/charlie↵│ 
           │      │       │ alice=arwdDxt/charlie   │

As you see, alice, as a member of charlie, gets her access to this table.

To answer your question,

I guess you defined the default privileges for yourself (alice in the example), but you developers act as a bunch of bobs here, not getting the necessary privileges. One way to get around this (as we do it at work) is to do a

SET ROLE TO schema_owner;

every time before creating a new object in the schema. This should be a role that all developers are a member of (otherwise you'd get an error).

NOTE that \dp is a psql command.