Table Ownership Convention

I am trying to shore-up our database permissions and ownerships and I was wondering what kind of convention (if any) exists for table owners. I have seen tables owned by the parent application, root, informix etc etc but I am not sure what the 'best practice' is. Basically we have a single OLTP engine that is accessed almost exclusively by an application server. Thanks in advance. --Ben sending to informix-list

Ben wrote: > I am trying to shore-up our database permissions and ownerships and I was > wondering what kind of convention (if any) exists for table owners. I have > seen tables owned by the parent application, root, informix etc etc but I am > not sure what the 'best practice' is. > > Basically we have a single OLTP engine that is accessed almost exclusively > by an application server. Neither informix nor root should normally own production tables in the database. User informix inevitably owns the system catalog. User root should not normally own anything. So, you can either create a user name to own the tables - based on the application name - or you can let 'real' users own the tables. I would prefer a DBA user specifically set up for the job. Some people have factored things so that dbausr1 owns one set of tables and dbausr2 owns a second set, and so on - that's OK too. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/

I tend to create a 'dba' user for each database and they create all the entities. Then with a little scripting you can easily track who has su'd to the dba user to carry out maintenance work. root can never connect to a database. Is this best practise - no idea - but it works for me and has done for years. Ben wrote: > I am trying to shore-up our database permissions and ownerships and I was > wondering what kind of convention (if any) exists for table owners. I have > seen tables owned by the parent application, root, informix etc etc but I am > not sure what the 'best practice' is. > > Basically we have a single OLTP engine that is accessed almost exclusively > by an application server. > > Thanks in advance. > > --Ben > > > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Ben wrote: > I am trying to shore-up our database permissions and ownerships and I was > wondering what kind of convention (if any) exists for table owners. I have > seen tables owned by the parent application, root, informix etc etc but I am > not sure what the 'best practice' is. > There's 3 levels of database permission: CONNECT: gives connection to the database RESOURCE: you can create, alter and drop your own tables DBA: you can create, alter and drop all tables Permissions are given or removed with GRANT and REVOKE: GRANT RESOURCE TO user1 GRANT DBA TO user2 Note after REVOKE DBA FROM user2user2 will have RESOURCE permission! With RESOURCE "create table t1 (...)" and "create table myname.t1 (...)" are the same. You cannot do "create table somename.t2 (...)" With DBA you can "create table somename.t2 (...)" In NON ANSI mode (default) tablename must be unique, so you cannot have user1.tab1 and user2.tab1 In ANSI mode fully quallyfied names are used, so you can have user1.tab1 and user2.tab1