Consultor Eletrônico

Status: Verified

GOAL:

How to prevent users other than the Administrator from accessing the schema

GOAL:

Setting the Progress Security for production databases

GOAL:

How to set security permissions on the metaschema tables

GOAL:

What are the Progress security levels

GOAL:

How to protect the schema from users

GOAL:

How to modify the user permissions on tables ?

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.1x

FIX:

There are two levels of security:

1.) at Compile Time - where Progress can control security with the 4GL compiler for example with Dynamic Queries or Procedure Editor, and

2.) at Run Time - where Progress cannot control security meaning, if there is a program:

FOR EACH customer.
DELETE Customer.

and a user that shouldn't be able to run this does so, they will be able to.

You'd need something like the following at the beginning of the program to prevent this from happening:

/* ---------- SEC_USRIDEL ------------- */
IF USERID <> "Admin" THEN DO:
MESSAGE "You know that you shouldn't be running this program .. " VIEW-AS ALERT-BOX.
RETURN.
END.

FOR EACH customer.
DELETE Customer.
/* ---------- SEC_USRIDEL ------------- */

For Compile Time security:

All users must have user-id's to login to the database.
A Security Administrator needs to be setup.

(for the sake of simplicity in the following example, assume that we're locking everything only to Administrator. It is however advisable to always set up an_other super user through the Database Admin -> Security -> Security Administrators)

Locking the schema to "administrator:

Login as administrator,
through DBADMIN -> Security -> Edit Data Security
select "show hidden"
then set the _file, _field, _index, _indexfile
for: Can-Read: Can-Write: Can-Create: Can-Delete: Can-Dump: Can-Load: all to administrator.

At this stage, users will NOT be able to see the schema through Data Dictionary or Data Administration or Insert Tables.Fields through the Editor.
(get message: You do not have permissions to see any table information")

But they will be able to run/write code to display, update, delete, create, dump, load like (say)
"FOR EACH customer.
DELETE Customer."
(if they found out the table/field names for example)

To disallow this: you need to lock the user table fields, through DBADMIN -> Security -> Edit Data Security ... which is not only time consuming but also subject to user entry error!

A faster and more accurate way of achieving this is to lock the user tables to Administrator through a 4GL program (run from a session where Administrator has logged in naturally!)

/* lock user tables to Administrator: SEC_SETBL-ADMIN */

FOR EACH _file WHERE _file-number > 0 AND _file._owner = "PUB":
ASSIGN _file._can-Read = "Administrator"
_file._can-Write = "Administrator"
_file._can-Create = "Administrator"
_file._can-Delete = "Administrator"
_file._can-Dump = "Administrator"
_file._can-Load = "Administrator".
END.

/* SEC_SETBL-ADMIN */

Now, the users not only cannot access the schema, but they can also not create their own 4GL and compile-run it. They will only be able to run pre-compiled programs which read, write, create, delete, dump, load. So if they managed to get hold of the current schema and generate compiled 4GL, they will be able to run it.

While Database data security is /always/ enforced at compile time. Since OE 10.1A (SP02 recommended, see Solution P117034 ) you can select the "User runtime permissions checking" database option (through Admin > Database Options). When selected: only authorized users can compile source code and the generated r-code may be run ONLY by authorized users.
If NOT selected: only authorized users can compile source code and the generated .r-code may be run by any other user.

Progress strongly advises that the security implementation decided upon is fully tested and documented before rolling it out to a production environment.

** ALWAYS ** have a valid backup before implementing security on the database.