[GLLUG] Re: MySql: Database inside of a Database
Szymon Machajewski
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';
flush privileges;
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
accordingly.
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
or
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;
http://www.mysql.com/doc/en/CREATE_TABLE.html
http://www.mysql.com/doc/en/GRANT.html
http://www.mysql.com/doc/en/Adding_users.html
Sincerely,
Szymon Machajewski MCSD, CNA, MySQL Core
Grand Rapids Community College
Open Sourceror
>>> "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
wants
> the stats, I can just delete the database and not have to go through
> deleting 5 to 10 tables.
http://www.mysql.com/doc/en/Client-Side_Scripts.html
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
/var/db/mysql.
cd /var/db/mysql
cp -pR statdb stat1
or
cd /var/db/mysql
rm -Rf stat1
> If this is possible, how can I grant a user name and password so
that
> they can do every thing on statdb and included databases but leave
the
> rest of the databases alone?
http://www.mysql.com/doc/en/Adding_users.html
http://www.mysql.com/doc/en/GRANT.html
You can create an automated script to do this:
Just specify the right command sequence inside
grant_privileges.sh
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
password requirement.
For a really programmatic approach, Perl's DBI interface
to MySQL works quite well.
Regards,
Scott
_______________________________________________
linux-user mailing list
linux-user at egr.msu.edu
http://www.egr.msu.edu/mailman/listinfo/linux-user
More information about the linux-user
mailing list