RE: PRIVILEGES

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Mon, 18 Sep 2006 09:56:35 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0FB9F968@mailserver1>

This one from Rene Nyffenegger : http://www.adp-gmbh.ch/ is my prefered :

unset ENTER_NAME
typeset -u ENTER_NAME
if [ -n "$PAR1" ];then

    ENTER_NAME=$PAR1
else

    echo " Enter user name ==> \c"
    read ENTER_NAME
fi

sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID ' right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 85 termout on heading off pause off embedded off verify off

select 'Date - '||to_char(sysdate,'Day Ddth Month YYYY HH24:MI:SS'),

       'Username          -  '||USER  nline ,
       'List Role and grants hierarchy for a user  ' nline from sys.dual

/
prompt
set embedded on heading on feedback off linesize 94 pagesize 0 Prompt : $ENTER_NAME

select
  lpad(' ', 4*level) || granted_role "User, his roles and privileges" from
  (
/* THE USERS */

    select

      null     grantee,
      username granted_role
    from
      dba_users
    where
      username like ('%$ENTER_NAME%')

/* THE ROLES TO ROLES RELATIONS */

  union
    select

      grantee,
      granted_role
    from
      dba_role_privs

/* THE ROLES TO PRIVILEGE RELATIONS */
  union (
    select grantee, privilege from dba_sys_privs     union
    select grantee, privilege from dba_tab_privs )   )
start with grantee is null
connect by grantee = prior granted_role
/

Output :

: TRCANLZR
User, his roles and privileges

---

---

    TRCANLZR

        ALTER SESSION
        CREATE PROCEDURE
        CREATE PUBLIC SYNONYM
        CREATE SEQUENCE
        CREATE SESSION
        CREATE TABLE
        CREATE VIEW
        DROP PUBLIC SYNONYM
        EXECUTE
        READ
        SELECT
        SELECT_CATALOG_ROLE
            EXECUTE
            HS_ADMIN_ROLE
                EXECUTE
                SELECT
            SELECT
        WRITE

-----Original Message-----
From: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg] Sent: Friday, 15 September, 2006 9:23 AM To: oracledbam_at_hotmail.com; Dean Paul
Cc: oracle-l_at_freelists.org
Subject: Re: PRIVILEGES

Query DBA_TAB_PRIVS .
Remember that the GRANTEE could also be a ROLE so you'd have to also query DBA_ROLE_PRIVS to see who has been granted the ROLE.

Hemant

• Dean Paul <oracledbam_at_hotmail.com> wrote:

> Hi,
> 
> I want to see which views  has been granted to  whome?
> Which table has all object privileges?
> Thx
> 
> 

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

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





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

Received on Mon Sep 18 2006 - 02:56:35 CDT