| Subject | Re: [firebird-support] Re: SYSDBA |
|---|---|
| Author | Helen Borrie |
| Post date | 2005-04-11T15:34:07Z |
At 02:39 PM 11/04/2005 +0000, you wrote:
great care to make sure that the owner of a database and of the objects in
it are not SYSDBA, if you are going to be in the position where you can't
have SYSDBA access.
because SYSDBA is the owner of your db and all of its objects and, before
you parted with it, you forgot to grant the required privileges to you (as
the ADMIN user, or whatever user you decide to be). The user that creates
the database is the owner of the database and (separately) the user that
creates an object in the database is the owner of that object.
to put your database offline for the time being...).
Restore the gbak backup on your own system, using gbak -c, as
SYSDBA. (actually, if you restore a database as another user, e.g. ADMIN,
then that user becomes the owner of the restored database. Unfortunately,
this doesn't help much, since all of the objects are still owned by SYSDBA.)
Log into the database with isql, as SYSDBA. Do a metadata extract using
isql and output it to a file. That will give you a full script for
reconstructing the database. Log out of isql; then log in again as your
ADMIN user, without connecting to a database.
Edit the script to remove any GRANT statements pertaining to the ADMIN user
(that your host's SYSDBA added previously). Retain any that pertain to
PUBLIC, to roles or to ordinary users.
Run your metadata script to recreate your database (into some other
location, natch!!). You should now have an empty database, of the same
structure as the original, but now ADMIN is the Owner of both the database
and the objects in it.
Next, pump the data from the original database. There are various tools
around for doing this, e.g. the IBPump tool from www.clevercomponents.com
or the very handy datapump tool in IB_SQL (free from
www.ibobjects.com). You will need to log into the original database as
SYSDBA (in case there are other deficient privileges you don't know about)
and you can log into the new database as either SYSDBA or ADMIN (i.e. owner).
Finally - before making a backup under the Owner login, fix up any
privileges that you need to. As Owner of both the database and the
objects, you can grant any privileges that the SYSDBA could.
When you give the gbak file back to the host SYSDBA, ask him to create a
user named ADMIN (or whatever your is) on the server and also VERY STRONGLY
instruct him to perform the restore using gbak -c AND to do so under
your ADMIN login. If he does it as SYSDBA you'll be in another undesirable
situation, where SYSDBA owns the database while your ADMIN user owns all
the objects. Since your user won't be the db owner any more, you won't be
able to do your own backups.
(In fact, if they are hosting several customers' databases, they probably
won't let you have a user named ADMIN. You should actually make a username
that isn't likely to be used by any other customer...ADMIN is a rather
obvious candidate for this type of user and you don't want to be giving
(effectively) superuser access to your database to some other customer!)
One thing your admin user can't do, that SYSDBA can, is create users and
change passwords.
./hb
>--- In firebird-support@yahoogroups.com, "Si Carter" <simon.carter@t..Whoo-ee, you sure can do literally *anything* with it. That's why you take
>.> wrote:
> >
> >
> > > -----Original Message-----
> > > Is the original table created with SYSDBA? If so, you're out
> > > of luck...
>
>YES.
> >
> >
> > The first rule of firebird club: you don't develop using sysdba
> > The second rule of firebird club: YOU DON'T DEVELOP USING SYSDBA
> >
> > :-)
> >
> > I've seen this happen on many occasions, there should be an FAQ
>regarding
> > developing/creating database under SYSDBA account
> >
>So what is the point of having the SYSDBA user if you can't really do
>anything with it?
great care to make sure that the owner of a database and of the objects in
it are not SYSDBA, if you are going to be in the position where you can't
have SYSDBA access.
>I've used oracle previously, and when creating a DB you use the ORACLEPrecisely the same is *possible" with FB. However, you got into your bind
>user and create, db, tables, etc.. If you then assign dba privileges
>to another user, the other user can then create tables, add columns,
>etc..
because SYSDBA is the owner of your db and all of its objects and, before
you parted with it, you forgot to grant the required privileges to you (as
the ADMIN user, or whatever user you decide to be). The user that creates
the database is the owner of the database and (separately) the user that
creates an object in the database is the owner of that object.
>Is this a bug?Not in the creation rules. :-)
>If so, then I agree there should be a FAQ on this subject. So if I have toGet the host SYSDBA to make a gbak backup for you (and, presumably, ask him
>recreate all my tables under this other DBA
>equivalent user, how do I export the data from the old db and import
>it into the new?
to put your database offline for the time being...).
Restore the gbak backup on your own system, using gbak -c, as
SYSDBA. (actually, if you restore a database as another user, e.g. ADMIN,
then that user becomes the owner of the restored database. Unfortunately,
this doesn't help much, since all of the objects are still owned by SYSDBA.)
Log into the database with isql, as SYSDBA. Do a metadata extract using
isql and output it to a file. That will give you a full script for
reconstructing the database. Log out of isql; then log in again as your
ADMIN user, without connecting to a database.
Edit the script to remove any GRANT statements pertaining to the ADMIN user
(that your host's SYSDBA added previously). Retain any that pertain to
PUBLIC, to roles or to ordinary users.
Run your metadata script to recreate your database (into some other
location, natch!!). You should now have an empty database, of the same
structure as the original, but now ADMIN is the Owner of both the database
and the objects in it.
Next, pump the data from the original database. There are various tools
around for doing this, e.g. the IBPump tool from www.clevercomponents.com
or the very handy datapump tool in IB_SQL (free from
www.ibobjects.com). You will need to log into the original database as
SYSDBA (in case there are other deficient privileges you don't know about)
and you can log into the new database as either SYSDBA or ADMIN (i.e. owner).
Finally - before making a backup under the Owner login, fix up any
privileges that you need to. As Owner of both the database and the
objects, you can grant any privileges that the SYSDBA could.
When you give the gbak file back to the host SYSDBA, ask him to create a
user named ADMIN (or whatever your is) on the server and also VERY STRONGLY
instruct him to perform the restore using gbak -c AND to do so under
your ADMIN login. If he does it as SYSDBA you'll be in another undesirable
situation, where SYSDBA owns the database while your ADMIN user owns all
the objects. Since your user won't be the db owner any more, you won't be
able to do your own backups.
(In fact, if they are hosting several customers' databases, they probably
won't let you have a user named ADMIN. You should actually make a username
that isn't likely to be used by any other customer...ADMIN is a rather
obvious candidate for this type of user and you don't want to be giving
(effectively) superuser access to your database to some other customer!)
One thing your admin user can't do, that SYSDBA can, is create users and
change passwords.
./hb