Aw: Re: Re: Need to drop role with question marks in the role name

From: <rogel_at_web.de>
Date: Wed, 13 Feb 2019 20:48:00 +0100
Message-ID: <trinity-07ec33ff-a69f-4370-9c00-0814b119f4b3-1550087280191_at_3c-app-webde-bs06>

That looks more like GS_READONLY and not APP_READONLY.

Anyway,

SQL> declare s clob;
2 begin
3 s:='create role"GS_READONLY"';
4 execute immediate s;
5 s:='create role"'||chr((239*256+191)*256+189) || chr((239*256+191)*256+189) || chr((239*256+191)*256+189) || 'GS_READONLY' || chr((239*256+191)*256+189) || chr((239*256+191)*256+189) || chr((239*256+191)*256+189)||'"';
6 execute immediate s;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> select dump(role) from dba_roles where role like '%GS_READONLY%';

DUMP(ROLE)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=11: 71,83,95,82,69,65,68,79,78,76,89
Typ=1 Len=29: 239,191,189,239,191,189,239,191,189,71,83,95,82,69,65,68,79,78,76,89,239,191,189,239,191,189,239,191,189

gives exactly your output.

Drop it via

SQL> declare s clob;
2 begin
3 s:='drop role"GS_READONLY"';
4 execute immediate s;
5 s:='drop role"'||chr((239*256+191)*256+189) || chr((239*256+191)*256+189) || chr((239*256+191)*256+189) || 'GS_READONLY' || chr((239*256+191)*256+189) || chr((239*256+191)*256+189) || chr((239*256+191)*256+189)||'"';
6 execute immediate s;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> select dump(role) from dba_roles where role like '%GS_READONLY%';

no rows selected

or just

begin
for d in (select 'drop role"' || role || '"' s from dba_roles where role like '%GS_READONLY%') loop
execute immediate d.s;
end loop;
end;
/

Gesendet: Mittwoch, 13. Februar 2019 um 19:00 Uhr
Von: "Sandra Becker" <sbecker6925_at_gmail.com>
An: rogel_at_web.de
Betreff: Re: Re: Need to drop role with question marks in the role name

DUMP(ROLE)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=29: 239,191,189,239,191,189,239,191,189,71,83,95,82,69,65,68,79,78,76,89,239,191,189,239,191,189,239,191,189
Typ=1 Len=11: 71,83,95,82,69,65,68,79,78,76,89

On Wed, Feb 13, 2019 at 10:53 AM <rogel@web.de> wrote:

what's the output of

select dump(role) from dba_roles where role like '%APP_READONLY%';

?

Gesendet: Mittwoch, 13. Februar 2019 um 18:48 Uhr
Von: "Sandra Becker" <sbecker6925@gmail.com>
An: "Testa, Joseph S" <TESTAJ3@nationwide.com>
Cc: oracle-l <oracle-l@freelists.org>
Betreff: Re: Need to drop role with question marks in the role name

I've tried both single and double quotes. Neither works.

On Wed, Feb 13, 2019 at 10:19 AM Testa, Joseph S <TESTAJ3@nationwide.com> wrote:

Sorry meant Double quotes “ “

-------------------------------

Quit doing what is easy and do what is right.

Joseph Testa

Database Services Security

From: Testa, Joseph S
Sent: Wednesday, February 13, 2019 12:19 PM
To: sbecker6925@gmail.com; oracle-l <oracle-l@freelists.org>
Subject: RE: [EXTERNAL] Need to drop role with question marks in the role name

Single quotes around it?

-------------------------------

Quit doing what is easy and do what is right.

Joseph Testa

Database Services Security

From: oracle-l-bounce@freelists.org <oracle-l-bounce@freelists.org> On Behalf Of Sandra Becker
Sent: Wednesday, February 13, 2019 12:17 PM
To: oracle-l <oracle-l@freelists.org>
Subject: [EXTERNAL] Need to drop role with question marks in the role name

Nationwide Information Security Warning: This is an external email. Do not click on links or open attachments unless you trust the sender.

Oracle EE12c

Somehow a role got created with the name ???APP_READONLY??? and it is owned by the sys user. I've been asked to delete the role since it isn't valid, but I haven't been able to figure out how to delete it. Any ideas?

Sandy

--

Sandy B.

--

Sandy B.

Sandy B.

--
http://www.freelists.org/webpage/oracle-l






(image/jpeg attachment: 01-part)




(image/jpeg attachment: 02-part)

Received on Wed Feb 13 2019 - 20:48:00 CET

This message: [ Message body ]
Next message: Jeff Chirco: "hugepages question"
Previous message: k a: "Re: exadata write performance problems"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message