Oracle DBA: Public Synonyms

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

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.

CREATE OR REPLACE PROCEDURE SKP_SecureTable (objname in varchar2) IS
begin
execute immediate 'create or replace public synonym ' || objname || ' for SYSADM.' || objname;
execute immediate 'grant select on SYSADM.' || objname || ' to SKP_ReadOnly';
end;
/

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.

create or replace procedure SKP_SecureAllTables is
 cursor tbls is select table_name from dba_tables where owner = 'SYSADM';
 tbl tbls%ROWTYPE;
begin
 open tbls;
 loop
 fetch tbls into tbl;
 exit when tbls%NOTFOUND;
 SKP_SecureTable(tbl.table_name);
 end loop;
 close tbls;
end;
/

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.

create or replace
TRIGGER SKP_TableCreated_Trig
AFTER create ON database
declare
jobnum number;
BEGIN
IF ORA_DICT_OBJ_OWNER='SYSADM' and (ora_dict_obj_type = 'TABLE' or ora_dict_obj_type = 'VIEW') THEN
 SYS.DBMS_JOB.SUBMIT (jobnum, 'SYS.SKP_SecureTable(''' || ORA_DICT_OBJ_NAME || ''');');
END IF;

END;
/

Update: I applied Nicolas’ tweak to make it only secure Tables and Views.  Thanks Nicolas!

As always, if you have an issue with any of this, see anything incorrect, or know a better way, please comment!