Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-06
3
Medium Priority
?
1,139 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
3 Comments
 
LVL 9

Accepted Solution

by:
MarkusId earned 1000 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 21

Assisted Solution

by:flow01
flow01 earned 1000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

824 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