PSST0101Just Trying to Remember and Share What I Learn in the PeopleSoft World

Oracle DBA: Public Synonyms

May 14, 2012 — digitaleagle

Recently, I wrote about granting read only access to the database for developers. Tim Hall commented that my solution was insecure and granted too much, possibly allowing a loophole. At the same time, I recently wanted to provide public synonyms to users.

So, the following role, procedures, and trigger attempt to accomplish those two goals:

Allow for securely granting readonly access

Automatically create public synonyms

First, I created a new custom role to use for readonly access. Note that with this role, I am making the following assumption:

Only developers/power users need access to the role

The developers/power users need access to all of the tables (we aren’t allowing for only particular groups of tables)

If we want to to restrict security, we will use PeopleSoft Query and not allow direct database access

So, here’s how I created the role:

create role SKP_ReadOnly;

Now, I need a procedure that will both create synonyms and connect my role with a particular table. The goal of this procedure is to accomplish any task that needs to happen to all tables in the system. You could easily add extra roles or other tasks to this procedure as well.

The only parameter is the table that you want to “secure”. Note that this “objname” is the actual table name with the “PS_” in front of it as opposed to the record name.

The first execute immediate creates the public synonym. This allows any user to query the table without having to put the schema in front of it. Without the synonym, every user has to put “sysadm.” in front of all of the PeopleSoft tables.

The second execute immediate gives “select” access to the SKP_ReadOnly role. This attaches readonly access for that table to that role.

Next, I need to run the previous procedure against all of the existing tables. This is a one-time deal for the most part, but I went ahead and created a procedure for it. The procedure simply loops through all of the tables in the “SYSADM” schema and executes the previous procedure passing that table as the parameter.

Now, we just need to call the procedure. This works on all of the existing tables.

call UP_SecureAllTables();

Finally, we need to handle new tables. If you create any new records or even if you Alter any existing records, you will loose the synonym and the role connection. Remember that when you alter an existing table in App Designer, it creates a new table with the new structure, copies the data from the old one, drops the old one, and renames to new one to the original name. When it drops the table you loose your security.

So, this trigger fixes that problem. Basically, any time a new table is created, it runs our original procedure to grant that table to the role and create the synonym.