Solved

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

Posted on 2002-05-13
7
2,185 Views
Last Modified: 2013-12-09
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
Comment
Question by:mrk_raj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 4

Expert Comment

by:YodaMage
ID: 7006775
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
 

Author Comment

by:mrk_raj
ID: 7007594
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
 
LVL 4

Expert Comment

by:YodaMage
ID: 7008619
Did you add the "with grant option;"?
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

Author Comment

by:mrk_raj
ID: 7008772
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
 
LVL 2

Accepted Solution

by:
bego earned 100 total points
ID: 7014209
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
 
LVL 1

Expert Comment

by:thegroup
ID: 7027844
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
 

Author Comment

by:mrk_raj
ID: 7038318
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

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Moving Oracle Database to other server 2 47
ms/access programmatically get a list of fils and folders 3 48
mysql vs miscrosoft sql server 6 62
comparing two rows 10 40
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question