Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to see what privileges are granted to schema of another user

Tags:

oracle

Consider the case : In a database , I have two users A and B and their corresponding schema.

I want to know , How can I get the information : what permissions are there for USER A in Schema B .

Consider the case : We have two users and their associated scehmas. We have user A and user B. In A, say we have TB1 TB2, in B,say we have TBa, TBb. Now I want to know how can I find what privileges User A has on Schema B.

For example : User A is writing : select * from B.TBb This means USER A is accessing User B's table so , it shows he has SELECT Privilege. I want to know what all privileges User A has on Schema B.

Which query shall be executed to get the list of privileges that User A has on Schema B.

like image 488
Bharti Pandita Avatar asked Jan 30 '13 12:01

Bharti Pandita


People also ask

How check privileges to all users in Oracle?

You can try these below views. SELECT * FROM USER_SYS_PRIVS; SELECT * FROM USER_TAB_PRIVS; SELECT * FROM USER_ROLE_PRIVS; DBAs and other power users can find the privileges granted to other users with the DBA_ versions of these same views. They are covered in the documentation .

How do I grant privileges to schema?

Only an authorization ID with ACCESSCTRL or SECADM can grant the following privileges on schema names starting with SYS: SELECTIN privilege on SYSCAT, SYSFUN, SYSSTAT or any schema names starting with SYSIBM (SQLSTATE 42501). SELECTIN, CREATEIN and DROPIN privileges on SYSPROC, SYSPUBLIC or SYSTOOLS schemas.

What are the privileges of a user in Oracle?

A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object. The types of privileges are defined by Oracle. Roles, on the other hand, are created by users (usually administrators) and are used to group together privileges or other roles.

Which statement allows privileges on database to users?

A user with the GRANT ANY OBJECT PRIVILEGE can grant or revoke any specified object privilege to another user with or without the GRANT OPTION of the GRANT statement. Otherwise, the grantee can use the privilege, but cannot grant it to other users.


1 Answers

You can use these queries:

select * from all_tab_privs; select * from dba_sys_privs; select * from dba_role_privs; 

Each of these tables have a grantee column, you can filter on that in the where criteria:

where grantee = 'A' 

To query privileges on objects (e.g. tables) in other schema I propose first of all all_tab_privs, it also has a table_schema column.

If you are logged in with the same user whose privileges you want to query, you can use user_tab_privs, user_sys_privs, user_role_privs. They can be queried by a normal non-dba user.

like image 200
Donato Szilagyi Avatar answered Sep 17 '22 14:09

Donato Szilagyi