Security
Non default roles and password protected roles
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Friday, 05 October 2012 16:10
-
Last Updated on Friday, 05 October 2012 16:12
-
Published on Friday, 05 October 2012 16:10
-
Written by Guy Lambregts
-
Hits: 5426
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.