Skip to content

# Month: September 2014

## Get per-object grants via common_schema

MySQL common_schema Security September 29, 2014

Did you know common_schema supports a complete breakdown of all accounts on your database server? It can provide you with the GRANT statements required to set up an account, the REVOKE statements to undo the former, and this can be broken down on a per-object & per-object-type basis. Consider the sql_grants view:

Find who has privileges on a table called t:

>     select * from common_schema.sql_grants where priv_level_name='table' and object_name='t'\G

>                GRANTEE: 'power1'@'localhost'
>                   user: power1
>                   host: localhost
>             priv_level: `mytst`.`t`
>        priv_level_name: table
>          object_schema: mytst
>            object_name: t
>     current_privileges: SELECT
>           IS_GRANTABLE: NO
>              sql_grant: GRANT SELECT ON `mytst`.`t` TO 'power1'@'localhost'
>             sql_revoke: REVOKE SELECT ON `mytst`.`t` FROM 'power1'@'localhost'
>          sql_drop_user: DROP USER 'power1'@'localhost'

or, who has privileges on the test schema:

>     select * from common_schema.sql_grants where priv_level_name='schema' and object_name='test' \G

>                GRANTEE: 'power1'@'localhost'
>                   user: power1
>                   host: localhost
>             priv_level: `test`.*
>        priv_level_name: schema
>          object_schema: NULL
>            object_name: test
>     current_privileges: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
>           IS_GRANTABLE: NO
>              sql_grant: GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* TO 'power1'@'localhost'
>             sql_revoke: REVOKE ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* FROM 'power1'@'localhost'
>          sql_drop_user: DROP USER 'power1'@'localhost'

>                GRANTEE: 'test'@'localhost'
>                   user: test
>                   host: localhost
>             priv_level: `test`.*
>        priv_level_name: schema
>          object_schema: NULL
>            object_name: test
>     current_privileges: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
>           IS_GRANTABLE: NO
>              sql_grant: GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* TO 'test'@'localhost'
>             sql_revoke: REVOKE ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* FROM 'test'@'localhost'
>          sql_drop_user: DROP USER 'test'@'localhost'

In the same manner, you can easily SELECT for all grants that are defined per-table, per-schema, … priv_level_name: is any one of ‘user’, ‘schema’, ‘table’, ‘column’, ‘routine’.

The above is a view, which aggregates data from all relevant INFORMATION_SCHEMA tables, normalizing and de-normalizing as necessary.

You might also want to look at sql_show_grants which provides with a no-breakdown GRANT for all accounts. It’s like a SHOW ALL GRANTS which doesn’t exist, plus it’s SELECTable.

Also, thanks to eval(), the above make for executable code. Consider: Continue reading » “Get per-object grants via common_schema”

## Three wishes for a new year

MySQL Opinions secondary September 23, 2014September 23, 2014

Another new year by Jewish calendar. What do I wish for the following year?

1. World peace
2. Good health to all
3. Multi-core execution for queries

After having evaluated a few columnar databases, and having seen how a single query gets 24 cores busy, I can’t look at MySQL the same way again. The fact that a single query consumes a single core only doesn’t seem right in the year 2014. Shard-query is a cool application-level attempt to solve the above; I would like to see stuff like this implemented inside the server (or inside the storage engine where possible).

None of my wishes in previous years [2010], [2011], [2012], [2013] came true (and mostly gone worse). I’m still willing to settle for two out of three.

## zookeepercli: lightweight, powerful, controlled command line client for ZooKeeper

Development Linux command line go Open Source ZooKeeper September 17, 2014

I’m happy to announce the availability of zookeepercli: a lightweight, simple, fast and controlled command line client for ZooKeeper.

zookeepercli allows for:

- Basic CRUD-like operations: `create`, `set`, `delete`, `exists`, `get`, `ls` (aka `children`).
- Extended operations: `lsr` (ls recursive), `creater` (create recursively)
- Well formatted and controlled output: supporting either `txt` or `json` format
- Single, no-dependencies binary file, based on a native Go ZooKeeper library by github.com/samuel/go-zookeeper (LICENSE)

I was dissatisfied with existing command line access to ZooKeeper. Uncontrolled and noisy output as well as large footprint were among the reasons. zookeepercli overcomes the above and provides with often required powers.

Usage samples:

>     $ zookeepercli --servers srv-1,srv-2,srv-3 -c create /demo_only "path placeholder"
>     $ zookeepercli --servers srv-1,srv-2,srv-3 -c create /demo_only/key1 "value1"
>     $ zookeepercli --servers srv-1,srv-2,srv-3 -c create /demo_only/key2 "value2"
>     $ zookeepercli --servers srv-1,srv-2,srv-3 -c create /demo_only/key3 "value3"

>     $ zookeepercli --servers srv-1,srv-2,srv-3 -c ls /demo_only
>     key3
>     key2
>     key1

>     # Same as above, JSON format output:
>     $ zookeepercli --servers srv-1,srv-2,srv-3 --format=json -c ls /demo_only
>     ["key3","key2","key1"]

>     $ zookeepercli --servers srv-1,srv-2,srv-3 -c delete /demo_only/key1
>     $ zookeepercli --servers srv-1,srv-2,srv-3 -c delete /demo_only/key2
>     $ zookeepercli --servers srv-1,srv-2,srv-3 -c delete /demo_only/key3
>     $ zookeepercli --servers srv-1,srv-2,srv-3 -c delete /demo_only

>     # Create a path recursively (auto-generate parent directories if not exist):
>     $ zookeepercli --servers=srv-1,srv-2,srv-3 -c creater "/demo_only/child/key1" "val1"
>     $ zookeepercli --servers=srv-1,srv-2,srv-3 -c creater "/demo_only/child/key2" "val2"

>     $ zookeepercli --servers=srv-1,srv-2,srv-3 -c get "/demo_only/child/key1"
>     val1

>     # This path was auto generated due to recursive create:
>     $ zookeepercli --servers=srv-1,srv-2,srv-3 -c get "/demo_only"
>     zookeepercli auto-generated

>     # ls recursively a path and all sub children:
>     $ zookeepercli --servers=srv-1,srv-2,srv-3 -c lsr "/demo_only"
>     child
>     child/key1
>     child/key2

zookeepercli is released as open source by Outbrain under the Apache 2.0 license.

Quick links:

- Project page
- Pre-built binaries for download
- License