• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2460
  • Last Modified:

How to make a user as the owner of an existing database

Hi,
I have a user called MYUSER with all permission to the table COUNTRY which
is created by SYSDBA. MYUSER has all the permission including WITH GRANT,
but  i wont be able to add/delete a field to the table COUNTRY.
How do i get privilage to add a field / delete a field for the user MYUSER
on this  table.
i think i need to make MYUSER the owner of table COUNTRY.

please help me

regards
Raj
0
mrk_raj
Asked:
mrk_raj
1 Solution
 
YodaMageCommented:
try:

grant delete, insert, update, select, reference on country to MYUSER with grant option;

-or-

create role test;
grant delete, insert, update, select, reference on country to test;
grant test to MYUSER;  
0
 
mrk_rajAuthor Commented:
thanks for ur suggetion

but i need to create/Delete a Field in COUNTRY with MYUSER...i can SELECT, DELETE,INSERT ...with this user but can not do anything with DDL statements...

hope u got my point

raj
0
 
YodaMageCommented:
Did you add the "with grant option;"?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
mrk_rajAuthor Commented:
yeassss..i did...then again its giving error
     unsuccessful metadata update
    -STORE RDB$RELATION_FIELDS failed
    -no permission for control access to TABLE tablname

please  help me
regards
raj
0
 
begoCommented:
Hi mrk_raj,

I did some research on that problem and it seems that there's no easy way to do it - the user can do metadata changes only to objects created by himself.

At http://community.borland.com/article/0,1410,25349,00.html the author claims that restoring the database as MYUSER may help - I tried it and the owner of the tables changed - but only for system tables - probably not what we're looking for :-).

At http://www.mers.com/IB_0335.HTML the author claims that editing system tables may help - does not seem to work either. This is what I tried:

E:\Program Files\Firebird\bin>isql -U sysdba -PAS masterkey f:\bak\friday.gdb
Database:  f:\bak\friday.gdb, User: sysdba
SQL> SELECT RDB$RELATION_NAME, RDB$OWNER_NAME FROM
CON> RDB$RELATIONS WHERE RDB$RELATION_NAME = 'FUND';

RDB$RELATION_NAME               RDB$OWNER_NAME

FUND                            SYSDBA

SQL> UPDATE RDB$RELATIONS
CON> SET RDB$OWNER_NAME = 'USER'
CON> WHERE RDB$RELATION_NAME = 'FUND';
SQL> SELECT RDB$RELATION_NAME, RDB$OWNER_NAME FROM
CON> RDB$RELATIONS WHERE RDB$RELATION_NAME = 'FUND';

RDB$RELATION_NAME               RDB$OWNER_NAME

FUND                            USER

So far, so good.

SQL> EXIT;

E:\Program Files\Firebird\bin>isql -U user -PAS password f:\bak\friday.gdb
Database:  f:\bak\friday.gdb, User: user
SQL> ALTER TABLE FUND ADD FOO VARCHAR(10);
Statement failed, SQLCODE = -607

unsuccessful metadata update
-STORE RDB$RELATION_FIELDS failed
-no permission for control access to TABLE FUND
SQL>

No luck. And editing system tables is not a good habit anyway. :-)

So it seems that a user can alter only the tables he created.

HTH,

bego
0
 
thegroupCommented:
The only user authorized to change table structures is SYSDBA or the user that creates the object, without exceptions. If you are not the creator (owner) or SYSDBA you can't modify table structures.
0
 
mrk_rajAuthor Commented:
yeas...i ve also tried all these stuff..but went in vain...
but some experts says u can do with restore/or updation of sys tables..etc...i tried everything...but now i knew..its just impossible....thanks bego......

thanks for all...

regards
raj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now