[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