Login Register Actian.com  

Actian Community Wiki

Navigation
Learn About
Developing With
Ingres Talk
Information
Toolbox

DBMS Authentication Workarounds

From Ingres Community Wiki

Jump to: navigation, search

Emulate Oracle, MySQL, SQL Server DBMS Authentication Semantics

By default Ingres 10S and earlier use Operating System authentication for local user access (also see Database_Authentication), this requires operating system users to be defined as well as DBMS users. This article will cover one way to allow applications to work as if only DBMS authentication is being used. A follow on article that discussed how to do this with the null security mechanism is available here.

If Ingres has already authenticated a user, they may not change their effective user id unless they are a either a DBA or an Ingres super user. Ingres authenticates users at initial connect time based on the operating user id (if connecting locally) or from the username/password provided in the vnode if connecting remotely (note Installation Passwords are a special case and is ignored here for simplicity).

One way to allow an application server to connect as an arbitrary user is to run the application server as an Ingres super user (or DBA), this user can then specify an alternative user id at connect time for each database connection. This does not require a DBMS password to be specified.

NOTE Users who have impersonation rights do not need to specify a DBMS password for the user they are connecting as.

Create users examples

To create a DBA user, simply create a user as normal and then create a database owned by the user. User can be created in Visual DBA, accessdb, or the sql terminal monitor (see here.) For example as the person who owns the Ingres installation (that is, the person who installed Ingres, often this is the "ingres" user, issue:

sql iidbdb
CREATE USER mydba\p\g\q
createdb testdb -umydba

To create an Ingres super user create a user almost as normal and specify super user privileges:

sql iidbdb
CREATE USER mysuper WITH PRIVILEGES=(SECURITY)\p\g\q

Now create Operating System user (e.g. Use adduser, useradd, etc.).

You can test out if this works in an sql terminal monitor to check the effective user id:


sql testdb -uingres
select * from iidbconstants\p\g

Python script showing connections as different users

Here is a python script that demonstrates an application connecting as two different users:

import sys
import getpass

import ingresdbi

stdout=sys.stdout

ingres_dbname = 'testdb'
whoami=getpass.getuser()

stdout.write('OS user id: %r\n' % whoami)
stdout.write('db: %r\n' % ingres_dbname )

stdout.write('\nConnecting as current user\n')
db = ingresdbi.connect(database=ingres_dbname, vnode='(local)', servertype='ingres')
c = db.cursor()
sql_query = "select varchar(user_name) from iidbconstants"
c.execute(sql_query)
dbusername = c.fetchone()[0]
stdout.write('returned DBMS user id: %r\n' % dbusername)
db.close()

stdout.write('\nConnecting as named user\n')
db = ingresdbi.connect(database=ingres_dbname, vnode='(local)', servertype='ingres', uid='ingres')
c = db.cursor()
sql_query = "select varchar(user_name) from iidbconstants"
c.execute(sql_query)
dbusername = c.fetchone()[0]
stdout.write('returned DBMS user id: %r\n' % dbusername)
db.close()

Script above assumes there is a database called testdb already created. Note no dbms password is required.


Output for the above:

OS user id: 'mysuper'
db: 'testdb'

Connecting as current user
returned DBMS user id: 'mysuper'

Connecting as named user
returned DBMS user id: 'ingres'


The example above is for using a super user, the exact same script could be used to connect as a DBA instead.

Personal tools
© 2011 Actian Corporation. All Rights Reserved