Re: Finding out if user has connect through granted

From: Pete Finnigan <oracle_list_at_peterfinnigan.demon.co.uk>
Date: Mon, 5 Jul 2004 15:40:18 +0100
Message-ID: <3hLxCeAShW6ABxnY@peterfinnigan.demon.co.uk>

>how can I query a view to show this statement?
>eg - If I want to use a script to recreate the SQL to create the user?
Hi Bruce,

See this example:

SQL> alter user scott grant connect through dbsnmp   2 with role all except resource;

User altered.

SQL> select * from proxy_roles;

PROXY                          CLIENT
------------------------------ ------------------------------

ROLE

---

DBSNMP                         SCOTT

RESOURCE SQL> select * from proxy_users;

PROXY                          CLIENT                         CREDENTIAL
------------------------------ ------------------------------ ----------

---

V M FLAGS

- - -----------------------------------
DBSNMP                         SCOTT                          NONE

    PROXY MAY NOT ACTIVATE ROLE SQL> select * from proxy_users_and_roles;

PROXY                          CLIENT
------------------------------ ------------------------------
FLAGS                               ROLE
----------------------------------- ------------------------------
DBSNMP                         SCOTT
PROXY MAY NOT ACTIVATE ROLE         RESOURCE


SQL> desc dba_proxies
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
--------
 PROXY                                     NOT NULL VARCHAR2(30)
 CLIENT                                    NOT NULL VARCHAR2(30)
 CREDENTIAL                                         VARCHAR2(18)
 TYPE                                               VARCHAR2(5)
 VERSION                                            VARCHAR2(1)
 AUTHORIZATION_CONSTRAINT                           VARCHAR2(35)
 ROLE                                               VARCHAR2(30)

SQL> alter user scott revoke connect through dbsnmp;

User altered.

SQL> You can also see the connection type in v$session_connect_info - which will be PROXY for a proxy connection.

You are correct my script find_all_privs.sql doesn't include proxy connections, I will add it when i get a chance to do so.

Thanks for the potential addition to my script.

kind regards

Pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jul 05 2004 - 09:38:20 CDT