Table Ownership Convention
Ben asked about best practice for table owners. Jonathan Leffler said neither informix nor root should own production tables and preferred a dedicated DBA user, possibly several. Paul Watson creates a dba user per database to own all objects. Claus Samuelsen explained CONNECT, RESOURCE and DBA privileges, GRANT/REVOKE, and naming differences in ANSI and non-ANSI modes. Advice given; no single convention.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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