Skip to content

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
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: