RE: PRIVILEGES
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: <hkchital@xxxxxxxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 18 Sep 2006 09:56:35 +0200
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@xxxxxxxxxxxxxx]
Sent: Friday, 15 September, 2006 9:23 AM
To: oracledbam@xxxxxxxxxxx; Dean Paul
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
