Users from a database Import

If I bring a database over from another server, the users come with it. Once I have a Linux admin make the user account on the new server, how do I map the OS user with the user that is already in the database?

Hi, Benji. If you are on linux, just make sure all your required database users have being created on the OS. A simple way to get them, would be: dbschema -d [dbname] -p all |egrep 'connect|dba|resource' Note that this command should be executed against all of your databases. Hope it helps. Best regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Informix on Cloud - Database Administrator - 2017 IBM dashDB Managed Service for Analytics and Transactions - 2017 DB2 Advanced DBA - v10.5 for LUW IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero Informix independent consultant ________________________________ De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de BENJI LONG <ruggedmouse@hotmail.com> Enviado: quarta-feira, 7 de março de 2018 10:44 Para: ids@iiug.org Assunto: Users from a database Import [40802] If I bring a database over from another server, the users come with it. Once I have a Linux admin make the user account on the new server, how do I map the OS user with the user that is already in the database? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, I ran that command, but nothing was returned. I was expecting to see a list of users.

Hi, You can also run the below in dbaccess after connecting to a database: select username, usertype from sysusers; Pam ________________________________________ From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of BENJI LONG [ruggedmouse@hotmail.com] Sent: Wednesday, March 7, 2018 10:06 AM To: ids@iiug.org Subject: Re: RE: Users from a database Import [40808] Hi, I ran that command, but nothing was returned. I was expecting to see a list of users. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

This is what is there. How does this tie into the OS user? Are they separate? Ie, you would have to log into the server with a user, and then use another user to access the database? username usertype informix D iwr_user D public C

Hi, all users that exist as OS-users (you see them in /etc/passwd) are allowed to connect to the database if they are logged into their linux account or opened a connection with for example a jdbc connection string or an odbc connection string by specifying username and password. The reason for this is: public C what means "public has connect permission". public are all users that have a valid account on your linux operation system. If you log in as informix or iwr_user you are dba of the database. If you have the permission you can log in with another account and then "su - informix" or "su - iwr_user". You must know the password of this accounts. You also could change user with "sudo -i -u informix" or "sudo -i -u iwr_user". You have to ask your linux admin to configure your account to be allowed to use sudo. It can be configured that you only have to know your own password to change user with "sudo". HTH, Reinhard. -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von BENJI LONG Gesendet: Mittwoch, 7. März 2018 16:18 An: ids@iiug.org Betreff: Re: RE: RE: Users from a database Import [40810] This is what is there. How does this tie into the OS user? Are they separate? Ie, you would have to log into the server with a user, and then use another user to access the database? username usertype informix D iwr_user D public C ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thank you for the information. Starting to understand a bit. Reading a security document I found as well, but it explains so many things that it is hard to figure out what the minimum requirements are. It looks like role separation chosen at install time, is different then roles you create to give to users for the database objects.

If I want to create a role that allows a user to connect to the database and have read and write, do I 1. create role 2. give it connect, read and write 3. give the role to my user Do I need to revoke anything? In other words, the connect lets you read,write, update and delete. If I only want read and write, do I have to revoke update and delete?

Any user will have access to specifically granted permissions - including whatever public has. So ensure public does not have read write. Otherwise you are spot on with respect to the user. Also pay attention to the tables, grant select, insert, update, delete to the role on the table in question. Or restrict as you see fit. cheers j. > On Mar 7, 2018, at 3:37 PM, BENJI LONG <ruggedmouse@hotmail.com> wrote: > > If I want to create a role that allows a user to connect to the database and > have read and write, do I > > 1. create role > 2. give it connect, read and write > 3. give the role to my user > > Do I need to revoke anything? In other words, the connect lets you read,write, > update and delete. If I only want read and write, do I have to revoke update > and delete? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

I'm missing something to this whole thing. If Public has connect, it has read, write update and delete. I want the user to have only read and write to the entire database. Doesn't every user have public so he has read, write, update and delete?

Connect is a database level privilege. Once connected, you have the privilege as spelled out in systabauth, which will list the table (tabid), user (grantee) and then a privilege string which is essentially s*uidx (select, updated, insert, delete and index (can create an index), no necessarily in that order. If they are upper case, the grantee has the right to pass that permission on to someone else. The * indicates there are column level permissions as well. grantee may be a role. There is also a grantor column indicating who granted the privilege. It is possible for a single user or role to have multiple grants to a given table from different grantors. j. > On Mar 7, 2018, at 5:18 PM, BENJI LONG <ruggedmouse@hotmail.com> wrote: > > I'm missing something to this whole thing. > > If Public has connect, it has read, write update and delete. > > I want the user to have only read and write to the entire database. Doesn't > every user have public so he has read, write, update and delete? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

so to restrict to only read and write, I would have to go through every table and set it for this user?

You=E2=80=99re confusing me with the terms "read and write". I = understand Select (read) and insert and/or update and/or delete (write). By default, when a table is created, it is wide open to everyone. You = have to revoke privileges from the table. Frequently in the schema you = will see a "create table=E2=80=9D followed immediately by a =E2=80=9Crevok= e all on "informix".sysblderrorlog from "public" as "informix=E2=80=9D;=E2= =80=9D (for example). At that point the table is locked down and now you have to specifically = grant privileges to users for the table. If you have 200 tables and you =want to grant the same permissions on each of those to a given user, = then yes - you have to do it speficially for each table - hence why we = love scripting: select =E2=80=9CGrant <permission> on =E2=80=9C || trim(tabname) || =E2=80= =9C to <user>;=E2=80=9D from systables where tabid> 99; cheers j. > On Mar 7, 2018, at 5:30 PM, BENJI LONG <ruggedmouse@hotmail.com> = wrote: >=20 > so to restrict to only read and write, I would have to go through = every table=20 > and set it for this user?=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Remind me not to use single quote marks every again in email, or get a = different email client. > You are confusing me with the terms "read and write". I understand = Select (read) and insert and/or update and/or delete (write). >=20 > By default, when a table is created, it is wide open to everyone. You = have to revoke privileges from the table. Frequently in the schema you = will see a "create table" followed immediately by a "revoke all on = "informix".sysblderrorlog from "public" as "informix=E2=80=9D;" (for = example). >=20 > At that point the table is locked down and now you have to = specifically grant privileges to users for the table. If you have 200 = tables and you want to grant the same permissions on each of those to a = given user, then yes - you have to do it speficially for each table - = hence why we love scripting: >=20 > select "Grant <permission> on " || trim(tabname) || " to <user>;" from = systables where tabid> 99; >=20 > cheers > j. > On Mar 7, 2018, at 5:54 PM, Jack Parker <jack.parker4@verizon.net> = wrote: >=20 > You=E2=80=99re confusing me with the terms "read and write". I = understand Select (read) and insert and/or update and/or delete (write). >=20 > By default, when a table is created, it is wide open to everyone. You = have to revoke privileges from the table. Frequently in the schema you = will see a "create table=E2=80=9D followed immediately by a =E2=80=9Crevok= e all on "informix".sysblderrorlog from "public" as "informix=E2=80=9D;=E2= =80=9D (for example). >=20 > At that point the table is locked down and now you have to = specifically grant privileges to users for the table. If you have 200 = tables and you want to grant the same permissions on each of those to a = given user, then yes - you have to do it speficially for each table - = hence why we love scripting: >=20 > select =E2=80=9CGrant <permission> on =E2=80=9C || trim(tabname) || = =E2=80=9C to <user>;=E2=80=9D from systables where tabid> 99; >=20 > cheers > j. >=20 >> On Mar 7, 2018, at 5:30 PM, BENJI LONG <ruggedmouse@hotmail.com = <mailto:ruggedmouse@hotmail.com>> wrote: >>=20 >> so to restrict to only read and write, I would have to go through = every table=20 >> and set it for this user?=20 >>=20 >>=20 >> = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >=20

Ok, thanks for the help Jack. I think I got it now.

ha. Took me a few times to figure out what you wrote...but I appreciate your effort.