PRODUCT_USER_PROFILE table, How do we disable roles using by this table?
PRODUCT_USER_PROFILE table to provide product level security that supplements the user-level security provided by the SQL GRANT andREVOKE commands and user roles.
This table in System account. We can insert the product based data here which user roles, grant and revoke command you want to restrict.
This table does not apply for SYSDBA or SYSOPER privileges.
The PUP table applies only to the local database. If accessing objects on a remote database through a database link, the PUP table for the remote database does not apply. The remote database cannot extract the username and password from the database link in order to determine that user’s profile and privileges.
More Detail from here : Oracle Document
Here I’m going to create user, roles and create table for this user.
SQL> conn / as sysdba Connected. SQL> create user fsmith identified by fsmith; User created. SQL> grant create session to fsmith; Grant succeeded. SQL> create role role2; Role created. SQL> grant create table to role2; Grant succeeded. SQL> grant role2 to fsmith; Grant succeeded. SQL> conn fsmith/fsmith Connected. SQL> create table emp(empno number,empname varchar2(20)); Table created. Now I insert into product_user_profile table for product level security like roles and after create table for this user, It has no privileges for create table because of the roles has disabled in product_user_profile table. SQL> conn system/Admin123 Connected. SQL> desc product_user_profile; Name Null? Type ----------------------------------------- -------- ---------------------------- PRODUCT NOT NULL VARCHAR2(30) USERID VARCHAR2(30) ATTRIBUTE VARCHAR2(240) SCOPE VARCHAR2(240) NUMERIC_VALUE NUMBER(15,2) CHAR_VALUE VARCHAR2(240) DATE_VALUE DATE LONG_VALUE LONG SQL> insert into product_user_profile values('SQL*Plus','FSMITH','ROLES',NULL,NULL,'ROLE2',NULL,NULL); 1 row created. SQL> commit; Commit complete. SQL> conn fsmith/fsmith Connected. SQL> create table role(role_name varchar2(20)); create table role(role_name varchar2(20)) * ERROR at line 1: ORA-01031: insufficient privileges