Re: change users' default schema

On 11/03/2006 03:40:55 AM, Norman Dunbar wrote:

> I've written a database logon trigger (after logon on database) which
> checks to see if the logging in user is for the application, and if so,
> sets current_schema.

Actually, you're right. One does not need "BECOME USER":

  1  select privilege priv from dba_sys_privs
  2  where grantee='SCOTT' or
  3        grantee in (select granted_role from dba_role_privs
  4*                   where grantee='SCOTT')
SQL> /

PRIV
----------------------------------------
CREATE JOB
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
ALTER SESSION
CREATE TABLE
CREATE SESSION
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

12 rows selected.

SQL>
SQL> connect scott/tiger
Connected.
SQL> alter session set current_schema=system;

Session altered.

SQL> select count(*) from help;

  COUNT(*)
----------
       978

SQL> connect scott/tiger
Connected.
SQL> /
select count(*) from help
                     *
ERROR at line 1:
ORA-00942: table or view does not exist




-- 
Mladen Gogala
http://www.mladen-gogala.com

--
http://www.freelists.org/webpage/oracle-l


Other related posts: