Alternative To SELECT_CATALOG_ROLE In Oracle

It may be useful to allow developers to look at the plsql (views, procs, etc) for a schema without granting the SELECT_CATALOG_ROLE role. This view will provide access, with the possible drawback of adding an object to the SYS schema.

Run this as sys, like:

sqlplus "/ as sysdba" @script.sql

CREATE OR REPLACE VIEW SYS.plsql_ASCHEMAOWNER (OWNER,NAME,TYPE,LINE,TEXT) AS
select OWNER,NAME,TYPE,LINE,TEXT
from sys.dba_source
where owner='ASCHEMAOWNER';

create or replace public synonym plsql_ASCHEMAOWNER for sys.plsql_ASCHEMAOWNER;
grant select on sys.plsql_ASCHEMAOWNER to public;

desc plsql_aschemaowner
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)

Basic Oracle Role For All Users

Prior to changes in Oracle 10, an "enduser" role was useful in every database. The role alllows a connecting and a few harmless though useful grants. Every new user would have this role.

CREATE ROLE "ENDUSER" NOT IDENTIFIED;
GRANT ALTER SESSION TO "ENDUSER";
GRANT CREATE SEQUENCE TO "ENDUSER";
GRANT CREATE SESSION TO "ENDUSER";
GRANT CREATE SYNONYM TO "ENDUSER";
GRANT CREATE VIEW TO "ENDUSER";