Database is allowing operations even for non-existing users

I just got something to add here:

I am logged into Linux server as user id "qaduser".

I created database from sports2000 and named it to mydb.

Enabled secondary broker

Here by default it will have DBA_ACC to "qaduser" (as db is created using this id) and sysprogress id which is default.

Now:

on second machine I logged into Linux by user ID "xyzid"

I just type the command like this:

sqlexp -db dbname -S port-name -user qaduser

It will allow me to connect to database with full dba access. Once I am in sqlexp I can do anything including creating a new user, granting a permission provided i should just know the port number and user id, its not a big deal to get the user id who created the db.

on prod server it should not be the case. Its a security concern. Is it a bug?

Can you share your views on it please.

Thanks,

Qasim

customer does not want to create _user records because that impacts 4GL clients.

but there is an option on user accounts called “sql-only”. if you create _suer records with that option enabled, then there should be no impact on 4GL clients.

Thanks Alok and Gus for your quick reply.

here question is about Progress DB security. LIke I explained in my second post how easily one could get a dba access to any id without trouble. We need to restrict DB access any non-existing user id or unauthorized user id.

is this a bug?

I didn't see it come up, but the way we've secured access to the SQL-side of the OE database is by adding "-ServerType SQL" to the proserve when starting the broker.  That way it disallows any remote ABL connections - which can be even more insecure than the SQL ones.  On the SQL side of things, you must specify GRANT statements before the databasse will give access to data.  Unlike SQL, any remote ABL clients are typically allowed full access to the data until it is explicitly denied.

On the ABL connectivity side (if you ever end up needing it), the only reasonable way we've found to add security is with IP filtering on the port number.  IP filtering is a function of the OS.

Hi dbeavon,

thanks for your reply. As you said " you must specify GRANT statements before the databasse will give access to data. "

Exactly, thats also my point. Anyoone can GRANT any access to any user (non-existing user id as well).

If I know dbname and its secondary broker port and who has created the db, then that information is enough for any other user. Other user can just have to write:

sqlexp -db dbname -port XXX -user username-who-created-db-or-sysprogress.

This is a security thread.

" sqlexp -db dbname -port XXX -user username-who-created-db-or-sysprogress"

    .Ummm, once  you have any sql user defined (including sysprogress), you must include the Password in the connection request. so, " sqlexp -db dbname -port XXX -user sysprogress" fails.

Next:

Progress allows you flexibility to configure DB's in development environments and then 'up' the game through various non-prod and production environments.

Don't want SQL user for a Database. 1. Only have shared memory connections or 2. Exclude SQL connections by defining ABL only Brokers.  (-ServerType 4GL),

Want to manage SQL Connection for a Database. Define your sql-users, GRANT them the right access,Define a specific broker for SQL Connections (-ServerType)

Added benefit of splitting the Brokers is you can tune both for their respective uses, including the number of connections for each type.

Hi Ducity,

Thanks for your reply.

You said:    .Ummm, once  you have any sql user defined (including sysprogress), you must include the Password in the connection request

But here customer do not want to define user (no entry in _user table),

Customer is using separate broker for 4GL and SQL.

Question: In order to secure the DB, does it mean that Progress need _user entry defined in db? if not, what is the solution to secure DB from unauthorized access to DB ?

As suggested by Gus if I create _user which is SQL only then its not validating the user id and password with sqlexp:

x                                                                    SQL      x

xUser ID  Domain                           User Name            Pwd? only     x

x-------- -------------------------------- -------------------- ---- ----     x

xqasim                                                          yes  yes      x

[mfg@vmlinux qea]$ sqlexp -db mfgdb -S 22087 -user abcxyz

OpenEdge Release 11.6 as of Fri Oct 16 18:22:20 EDT 2015

Connecting user "abcxyz" to URL "jdbc:datadirect:openedge://localhost:22087;databaseName=mfgdb"... (8920)

SQLExplorer>

User abcxyz should not have access to DB. Any further advise?

I suggest you raise it with tech support, what you describe in not expected behaviour.

In the data dictionary tool (type dict. ctrl-x in procedure editor) you can disable access by user with blank user id name as follows:

Admin > Database Options > Disallow Blank Userid

This will then require all users to have an _user account and password in order to connect to the database.

I missed this sql  question which was posted here in the DB forum. Usually I read the DB posting but somehow I missed this one. Sorry about that.

The best answer for your customer, in my view,  is to create a "sql only" user.  Both ABL and sql have the ability to create a "sql only" user in _User.

The sql only user is not seen by the ABL and has no effect at all on ABL operation.

For sql, the  "sql only" user is always seen and this user is required to authenticate with their defined password. This gives a good amount of authentication security.

Hope this helps.

...steve pittman [sql software architect]