• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2022
  • Last Modified:

Sql to find out schemas with Read Only access in a database?

What is the sql to find out what all schemas in a database have a read-only access?
  • 2
  • 2
  • 2
  • +2
1 Solution
Schemas can not have READ_ONLY access. If they posses tables, they have full access to the tables.

But there are tablespaces that are "READ ONLY".

If you are interested to schemas that have been granted only SELECT privelege to a given set of tables
look this
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 GRANTEE                                   NOT NULL VARCHAR2(30)
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                             NOT NULL VARCHAR2(30)
 GRANTOR                                   NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 GRANTABLE                                                VARCHAR2(3)
 HIERARCHY                                                 VARCHAR2(3)

SELECT distinct grantee   FROM DBA_TAB_PRIVS
WHERE upper(owner) in (list_of_schemas_here)
AND upper(privilege) = 'SELECT';

list_of_schemas_here means a list of schemas --->  ('SCOTT','HR','PR')
which tables you are investigating for SELECT privelege granted to other schemas.
better to check system privileges. if user has only "create session" and no "create table", "create procedure",... it may be considered as read only

select * from dba_sys_privs sp where exists (select 1 from dba_users where username = sp.grantee)
johnsoneSenior Oracle DBACommented:
How do you define a read only account?

If you define a read only account as a user that does not own any tables and only has select privileges on other tables (or the SELECT ANY TABLE system privilege), then this should work:

select username
from dba_users
where (0 = (select count(1)
from dba_tab_privs
where privilege in ('INSERT','UPDATE','DELETE') and
grantee = dba_users.username)
or 1 = (select count(1)
from dba_sys_privs
where grantee = dba_users.username and
privilege = 'SELECT ANY TABLE')) and
0 = (select count(1)
from dba_tables
where owner = dba_users.username);
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Mark GeerlingsDatabase AdministratorCommented:
Johnsone's query may come close to answering your question, but actually I would say that in Oracle, schemas *NEVER* have read-only access.  They always have read/write access to their own objects.

Oracle user accounts however may be have only read-only privileges, but this can *NOT* defined in the user account anywhere!  This can only be determined indirectly by examining the roles and system priviliges that have been granted to the user, as johnsone's script does.
johnsoneSenior Oracle DBACommented:
Like I said, it depends on how you define read only.

If the user does not have quota on any tablespaces and does not have any system privileges that contain CREATE, we can be pretty comfortable the user has no objects it can modify.  However, that certainly doesn't prevent one from being created.  A user with no privileges at all can still own procedures, functions, etc., the object can be created by a user with the CREATE ANY privilege, then with only the CREATE SESSION privilege the user that now owns that object can change and/or drop it.

If you own it, you can do anything you want to it.
gram77Author Commented:
I have connected to hock schema, but I do not see any hock privilege why?

All that I see is:
HOCK_RO                       CREATE SYNONYM                           NO

so, HOCK_RO has permission to CREATE SYNONYM

but what about HOCK system privilege? How do I see it.

Q I want to see If HOCK schema has read-only objects or not?

SQL> conn hock/xxxx@abc.def.com
SQL> select * from dba_sys_privs sp where exists (select 1 from dba_users where
  2  username = sp.grantee);

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
HOCK_RO                       CREATE SYNONYM                           NO
AGGR                           CREATE DATABASE LINK                     NO
gram77Author Commented:
I have the following output in hock schema. What does it mean?

Why isn't there any entry for HOCK itself.

SQL> conn hock/xxx@abc.def.com
SQL> select * from dba_sys_privs sp where exists (select 1 from dba_users where
  2  username = sp.grantee);

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
HOCK_RO                             CREATE SYNONYM                           NO
PGGR                           CREATE DATABASE LINK                     NO
PGGR                           CREATE PROCEDURE                         NO
PGGR                           CREATE SESSION                           NO
PGGR                           CREATE TABLE                             NO
PGGR                           CREATE VIEW                              NO
PGGR                           SELECT ANY TABLE                         NO
PGGR                           UNLIMITED TABLESPACE                     NO
LMONCURE                       CREATE SESSION                           NO
LMONCURE                       CREATE TABLE                             NO
LMONCURE                       CREATE VIEW                              NO
SXELSK                         CREATE TABLE                             NO
SXELSK                         CREATE VIEW                              NO
SALEMK                         CREATE PROCEDURE                         NO
SALEMK                         CREATE SESSION                           NO
SALEMK                         UNLIMITED TABLESPACE                     NO

GRANTEE - the schema that has been granted a privelege
PRIVILEGE  - the privilege
ADM - "Administrative Option" -means if YES that the grantee can grant this privilege to other users
Mark GeerlingsDatabase AdministratorCommented:
"Q I want to see If HOCK schema has read-only objects or not?"


If the HOCK schema owns any objects, they are definitely *NOT* read-only!  In Oracle, the object owner always has all privileges on objects in his/her schema.

Now, for objects in other schemas, the HOCK user *MAY* have read-only privileges *IF* that is what the owner(s) of the other schema object(s) have granted either directly to HOCK or to roles that have been granted to HOCK.

Then in addition to specific object grants on tables and views, Oracle also supports "system privileges" that can be granted either to users or to roles.  These include:
selact any table
update any table
insert any table
delete any table

If the HOCK user, or roles that have been granted to the HOCK user have been granted any of these, then even though HOCK may not have specific table grants, the system privileges would allow that type of access.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now