Bugs item #2782202, was opened at 2009-04-27 17:05 Message generated for change (Comment added) made by sjoerd You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2782202&group_id=56967
Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: SQL/Core Group: SQL CVS Head >Status: Closed >Resolution: Fixed Priority: 5 Private: No Submitted By: Roberto Cornacchia (cornuz) Assigned to: Sjoerd Mullender (sjoerd) Summary: SQL: dump&restore fully usable only by admin Initial Comment: When properly using a user and a schema, rather than 'monetdb' user and the 'sys' schema, the dump and restore functionality becomes unusable. In principle, a user should be able to dump and restore all the data he's allowed to manage, but this isn't possible at the moment. # assume you have an empty database 'test' running # executes mclient as admin $ MCLIENT_SQL_ROOT="mclient -lsql -dtest" # executes mclient as user 'lhm $ MCLIENT_SQL_LHM="mclient -lsql -dtest -ulhm -Plhm" # as admin, create a user 'lhm', and a schema 'lhm' (default for the user) $ echo "CREATE USER \"lhm\" WITH PASSWORD 'lhm' NAME 'LHM user' SCHEMA \"sys\";" | $MCLIENT_SQL_ROOT $ echo "CREATE SCHEMA \"lhm\" AUTHORIZATION \"lhm\";" | $MCLIENT_SQL_ROOT $ echo "ALTER USER \"lhm\" SET SCHEMA \"lhm\";" | $MCLIENT_SQL_ROOT # here the user may create and populate tables.. # dump the database as admin # Restoring this dump, the admin can recreate the original database, user, and schema $ $MCLIENT_SQL_ROOT --dump START TRANSACTION; CREATE USER "lhm" WITH PASSWORD '<cannot be dumped>' NAME 'LHM user' SCHEMA "sys"; CREATE SCHEMA "lhm" AUTHORIZATION "lhm"; ALTER USER "lhm" SET SCHEMA "lhm"; COMMIT; # dump the database as user 'lhm' # Restoring this dump fails # Problem n. 1: incomplete dump. The user was apparently not allowed to dump all the necessary information # Problem n. 2: the user would not be allowed to recreate the schema 'lhm' anyway $ $MCLIENT_SQL_LHM --dump START TRANSACTION; CREATE SCHEMA lhm; SET SCHEMA lhm; COMMIT; ---------------------------------------------------------------------- >Comment By: Sjoerd Mullender (sjoerd) Date: 2009-06-11 19:06 Message: When you replay the scenario given in the bug report, you now get as output to the last dump: START TRANSACTION; SET SCHEMA "lhm"; COMMIT; This dump *can** be executed by the user (if the user was already created etc.). This issue is covered by test sql/src/test/UserDump create. (Also, the admin dump now dumps the password in such a way that it can be restored without in any way revealing the password.) Closing as fixed. ---------------------------------------------------------------------- Comment By: Niels Nes (nielsnes) Date: 2009-05-13 22:48 Message: the output of the dump is different. I guess this is related to the dump library, ie reassigning to sjoerd. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2782202&group_id=56967 ------------------------------------------------------------------------------ Crystal Reports - New Free Runtime and 30 Day Trial Check out the new simplified licensing option that enables unlimited royalty-free distribution of the report engine for externally facing server and web deployment. http://p.sf.net/sfu/businessobjects _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
