How to create a "read only" user with IngresNET login?
Johannes
we are working with an "old" Ingres II 2.6 and
i wanted to create a new db user with "select" rights on 3 databases,
so i started to create a GRANT script like this:
select 'grant select on '+squeeze(table_name)+' to username'+';
commit;' from iitables where table_owner = 'myowner' AND (table_type=
'T' OR table_type = 'V');
The Result is a large script with all GRANT SELECTs for all tables ;-)
I executed this script and everything looks fine, but i cant access
the databases with Ingres.Net from a client pc.
So i started reading a lot of documentation and learned that i have to
create the same user in the HP-UX environment
to connect to the database server via Ingres.Net. But this does not
work.
I cant access the database. Is there any rightsmanagement on the
ingres server side where i can enable this user to acces remotely?
Thank you in advance
Johannes Nissen
Martin Bowes
If private did you grant the new username access to the databases.
Marty
Hello,
Thank you in advance
Johannes Nissen
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Ingres Forums
Usually you would - before GRANTing anything to a user - start with
creating the user both on OS level (which you now did) AND in the DB
installation.
The latter is done by either using "accessdb" or using the "CREATE
USER" SQL statement (run against database iidbdb).
--
Bodo
------------------------------------------------------------------------
Bodo's Profile: http://community.ingres.com/forum/member.php?userid=16
View this thread: http://community.ingres.com/forum/showthread.php?t=11633
Johannes
> Are these databases private or public?
>
> If private did you grant the new username access to the databases.
>
> Marty
>
>
>
Yes i did, but only the "access database" right, no other privilegs.
The others don't seem to affect the login procedure, or i am wrong?
Thank you
Johannes
Thank you Bodo,
tomorrow I will try it in your recommended order again.
I used the CREATE USER statement and will take a look at the accessdb
command.
Johannes
Johannes
now it seems i am near my goal, i don't get the login error any more:
E_LQ0001 Failed to connect to DBMS session.
E_LC0001 GCA protocol service (GCA_REQUEST) failure.
Internal service status E_GC000b -- Remote login failure: invalid
password/userid. If using installation passwords, please make sure
all
servers are 6.4/02 or newer and that they have been started by the
installation owner..
But now I get this meassage box under Ingres ODBC Administrator:
Caution failed. - Database Name Browser
[CA][Ingres ODBC Driver][Ingres]User authorization check failed.
Your user identifier was not known to this installation.
Contact your system manager for further assistance.
Does this refer to the Ingre/Net installation on the XP Client,
or to the server authentication?
Johannes
Ingres Forums
What is the actual error message your new user is getting when trying to
connect?
Can other (existing) users connect via Ingres/Net?
Johannes
the actual message is:
Caution failed. - Database Name Browser
[CA][Ingres ODBC Driver][Ingres]User authorization check failed.
Your user identifier was not known to this installation.
Contact your system manager for further assistance.
If I take an other existing user, it works fine.
The Databases are shown in the Database Select Field,
but not with my new created user.
If i look in the errlog.log on the Ingres Server side,
i can't see anymore login failure messages.
Johannes
Martin Bowes
You need to ensure the username is created on the installation you are
connecting to.
Marty
-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of
Johannes
Sent: 05 February 2010 09:24
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] How to create a "read only" user with
IngresNETlogin?
Hi Bodo,
the actual message is:
Johannes
_______________________________________________
Johannes
I checked "cat /etc/passwd" and my new User is created in the Unix
environment on the Ingres DB Server.
The password also is correct, i tried "su newuser" and entered the
password.
Johannes
Ingres Forums
Did your add your user to the same installation you are connecting to
via Ingres/Net - listen address in the netu entry for your vnode should
be the same as II_INSTALLATION on the server?
Does your username contain some special characters (e.g. ".") or
spaces?
Do you have global and private entries for the same vnode - then the
private entry for the client user would take precedence.
Have you checked that the user can actually access the database on the
server:
Did you do a "sql dbname" after the logon as the user?
Martin Bowes
You've created a UNIX username...That is not creating an Ingres
Username.
You need to do...
sql iidbdb << SQL_END
create user you_username;
\p\g
\q
SQL_END
Marty
-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of
Johannes
Sent: 05 February 2010 10:20
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] How to create a "read only" user
withIngresNETlogin?
Hello Marty,
Johannes
_______________________________________________
Laframboise, André
You also need to create the user under Ingres. Ingres keeps it's own list of authorzed users.
You can use 'accessdb', option '3' and select 'create'.
Andre
-----Original Message-----
From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Johannes
Sent: February 5, 2010 5:20 AM
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] How to create a "read only" user withIngresNETlogin?
Hello Marty,
Johannes
_______________________________________________
Larry West
Was mkvalidpw run after the ingres installation?
>_______________________________________________
>Info-Ingres mailing list
>Info-...@kettleriverconsulting.com
>http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Larry West, Financial Systems Manager
(206) 543-1644
Applied Physics
University of Washington
Johannes
The Ingres II Database is running since 4 yaers (not a fresh install),
we have one DB User,
wich is also owner of the dbs, tables, view, and so on. This user also
exists as a Unix User,
like Bodo mentioned before.
With this user it is absolutly no problem to connect via Ingres/Net
(ODBC) to the database.
Now we get a Business Inelligence System / Datawarehouse to run
statistics and reports.
For the Data Import to the DWH we would like to have a "read only"
user on our productiv Ingres Server,
to meet compliance of data security and safety.
What i did:
I installed Ingres/Net as Administrator on the DWH Server (W2K3R2 with
MSSQL2008) and configured a vnode
with Ingres Servername, old_username, old_password, WinTCP, Socket
II.
This works fine.
In the next step i created a new Unix user with password.
Then i created a new user with same name in Ingress with accessdb
command like Bodo and Andre recommended.
After this i granted access to 3 Databases (not the system DBs!) and
ran a script to grant selects on diffrent tables.
Back on the DWH Server i put the new username and new password into
the vnode fields.
Now the connection seems to connect (tried with VisualDBA) but it
freezes.
In the Ingres errlog.log isn' t any Login Failure (yesterday was,
because i had no Unix user).
If I now switch to ODBC and select this vnode, i got this error:
"Caution failed. - Database Name Browser
[CA][Ingres ODBC Driver][Ingres]User authorization check failed.
Your user identifier was not known to this installation.
Contact your system manager for further assistance."
And i cant see and select any database in the drop down field.
I have only one vnode configured on the DWH Server, and the user
credentials are private.
@Bodo: I tried to run sql dbname on the Ingres Server, but it seems
that the environment isn't configured.
I got an error of missing library paths.
@Larry: I did not run "mkvalidpw" because it isn't a fresh ingres
installation... (Shall i run it for each new user?)
Thank you all for your patience ;-)
Johannes
Larry West
problem. I recently similar problems
but it was with a fresh install of 9.2
Ingres Forums
Hi Johannes,
mkvalidpw shouldn't be the problem as you can connect with your
old/existing user via Ingres/Net.> I tried to run sql dbname on the Ingres Server, but it seems
> that the environment isn't configured.
> I got an error of missing library paths.
- II_SYSTEM according your Ingres installations loacation
- PATH should contain $II_SYSTEM/ingres/bin and
$II_SYSTEM/ingres/utility
- LD_LIBRARY_PATH (or LIBPATH or SHLIB_PATH depending on your Unix
flavor) should contain $II_SYSTEM/ingres/lib
Then it should be possible to run "sql yourdb".
Ingres Forums
'Let Claude Hopkins Scientific Advertising Audiobook Skyrocket Your
Sales' (http://www.claudehopkinsaudio.com)
--
anneemerick
------------------------------------------------------------------------
anneemerick's Profile: http://community.ingres.com/forum/member.php?userid=22854
Johannes
@ Bodo: I successfully started isql on the Ingres Database Server with
our new user,
it also was possible to do some selects like i granted the
permissions.
Is there any kind of vnode configuration with rights/restrictions
which could block
the connection from Ingres/Net client machines?
Johannes
Ingres Forums
There shouldn't be any problem with the Ingrea/Net connect, provided
your vnode on the Ingres client (which is your DWH Server) is using same
connection information (remote address, protocol, listen address) as the
one which is working.
Can you do a remote login (e.g. using "rlogin" or "telnet" - not just a
"su username") from your client to the server machine using your new
user's credentials?
Have you tested the (private) vnode using the "Test" item in "netutil"?
--
Bodo
------------------------------------------------------------------------
Bodo's Profile: http://community.ingres.com/forum/member.php?userid=16
Johannes
Here the steps:
1. Create Unix user
2. Set Ingres environment to Unix user
3. create DB user with same name than Unix user
4. create grant script with select 'grant select on
'+squeeze(table_name)+' to username'+'; commit;' from iitables where
table_owner = 'myowner' AND (table_type= 'T' OR table_type = 'V');
5. This was my major fault: execute the grant scripts in exclusiv mode
(there should be nobody working with the database, because inges needs
exclusiv rights on the tables during grant statements)
6. Stopping and starting ingres again.
Thank you all for your support.
cheers
Johannes
On 8 Feb., 16:54, Ingres Forums <info-
Ingres Forums
I don't see any reason why you have to do steps 5 and 6.
Ingres doesn't require exclusive DB access to do grants.
It just requires an exclusive lock on the table, but provided your
other users commit their transactions where the table is involved this
shouldn't be a problem.
grantee, so no reason to restart the DBMS.
Johannes
but if I tried to execute the grant script (serveral times) we got a
lot of logical locks
which could not be solved by killing the specific process number.
It seems to be a deadlock scenario.
But it is also possible that this was a result of bad software design.
I took my chance in a scheduled downtime and it works without any
problems
with exclusiv access.
Johannes