Re: Security re: default and non-default roles

Leslie,

First make sure they are indeed the default roles.

select * from dba_role_privs where grantee = 'SCOTT'

See the DEFAULT_ROLE column; it should be NO.

Next, after logging in, before SET ROLE, issue select * from session_roles; to see the roles that are active now. Should be only the DEFAULT roles. If you see more, it's possible that one of those roles have default roles. For instance if DBA is a default role,

select * from dba_role_privs where grantee = 'DBA';

See if any of these are default roles. I suspect you have a second level role as default which is different from Development.

HTH. Arup Nanda

• Original Message ----- From: "Tierstein, Leslie" <LTierstein_at_cns.gov> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, July 17, 2003 10:24 AM Subject: Security re: default and non-default roles

> In an attempt to enhance security, we set up one role which is a default
and
> has only select privileges on tables and another role, with is not
default,
> which has update, insert, delete, as well as select privileges. Our
> application turns on the non-default role via set role.
>
> This all works just fine in our development database, but not in the test
> database (or production). The symptom is that the non-default role is
> enabled on login to sql*plus, even when it's not supposed to be. Can
anybody
> give me a clue as to where to look to find out what's wrong and fix it?
>
> TIA,
> Leslie
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tierstein, Leslie
> INET: LTierstein_at_cns.gov
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Thu Jul 17 2003 - 10:22:49 CDT