28 | 03 | 2024
Latest Articles
Popular Articles

Security

Non default roles and password protected roles

User Rating:  / 1
PoorBest 

Enhanced security with non default roles and password protected roles

 

With the below example I show

 

1. How to create a custom made role and grant some privilege to that role

2. How to grant that role to a user but disabled by default

3. how to protect custom made roles by a password

 

SQL> create role DB_ROLE identified by secret;

Role created.

SQL> grant resource to DB_ROLE;

Grant succeeded.

SQL> create user DB_USER identified by my_secret;

User created.

SQL> grant create session, DB_ROLE to DB_USER;

Grant succeeded.

SQL> grant unlimited tablespace to DB_USER;

Grant succeeded.

SQL> alter user DB_USER default role all except DB_ROLE;

User altered.

 

SQL> connect DB_USER/my_secret;
Connected.

SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> set role DB_ROLE;
set role DB_ROLE
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'DB_ROLE'

SQL> set role DB_ROLE identified by secret;

Role set.

SQL> create table t1 (c1 number);

Table created.