Grants...

[Sameer]

Hi All,

How I am able to execute SELECT on system.test table as I have granted only INSERT priviledge to scott?

SQL> connect system/[email protected]
Connect durchgeführt.
SQL> CREATE TABLE test (datum DATE);

Tabelle wurde angelegt.

SQL> GRANT INSERT ON test TO SCOTT;

Benutzerzugriff (Grant) wurde erteilt.

SQL> connect scott/[email protected]
Connect durchgeführt.
SQL> CREATE OR REPLACE PROCEDURE insdate AS
2 BEGIN
3 INSERT INTO system.test values (SYSDATE);
4 COMMIT;
5 END;
6 /

Prozedur wurde angelegt.

SQL> exec insdate;

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> SELECT * FROM system.test;

DATUM
--------
01.08.02

SQL> show user
USER ist "SCOTT"
SQL>
SQL>
SQL> select * from USER_TAB_PRIVS_RECD;

OWNER TABLE_NAME GRANTOR PRIVILEGE GRA
-----------------------------------------------------------
SYSTEM TEST SYSTEM INSERT NO

Thanks

Sameer

[Tarry]

well ,you might have system as his default tablespace!

[Tarry]

Query like this...

SQL>  select username,default_tablespace
  2   from dba_users
  3  where username = 'SCOTT';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT                          USERS

Cheers

[Sameer]

Hi...

Yes..

SCOTT's default tablespace was SYSTEM, but now I have altered SCOTT's default tablespace to USERS and also re-granted the only INSERT on test to SCOTT but still when I connect as SCOTT I still able to execute

SELECT * from SYSTEM.test;

Is it because I use SYSTEM.test table in one of the SCOTT's procedure? But how come that.. Procedure is onwed by SCOTT and not SYSTEM

Sameer

[Mr.Hanky]

Did you grant DBA to SCOTT?

select * from dba_role_privs
where grantee='SCOTT';

MH

[Sameer]

No.. I haven't :-( but still .. see below..


SQL> show user
USER is "SYSTEM"
SQL>
SQL> select * from dba_role_privs where grantee='SCOTT';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT CONNECT NO YES
SCOTT RESOURCE NO YES

SQL> create table test1 (name varchar2(10));

Table created.

SQL> insert into test1 values ('sameer');

1 row created.

SQL> commit;

Commit complete.

SQL> connect scott/[email protected]
Connect durchgeführt.
SQL>
SQL> select * from system.test1;

NAME
----------
sameer

[Tarry]

user scott has either select any priv(thru any of these 2 roles connect or resource) or he has object privilege on that particular table of user system).

Try to query something on the sys users tables...
otehrwise it's not possible to query any other tables.

[Sameer]

Which DD will give me list of privileges given to ROLE
CONNECT and RESOURCE?

Sameer

[marist89]

SQL> create user a identified by a
  2  temporary tablespace temp default tablespace users;

User created.

SQL> grant create session, create table to a;

Grant succeeded.

SQL> alter user a quota unlimited on users;

User altered.

SQL> create user b identified by b
  2   temporary tablespace temp default tablespace users;

User created.

SQL>  grant create session to b;

Grant succeeded.

SQL> connect a/a
Connected.
SQL> create table xyz (x number(10), y date, z varchar2(10));

Table created.

SQL> grant insert on xyz to b;

Grant succeeded.

SQL> connect b/b
Connected.
SQL> desc a.xyz
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER(10)
 Y                                                  DATE
 Z                                                  VARCHAR2(10)

SQL> select count(*) from a.xyz;
select count(*) from a.xyz
                       *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> insert into a.xyz values (1, sysdate, 'foo');

1 row created.

SQL> commit;

Commit complete.
SQL> select count(*) from a.xyz;
select count(*) from a.xyz
                       *
ERROR at line 1:
ORA-01031: insufficient privileges

[Tarry]

if you created scott with the scott.sql dem,o script and roles connect and resource weren't altered then he must have had the select any table priv.. as you can see......

SQL> conn system/manager
Connected.
SQL> create table tarry
  2  (name varchar2(30),
  3  sex varchar2(10))
  4  /

Table created.

SQL> insert into tarry
  2  values('Pamela','hottt!!');

1 row created.

SQL> disconnect
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL> conn scott/tiger
Connected.
SQL> select * from system.tarry
  2  /
select * from system.tarry
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn sys/man as sysdba
Connected.

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> select * from system.tarry;

NAME                           SEX
------------------------------ ----------
Pamela                         hottt!!

Cheers!!!!