Solved

How can I fastly verify if an Oracle User has the GRANT CREATE ANY SYNONYM privilege?

Posted on 2009-04-06
3
1,108 Views
Last Modified: 2012-06-27
Hello,
I need to know if a particular Oracle User has the GRANT CREATE ANY SYNONYM privilege.

With what query can I do this investigation?
0
Comment
Question by:hc2342uhxx3vw36x96hq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Accepted Solution

by:
MarkusId earned 250 total points
ID: 24075562
1. Query: direct grant
2. Query: grant via a role
select * from dba_sys_privs
where privilege = 'CREATE ANY SYNONYM' 
and admin_option = 'YES'
and grantee = 'Oracle User';
 
select a.* from dba_sys_privs a,
dba_role_privs b
where b.grantee = 'Oracle User'
and a.grantee = b.granted_role;

Open in new window

0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 250 total points
ID: 24075563
select GRANTEE
FROM DBA_SYS_PRIVS
WHERE PRIVILEGE = 'CREATE ANY SYNONYM'
AND GRANTEE = &&USER
UNION
SELECT GRANTEE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = &&USER
AND GRANTED_ROLE IN
(
select GRANTEE
FROM DBA_SYS_PRIVS
WHERE PRIVILEGE = 'CREATE ANY SYNONYM'
)

0
 
LVL 1

Author Closing Comment

by:hc2342uhxx3vw36x96hq
ID: 31566934
Many thanks for having helped me ;-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question