How to view the access priviledge granted to all users (include admin)

[BenjaminLim]

Hi,

Please advice how can I view the access priviledge to tables, views, etc granted to all users (including administrators)

Please advice. Thanks.

Regards
Benjamin

 

[rcurva]

You can make use of the views below;

ALL_TAB_PRIVS --- obj. privileges on tables
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD
ALL_COL_PRIVS
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS

if you have DBA privilege, then you can accesses the ffg;

DBA_COL_PRIVS
DBA_ROLE_PRIVS
DBA_SYS_PRIVS
DBA_TAB_PRIVS

For example:

Select *
from all_tab_privs
where grantee = 'SCOTT';

/** list out all table privs for SCOTT user **/

Access to views depends on the privs to the underlying tables.

 

[BenjaminLim]

Thanks for the quick respond.

I tried it out but the problem is that the

1. ALL_TAB_PRIVS_MADE, ROLE_ROLE_PRIVS
does not contain any records.
2. The tables that I do not have access to is that listed ( as I need to know who has access to those tables since I do not have)

Could you kindly explain what do the tables you listed stores? (Do not understand the acronynons such as RECD, etc)

Please advice. Thanks.

Regards
Benjamin

 

[rcurva]

ALL_TAB_PRIVS --- obj. privileges on tables
ALL_TAB_PRIVS_MADE -- obj. privileges made
ALL_TAB_PRIVS_RECD -- obj. privileges received
ALL_COL_PRIVS -- obj. privileges on tables' columns (yup, you can grant down to the column level)
ALL_COL_PRIVS_MADE -- obj. privileges on tables' columns made
ALL_COL_PRIVS_RECD -- obj. privileges on tables' columns received
ROLE_ROLE_PRIVS -- roles granted to roles
ROLE_SYS_PRIVS -- system privs. granted to roles
ROLE_TAB_PRIVS -- obj. privs granted to roles

ROLES can be created to group privileges to be assigned to users. This feature is good especially in security maintenance.

 

[BenjaminLim]

Thanks rcurva.

I would like to rephrase my 2nd question:

The tables that I do not have access to is not listed in any of the recommended table (as I need to know who has access to those tables since I do not have)

And in term of hierachy who has the highest access rights by default (would it be SYS?) I am trying to understand the structure.

Please advice further. Thanks.

Regards
Benjamin

 

[rcurva]

By default, sys has the highest system privileges, but in version 7, sys and system share the limelight.

Before you can access those tables, the schema owner of those tables must give you the required object privileges first. The schema owner is the creator of the tables.

You'll have to ask your DBA to know who the owner of those tables, bec. I assume that you are not the DBA.

 

[BenjaminLim]

Thanks.

1. What is the synthax to view who the table creator is?
2. If I do not require access to those tables but for trouble-shooting reason I need to know who have access to those tables, how could I do so using my logon?

You are right I am not the DBA but system support personnel.

 

[rcurva]

You can issue this SQL in sqlplus.

SELECT *
FROM DBA_TABLES;


The owner column is the creator of the table.

 

[BenjaminLim]

You mean if I want to see the creator for all tables, it must be via DBA account right?

I issue the above statement but an error was shown I subsequently tried it out with ALL_TABLES and it was OK however the tables that I were not granted access again did not appear as 1 on the rows in ALL_TABLES selected.

Please advice. Thanks

 

[rcurva]

Yup. You guessed it right, as an ordinary user, you can only see objects that you created and objects that you have access on.

The DBA account can view all objects but cannot give grants on objects that he doesn't own.

 

[karluk]

Probably you could check db privileges if you have "select any table" privileges. That should give you access to the DBA_ catalog views, even if you don't have DBA privileges.