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