[GLLUG] Re: MySql: Database inside of a Database
SMachaje at grcc.edu
Wed Feb 11 10:20:51 EST 2004
Maybe a simple solution:
grant select on database.* to user@'%' identified by 'password';
You can create a user with limited rights. Replace select with "all"
for all rights or any combination of (select, update, delete etc).
Replace database with the database name. Replace user and password
So this is how you can create a user that will be able to change one
database but no other or will be able to change one table in a database
but not other.
Also you can duplicate a table by running:
insert into new_table select * from old_table
CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;
Szymon Machajewski MCSD, CNA, MySQL Core
Grand Rapids Community College
>>> "Scott Henry Harrison" <harris41 at msu.edu> 2/11/2004 8:06:23 AM >>>
> Is it possible to have a database inside of a database?
Anything is possible, but I am not sure that I recognize what
you are suggesting as a conventional solution.
> Or maybe some one can offer a better solution:
> I would like to do this so that when the stat1's user no longer
> the stats, I can just delete the database and not have to go through
> deleting 5 to 10 tables.
To replicate a database, you can dump out its tables and the
load into new database:
mysqldump -u delta statdb -p > statdb.copy.dump
mysqladmin create stat1
mysql -u root mysql -p < grant_privileges_SEE_BELOW_FOR_HOWTO
mysql -u delta stat1 -p < statdb.copy.dump
To delete a database:
mysqladmin drop stat1
Alternatively, you can replicate and delete databases
underneath DB_DIR, where DB_DIR is something like
cp -pR statdb stat1
rm -Rf stat1
> If this is possible, how can I grant a user name and password so
> they can do every thing on statdb and included databases but leave
> rest of the databases alone?
You can create an automated script to do this:
Just specify the right command sequence inside
mysql -u root mysql -p < grant_privileges.sh
mysqladmin may require a password (and you would need
to then also have -p, like "mysqladmin create somedb -p").
Of course, if you directly copy directories underneath
/var/db/mysql or whatever DB_DIR is (and "violate"
expected usage of mysqladmin), then you can avoid this
For a really programmatic approach, Perl's DBI interface
to MySQL works quite well.
linux-user mailing list
linux-user at egr.msu.edu
More information about the linux-user