Board index » delphi » Table ownership and granting rights.

Table ownership and granting rights.

I created some tables in a database as the SYSDBA but now I want to change
the ownership of those tables to some other user name. Is there a way to do
that without dropping the tables and recreating them logged on to the
database as the other user ? If not, is there a way to grant rights on the
table from IBConsole or do I have to do it through ISQL ?
 

Re:Table ownership and granting rights.


Already done. Doesn't seem as if I can change the owner once the database
has been created, but it does seem that I should be able to do so if I am
the owner.

Quote
"Hung Huynh" <d95h...@dtek.chalmers.se> wrote in message

news:3d87be51@newsgroups.borland.com...
Quote
> Edward Diener wrote:
> > I created some tables in a database as the SYSDBA but now I want to
change
> > the ownership of those tables to some other user name. Is there a way to
do
> > that without dropping the tables and recreating them logged on to the
> > database as the other user ? If not, is there a way to grant rights on
the
> > table from IBConsole or do I have to do it through ISQL ?

> Hi, I had this question myself yesterday :) but then i found the
> SQL-button in the IBConsole toolbar. Just run that SQL with something
> like this:

> GRANT SELECT, DELETE, INSERT ON CUSTOMER TO MY_USER

> The complete syntax for GRANT is in DataDef.pdf. I don't know yet how to
> change the owner of a database though. And only the owner has the right
> to issue GRANT.

> Regards,
> /Hung Huynh

Re:Table ownership and granting rights.


Quote
Edward Diener wrote:
> I created some tables in a database as the SYSDBA but now I want to change
> the ownership of those tables to some other user name. Is there a way to do
> that without dropping the tables and recreating them logged on to the
> database as the other user ? If not, is there a way to grant rights on the
> table from IBConsole or do I have to do it through ISQL ?

Hi, I had this question myself yesterday :) but then i found the
SQL-button in the IBConsole toolbar. Just run that SQL with something
like this:

GRANT SELECT, DELETE, INSERT ON CUSTOMER TO MY_USER

The complete syntax for GRANT is in DataDef.pdf. I don't know yet how to
change the owner of a database though. And only the owner has the right
to issue GRANT.

Regards,
/Hung Huynh

Re:Table ownership and granting rights.


Database ownership and table (object) ownership are two different things.

Database ownership can be changed by creating a backup and restoring it
with a different username.

Object ownership can be changed by logging in with the current owner
name, browsing the RDB$xxx table (for tables: RDB$RELATIONS)
and changing the value in the RDB$OWNER_NAME column.

--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase
http://www.upscene.com

Firebird Workbench - the developer tool for Firebird
http://www.upscene.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

"Edward Diener" <eddielee@ .com> wrote in message
news:3d87d0f3@newsgroups.borland.com...

Quote
> Already done. Doesn't seem as if I can change the owner once the database
> has been created, but it does seem that I should be able to do so if I am
> the owner.

> "Hung Huynh" <d95h...@dtek.chalmers.se> wrote in message
> news:3d87be51@newsgroups.borland.com...
> > Edward Diener wrote:
> > > I created some tables in a database as the SYSDBA but now I want to
> change
> > > the ownership of those tables to some other user name. Is there a way
to
> do
> > > that without dropping the tables and recreating them logged on to the
> > > database as the other user ? If not, is there a way to grant rights on
> the
> > > table from IBConsole or do I have to do it through ISQL ?

> > Hi, I had this question myself yesterday :) but then i found the
> > SQL-button in the IBConsole toolbar. Just run that SQL with something
> > like this:

> > GRANT SELECT, DELETE, INSERT ON CUSTOMER TO MY_USER

> > The complete syntax for GRANT is in DataDef.pdf. I don't know yet how to
> > change the owner of a database though. And only the owner has the right
> > to issue GRANT.

> > Regards,
> > /Hung Huynh

Re:Table ownership and granting rights.


In article <3d88c...@newsgroups.borland.com>, eddie...@tropicsoft.com
says...

Quote
> What establishes database ownership ? The name which is logged onto the
> server when the database is first created ? The username and password in the
> CREATE DATABASE SQL statement ?

        Yes.  (They are the same.)
Quote

> What determines object ownership ? The logged in name for the database ?

        Yes.

        -Craig

--
 Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
     Delphi/InterBase Weblog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Re:Table ownership and granting rights.


Quote
"Martijn Tonies" <m.tonies@upscene_remove.com> wrote in message

news:3d88545a@newsgroups.borland.com...

Quote
> Database ownership and table (object) ownership are two different things.

> Database ownership can be changed by creating a backup and restoring it
> with a different username.

What establishes database ownership ? The name which is logged onto the
server when the database is first created ? The username and password in the
CREATE DATABASE SQL statement ?

Quote

> Object ownership can be changed by logging in with the current owner
> name, browsing the RDB$xxx table (for tables: RDB$RELATIONS)
> and changing the value in the RDB$OWNER_NAME column.

What determines object ownership ? The logged in name for the database ?

Re:Table ownership and granting rights.


"Craig Stuntz [TeamB]" <cstu...@nospamplease.vertexsoftware.com> wrote in
message news:MPG.17f2922754ef5252989da7@newsgroups.borland.com...

Quote
> In article <3d88c...@newsgroups.borland.com>, eddie...@tropicsoft.com
> says...
> > What establishes database ownership ? The name which is logged onto the
> > server when the database is first created ? The username and password in
the
> > CREATE DATABASE SQL statement ?

> Yes.  (They are the same.)

I think they can be different. Can not one can log into a server and then
create a database using a different username-password which is one of the
allowed usernames for the server ? If that is the case, who is the owner.

Quote

> > What determines object ownership ? The logged in name for the database ?

> Yes.

Thanks !

Re:Table ownership and granting rights.


In article <3d88cc6...@newsgroups.borland.com>, eddie...@tropicsoft.com
says...

Quote
> I think they can be different. Can not one can log into a server and then
> create a database using a different username-password which is one of the
> allowed usernames for the server ?

        No, not really.  "Logging into the server is an IBConsole
concept."  All it does is give you a default username/password so that
you don't have to retype it at every action you perform.  Whatever
username/password are used to create the DB *is* the account which is
connected at the time the DB is created.

        -Craig

--
 Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
     Delphi/InterBase Weblog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Re:Table ownership and granting rights.


Quote
"Edward Diener" <eddie...@tropicsoft.com> wrote in message

news:3d88cc68$1@newsgroups.borland.com...

Quote
> "Craig Stuntz [TeamB]" <cstu...@nospamplease.vertexsoftware.com> wrote in
> message news:MPG.17f2922754ef5252989da7@newsgroups.borland.com...
> > In article <3d88c...@newsgroups.borland.com>, eddie...@tropicsoft.com
> > says...
> > > What establishes database ownership ? The name which is logged onto
the
> > > server when the database is first created ? The username and password
in
> the
> > > CREATE DATABASE SQL statement ?

> > Yes.  (They are the same.)

> I think they can be different. Can not one can log into a server and then
> create a database using a different username-password which is one of the
> allowed usernames for the server ? If that is the case, who is the owner.

The one who creates the database is the owner.

--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase
http://www.upscene.com

Firebird Workbench - the developer tool for Firebird
http://www.upscene.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

Re:Table ownership and granting rights.


Quote
"Martijn Tonies" <m.tonies@upscene_remove.com> wrote in message

news:3d88545a@newsgroups.borland.com...

Quote
> Database ownership and table (object) ownership are two different things.

> Database ownership can be changed by creating a backup and restoring it
> with a different username.

> Object ownership can be changed by logging in with the current owner
> name, browsing the RDB$xxx table (for tables: RDB$RELATIONS)
> and changing the value in the RDB$OWNER_NAME column.

Can I change the ownership of the system objects to another owner or should
I only change the ownership of my own tables to the other owner ? Right now
everything is under SYSDBA but I want to change the ownership to another
username, which is what I will use when connecting to the database. Even if
I change the ownership from SYSDBA, isn't it true that the SYSDBA still has
rights to manipulate the other objects ?

The scenario is this. I am creating a database for a Web server application.
The people running the server will be the SYSDBA for the server. I generally
do not want them to have access to my database as it may contain proprietary
information. It's not that I don't trust them since I am pretty sure that
they will not be invading my information, but if I can make sure that the
information in the database is only available to my own username, I would
feel better.

Re:Table ownership and granting rights.


In article <3d8a113...@newsgroups.borland.com>, eddie...@tropicsoft.com
says...

Quote

> Can I change the ownership of the system objects to another owner or should
> I only change the ownership of my own tables to the other owner ?

        In general, don't mess with the system tables.  Even when it
works, it often doesn't survive a backup and restore, and it doesn't
alway work.

Quote
> Right now
> everything is under SYSDBA but I want to change the ownership to another
> username, which is what I will use when connecting to the database. Even if
> I change the ownership from SYSDBA, isn't it true that the SYSDBA still has
> rights to manipulate the other objects ?

        Yes, it's true.  SYSDBA can do just about anything.

Quote

> The scenario is this. I am creating a database for a Web server application.
> The people running the server will be the SYSDBA for the server. I generally
> do not want them to have access to my database as it may contain proprietary
> information. It's not that I don't trust them since I am pretty sure that
> they will not be invading my information, but if I can make sure that the
> information in the database is only available to my own username, I would
> feel better.

        If you want to keep data secure, encrypt it, and don't give anyone
the password.  Anything else can be circumvented, especially if the user
has access to the physical GDB file.

        If you want to keep your metadata secure, the best you can do is:

1.  Delete the source code for your procedures, triggers, domains,
views, and the like from the system table (an exception to what I wrote
above -- this is safe).  IB runs BLR, not source code, so it's OK to do
this.  If you're using IB 6.0.x, you must set the source to an empty
string instead of NULL or IB will run the triggers twice.  If you're
using IB 6.5, NULL is fine.  This actually gives you a good deal of
protection as most people can't be bothered to try and reverse-engineer
BLR, but it's not infallible.

2.  If using IB 6.5 or higher, run the blindmeta.sql script, but note
that some components (etc.) need access to certain system tables, so you
may need to GRANT some of this access back.  This gives you no
protection whatsoever from SYSDBA, only from non-DBA users.

        HTH,

        -Craig

--
 Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
     Delphi/InterBase Weblog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Other Threads