Re: change users' default schema

From: Mladen Gogala <mgogala_at_verizon.net>
Date: Fri, 03 Nov 2006 08:19:36 -0500
Message-id: <1162559976l.2710l.0l@medo.noip.com>

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

Received on Fri Nov 03 2006 - 07:19:36 CST