Home » RDBMS Server » Security » Grant ROLE to user takes longer time (Oracle 12c, windows 2012)
Grant ROLE to user takes longer time [message #648661] Mon, 29 February 2016 01:50 Go to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi All,

Having a problem of granting role.

I am trying to grant a role to an user, which takes long time and not finishing.

I tried with Locally and Remotely.

Found no error in ALERT LOG FILE.

No SYNTAX error

Kindly help.

Regards
Muktha
Re: Grant ROLE to user takes longer time [message #648664 is a reply to message #648661] Mon, 29 February 2016 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Trace the session.

Re: Grant ROLE to user takes longer time [message #648669 is a reply to message #648664] Mon, 29 February 2016 04:16 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Michel,

Here is the Trace File Output. I couldn't see any issues, except the Library Cache Miss during the parsing stage.
Kindly guide me.

SQL ID: 0qshmpqwz6mmz Plan Hash: 0

grant <role> to <user>;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109  
********************************************************************************

SQL ID: 5hrvvu1r771m5 Plan Hash: 415205717

SELECT VALUE$ 
FROM
 SYS.PROPS$ WHERE NAME = 'OGG_TRIGGER_OPTIMIZATION'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           0

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  TABLE ACCESS FULL PROPS$ (cr=3 pr=0 pw=0 time=34 us cost=2 size=28 card=1)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           0

Misses in library cache during parse: 0



Regards
Muktha
Re: Grant ROLE to user takes longer time [message #648671 is a reply to message #648669] Mon, 29 February 2016 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The raw trace with WAIT events.

Re: Grant ROLE to user takes longer time [message #648672 is a reply to message #648671] Mon, 29 February 2016 05:16 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Michel,

Here is the Raw trace file with the Wait Events.

I have used the method of 'EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);"

Thanks and Regards
Muktha
Re: Grant ROLE to user takes longer time [message #648674 is a reply to message #648672] Mon, 29 February 2016 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Here is the Raw trace file with the Wait Events.


Where?

Re: Grant ROLE to user takes longer time [message #648676 is a reply to message #648674] Mon, 29 February 2016 06:06 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Michel,

Sorry by mistaken it was sent without the attachment.

Here it is.

Regards
Muktha
  • Attachment: Isslapps.txt
    (Size: 1.93KB, Downloaded 92 times)
Re: Grant ROLE to user takes longer time [message #648677 is a reply to message #648676] Mon, 29 February 2016 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Incomplete file.

Re: Grant ROLE to user takes longer time [message #648719 is a reply to message #648677] Tue, 01 March 2016 23:58 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Michel,

Last time I traced another session through SYS Login, but this time I traced the own session as below.



ALTER SESSION SET SQL_TRACE TRUE;

BEGIN
    DBMS_SESSION.session_trace_enable (waits          => TRUE,
                                       binds          => FALSE,
                                       plan_stat      => 'all_executions'
                                      );
 END;



Here is the trace output file with wait event details.

Regards
Muktha
Re: Grant ROLE to user takes longer time [message #648722 is a reply to message #648719] Wed, 02 March 2016 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WAIT #1895312376: nam='enq: CB - role operation' ela= 198092906 name|mode=1128398854 0=0 0=0 obj#=-1 tim=2403565707281
Someone is holding the master CBAC (Code Based Access Control) lock due to new method to grant roles to PL/SQL units. Check V$LOCK.
ERROR #1895312376:err=3113 tim=2403565707442
ORA-03113 -> you have a trace file.
Check it, then check MOS/Metalink.

[Updated on: Wed, 02 March 2016 00:13]

Report message to a moderator

Re: Grant ROLE to user takes longer time [message #648736 is a reply to message #648722] Wed, 02 March 2016 01:42 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Michel,

FYI,

I tried with another roles to another users, but couldn't get succeed.

Nothing I could found in the v$lock.

I used the below query to find out the lock of the object.

SELECT p.username pu,
        s.username su,
        s.status stat,
        s.sid ssid,
        s.serial# sser,
        lpad(p.spid,7) spid,
        substr(sa.sql_text,1,540) txt
   FROM v$process p, v$session s, v$sqlarea sa
  WHERE p.addr=s.paddr
    AND s.username is not null
    AND s.sql_address=sa.address(+)
    AND s.sql_hash_value=sa.hash_value(+)
    and s.sid in (select sid from v$lock)
 ORDER BY 1,2,7


Sorry to say that I am unaware of the new feature CBAC of 12c version.
Will take time to read that.

Help me.

Regards
Muktha
Re: Grant ROLE to user takes longer time [message #648738 is a reply to message #648736] Wed, 02 March 2016 01:45 Go to previous message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I used the below query to find out the lock of the object.


Just query V$LOCK.

Previous Topic: Applying ROLE-based security to columns in a View in Oracle
Next Topic: Role-based Sensitive Column Masking by VPD (ORA-28112)
Goto Forum:
  


Current Time: Tue Nov 21 08:07:25 CST 2017

Total time taken to generate the page: 0.07141 seconds