CODEWITHSUNDEEP
sqloracle-database
Felipe Vidal
Felipe Vidal

Reputation: 505

Oracle - list privileges I have in my schema - commands

Is there any way for me to see which DDL privileges I have in my own schema? I'm looking for a SELECT-type sentence.

I mean, how do I know if I can create, drop, alter, &c.?

Modification - 2019/Oct/24th - I think it would be simpler to understand "How can I know if I have the CREATE ANY TRIGGER privilege?".

Thanks in advance.

Upvotes: 0

Views: 899

Answers (2)

soodeh ebrahimi
soodeh ebrahimi

Reputation: 1

you can get it with query on DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS to get information about user privileges about system, tables, and roles. for example SELECT * FROM DBA_SYS_PRIVS;

Upvotes: 0

1991DBA
1991DBA

Reputation: 805

You should automagically have select privileges on tables in your own schema - they belong to you. You can figure out if you can create, drop, or alter objects based on the roles that have been granted to you. You can use a query like SELECT * FROM USER_ROLE_PRIVS; or select dbms_metadata.get_granted_ddl('ROLE_GRANT', user) from dual; to get a list of the roles that have been granted to you, and based on that, you'll know what kind of privileges you have within the database.

For example, if you see that you've been granted the "Resource" role, you will be able to do things like create tables, procedures, triggers, etc.

You can use a query like select * from session_privs; to see EVERY privilege that you have, but that will include privileges for the entire database, including privileges that are associated with a role. To see privileges that aren't associated with a role, you could use a query like SELECT * FROM USER_SYS_PRIVS;

Upvotes: 1

Related Questions