Re: Sequence problem driving me crazy

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/06
Message-ID: <8hk1un$ro3$1@nnrp1.deja.com>#1/1

In article <393D5D19.62E7B5F2_at_Unforgetable.com>,   Walter T Rejuney <BlueSax_at_Unforgetable.com> wrote:
> Database: 7.3.4
> OS: Unixware 2.1.2
>
> I have a simple sequence names MYSEQ which I have created in a schema
> named XYZ. Therefore the fully qualified name would be XYZ.MYSEQ.
>
> This sequence works fine when I'm logged in as XYZ, but I want to give
> the logins OPS$XYZ and XYZDBA the ability to get values from the
> sequence and I want to do this via a role called XYZ_SELECT_ROLE which
> is assigned to XYZ, OPS$XYZ and XYZDBA
>
> However, when I enter the command:
>
> grant select on xyz.myseq to xyz_select_role I receive a 1031 error.
>
> I even tried dropping the sequences and recreating them using the
 system
> login but specifying xyz.myseq. Again, the sequence created fine and
> works fine when logged in as xyz, but I cannot give grants to either a
> specific login or to a role and I don't understand why.
>
> I do not want to go the route of giving SELECT ANY SEQUENCE to the
 role
> because I think that is sloppy security and I don't like to do things
> the sloppy way.
>
> What makes this really baffling to me is that I can do:
>
> grant select on xyz.myseq to ops$xyz;
>
> and that will work. It is just when assigning it to a role that the
> problem occurs.
>

kcs as wtr-

Who is doing the grant? Is it XYZ or someone else. I'm going to guess its someone else. And that someone else has NOT been granted select on xyz.myseq WITH GRANT OPTION. Consider this chain of events:

SQL> create user xyz identified by xyz;
User created.

SQL> grant connect, resource to xyz;
Grant succeeded.

SQL> create role xyz_select_role;
Role created.

SQL> connect xyz/xyz
Connected.
SQL> create sequence myseq;
Sequence created.

SQL> REM this will work since I OWN the sequence SQL> grant select on myseq to xyz_select_role; Grant succeeded.

SQL> revoke select on myseq from xyz_select_role; Revoke succeeded.

SQL> connect system/manager
Connected.
SQL> select xyz.myseq.nextval from dual;

   NEXTVAL

---

         1

SQL> REM I can see the sequence, I'm a DBA
SQL> REM but i CANNOT grant on it...
SQL> grant select on xyz.myseq to xyz_select_role;
grant select on xyz.myseq to xyz_select_role
                    *

ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect xyz/xyz
Connected.
SQL> grant select on myseq to system WITH GRANT OPTION; Grant succeeded.

SQL> connect system/manager
Connected.
SQL> REM But now I can...
SQL> grant select on xyz.myseq to xyz_select_role; Grant succeeded.

SQL> So, either do the grant as XYZ or grant select on the sequence WITH GRANT option when connect as xyz to the person wanting to do the grant to the role.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Jun 06 2000 - 00:00:00 CDT