OraFAQ Forum: Server Administration » Duplicate permissions entries and can't create new ones

Home » RDBMS Server » Server Administration » Duplicate permissions entries and can't create new ones (10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Duplicate permissions entries and can't create new ones [message #617090]

Tue, 24 June 2014 17:17

amerton
Messages: 3
Registered: June 2014

Junior Member

I have a process which creates files, then uses Java to compress them into a single .zip file, then deletes the uncompressed files, but I have started getting errors which I can't work around.

Firstly, my USER_JAVA_POLICY view shows duplicate entries:

Select  name, action, enabled, count(*) 
from    user_java_policy
where   grantee_name = 'SSCMIG_DEV'
Group by name, action, enabled
having count(*) > 1;

/home/t924969/sscmig/outgoing/Rollback-WN-BNG01-001-002	read	ENABLED	2
/home/t924969/sscmig/outgoing/Rollback-WN-BNG02-001-001	read	ENABLED	2
/home/t924969/sscmig/outgoing/Rollback-001.zip	        write	ENABLED	3

Could this have been caused by my NOT committing after creating permissions (I didn't realise I needed to commit/rollback for a while after I started making these entries), and then granting the same permisson again (from another session)?

Secondly, I then tried to delete the duplicates. According to the docs, I need to revoke, then delete, the permission. I get the following:

exec dbms_java.revoke_permission('SSCMIG_DEV','java.io.FilePermission','/home/t924969/sscmig/outgoing/Rollback-001.zip','read');

[Error] Execution (6: 1): ORA-01653: unable to extend table SYS.IDL_UB1$ by 1024 in tablespace SYSTEM
ORA-06512: at "SYS.DBMS_JAVA", line 339
ORA-06512: at line 1

I get the same error if I try

exec sys.dbms_java.disable_permission(160);

For technical and bureaucratic reasons, extending the TS is NOT an option (unsupported very old box with no way to add disk, etc).

If I try to simply delete the entry nothing happens, which is what the docs say - I can't delete until I revoke/disable it.

1. Why does it try to extend the table space? shouldn't it just be updating the existing entry?

2. Can I work around this by connecting as SYS/SYSDBA and directly updating the STATUS in the policy table?

3. Is there a solution which DOESN'T involve more disk for the TS? Smile

Report message to a moderator

Re: Duplicate permissions entries and can't create new ones [message #617091 is a reply to message #617090]

Tue, 24 June 2014 17:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/maillist/oracle-l/2001/05/14/1263.htm

http://grokbase.com/t/freelists.org/oracle-l/015e78sdv4/sys-idl-ub1

http://mwidlake.wordpress.com/2009/08/03/why-is-my-system-tablespace-so-big/

Good Luck!

Report message to a moderator

Re: Duplicate permissions entries and can't create new ones [message #617093 is a reply to message #617090]

Tue, 24 June 2014 18:19

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

amerton wrote on Tue, 24 June 2014 18:17

Firstly, my USER_JAVA_POLICY view shows duplicate entries.
Could this have been caused by my NOT committing after creating permissions

Most likely there are no duplicates in USER_JAVA_POLICY. There are two kinds of java permissions: grant and restrict. Most likely same grantee was granted both grant and restrict on same permission type, permission name and permission action:

SQL> execute dbms_java.restrict_permission('SCOTT','java.io.FilePermission','c:temp','read');

PL/SQL procedure successfully completed.

SQL> execute dbms_java.restrict_permission('SCOTT','java.io.FilePermission','c:temp','read');

PL/SQL procedure successfully completed.

SQL> select  name,
  2          action,
  3          enabled,
  4          count(*)
  5    from  user_java_policy
  6    where grantee_name = 'SCOTT'
  7    group by name,
  8             action,
  9             enabled
 10    having count(*) > 1
 11  /

NAME                 ACTION               ENABLED    COUNT(*)
-------------------- -------------------- -------- ----------
c:temp               read                 ENABLED           2

SQL> select  kind,
  2          name,
  3          action,
  4          enabled,
  5          count(*)
  6    from  user_java_policy
  7    where grantee_name = 'SCOTT'
  8    group by kind,
  9             name,
 10             action,
 11             enabled
 12    having count(*) > 1
 13  /

no rows selected

SQL>

SY.

Report message to a moderator

Re: Duplicate permissions entries and can't create new ones [message #617094 is a reply to message #617091]

Tue, 24 June 2014 18:31

amerton
Messages: 3
Registered: June 2014

Junior Member

Thanks for the links - I've actually found all of them previously, and none helped Sad

The two biggest tables in my SYSTEM are IDL_UB1$ at 174MB and SOURCE$ at 54 MB.

We've dropped 17 old schemas (this DB is used by our team for sundry projects, so gets a bit bloated over time), and although I can't see any more free space in the tablespace, I no longer get the errors. Most of them did have packages, sowere taking up space in SOURCE$ and/or IDL_UB1$.

I have successfully run disable_permission/delete_permission for each (unwanted) entry in the table and can now continue.

Thanks for the help Smile

Report message to a moderator

Re: Duplicate permissions entries and can't create new ones [message #617095 is a reply to message #617094]

Tue, 24 June 2014 18:36

amerton
Messages: 3
Registered: June 2014

Junior Member

@solomon - no, they were the same ACTION values as well - the columns above are name, action, status, count - I had two entries for the same path with read, for example.

Try doing the same test you did above, but run the second one in a different session before commiting the first, then commit both - that's my theory for how I created the duplicates, but can't afford the time to confirm it Smile

Thanks for the response though Smile

Report message to a moderator

Re: Duplicate permissions entries and can't create new ones [message #617098 is a reply to message #617095]

Tue, 24 June 2014 20:23

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

KIND, not action:

SQL> desc user_java_policy
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ==>KIND                                               VARCHAR2(8)
 GRANTEE_NAME                              NOT NULL VARCHAR2(30)
 TYPE_SCHEMA                               NOT NULL VARCHAR2(30)
 TYPE_NAME                                          VARCHAR2(4000)
 NAME                                               VARCHAR2(4000)
 ==>ACTION                                             VARCHAR2(4000)
 ENABLED                                            VARCHAR2(8)
 SEQ                                                NUMBER

SQL>

SY.

Report message to a moderator

Re: Duplicate permissions entries and can't create new ones [message #617099 is a reply to message #617098]

Tue, 24 June 2014 20:25

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

Just issue:

select  name,
        action,
        enabled,
        min(kind) kind1,
        max(kind) kind2 
  from  user_java_policy
  where grantee_name = 'SSCMIG_DEV'
  group by name,
           action,
           enabled
  having count(*) > 1
/

and post results.

SY.

[Updated on: Tue, 24 June 2014 20:26]

Report message to a moderator

Previous Topic:	Undo Tbs is not clearing/Flushing
Next Topic:	ORA-00376: file 24 cannot be read at this time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 22 22:05:55 CST 2025