Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question, manu.

Asked: June 20, 2017 - 1:29 pm UTC

Last updated: October 01, 2018 - 4:41 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

Good Day !

Today we have a structured approach to access control, based on use of schemas (to group together database objects), and assigning specific permissions to roles.

2 roles :
- USER_ROLE = CONNECT + SELECT/INSERT/UPDATE/DELETE ANY TABLE
- DBA_ROLE = CREATE/DROP ANY INDEX/PROCEDURE/SEQUENCE/TABLE/VIEW

USER_ROLE has DML privileges and DBA_ROLE has DDL privileges.

Privilege SELECT ANY TABLE not only access the objects in his own schema but in all schemas.

How can we confine each user with USER_ROLE to their own schema ? An answer is to use "grant select, insert, update, delete on <table> to <user>" ... it seems complex to me

Do you have any simple working solution ?

Best regards

and Chris said...

There isn't an easy way unfortunately. You need to create a role for each schema and assign the relevant grants to that.

Note: the ability to grant privs on all objects in a schema to another is one of the more popular suggestions on the DB ideas forum:

https://community.oracle.com/ideas/2333

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Create a role for each schema and assign the relevant grants to that

A reader, June 21, 2017 - 1:10 pm UTC

Hi,

I read your answer

On the same Database :
- 3 schemas A, B and C
- 2 roles R_DBA and R_USER
- 2 accounts by schema : A_DBA/A_USER, B_DBA/B_USER and C_DBA/C_USER

The privilege "SELECT ANY TABLE" is granted on role R_USER.
The role R_USER is granted on all users A_USER, B_USER and C_USER.

So I give "select" on all tables on all schemas

It would be more secure if I have some clear grants that give access to a whole schema than having thousends of individual grants on tables that do the same ?

+----------------+----------------+----------------+
| Schema A | Schema B | Schema C | -> Schemas
+-------+--------+-------+--------+-------+--------+
| A_DBA | A_USER | B_DBA | B_USER | C_DBA | C_USER | -> Users
+-------+--------+-------+--------+-------+--------+
| R_DBA | R_USER | R_DBA | R_USER | R_DBA | R_USER | -> Roles
+-------+--------+-------+--------+-------+--------+

June 22, 2017 - 10:10 am UTC

It would be more secure if I have some clear grants that give access to a whole schema than having thousends of individual grants on tables that do the same ?

I'm not sure what you mean. You should avoid "select any" privileges. This gives people to much power.

I'm saying you should create these roles:

R_DBA_A, R_USER_A
R_DBA_B, R_USER_B
R_DBA_C, R_USER_C

And give them to the relevant users. The explicitly grant the relevant privileges to these. Currently you have to specifically state all of these.

"DEFAULT_ROLE" in dba_role_privs

Rajeshwaran, Jeyabal, September 28, 2018 - 2:01 pm UTC

Team,

Could you help us to understand what is "DEFAULT_ROLE" in dba_role_privs means?

checked into the documentation, but that doesn't help much - could you please explain.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/DBA_ROLE_PRIVS.html#GUID-9BE97833-92B5-4CCE-AAC1-16C094C1A530

<quote>
Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO)
</quote>

How could i designate the role as a Default role for user.

rajesh@ORA12C> grant r to demo;

Grant succeeded.

rajesh@ORA12C> select *
  2  from dba_role_privs
  3  where granted_role ='R';

GRANTEE    G ADM DEL DEFAULT_ROLE         COM INH
---------- - --- --- -------------------- --- ---
DEMO       R NO  NO  YES                  NO  NO
RAJESH     R YES NO  YES                  NO  NO

rajesh@ORA12C>

October 01, 2018 - 4:41 pm UTC

With:

alter user chris default role [ all [ except csv of roles ] | csv of roles | none ];

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ALTER-USER.html#GUID-9FCD038D-8193-4241-85CD-2F4723B27D44

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Create a role for each schema and assign the relevant grants to that

"DEFAULT_ROLE" in dba_role_privs

More to Explore

PL/SQL demos

PL/SQL docs

Roles and Privileges

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Create a role for each schema and assign the relevant grants to that

"DEFAULT_ROLE" in dba_role_privs

More to Explore

PL/SQL demos

PL/SQL docs