Grants...

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

well ,you might have system as his default tablespace!

Query like this...

Code:

---

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

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

---

Cheers

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

Did you grant DBA to SCOTT?

select * from dba_role_privs
where grantee='SCOTT';

MH

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

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.

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

Sameer

Code:

---

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

---

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......

Code:

---

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!!!!

I understand.. whatever happening in my schema is wrong..

I have granted two roles.. CONNECT and RESOURCE

So I would like to know which privileges SCOTT has received through these two ROLES

Quote:

---

Originally posted by Sameer
Which DD will give me list of privileges given to ROLE
CONNECT and RESOURCE?

Sameer

---

Code:

---

SQL> /* I'm connected as sys/pwd as sysdba  */
SQL> select privilege, admin_option
  2  from dba_sys_privs
  3  where grantee='CONNECT';

PRIVILEGE                                ADM
---------------------------------------- ---
ALTER SESSION                            NO
CREATE CLUSTER                          NO
CREATE DATABASE LINK                    NO
CREATE SEQUENCE                          NO
CREATE SESSION                          NO
CREATE SYNONYM                          NO
CREATE TABLE                            NO
CREATE VIEW                              NO

8 rows selected.

---

I am giving up... :-((

CONNECT:

PRIVILEGE ADMIN_OPTION
---------------------------------------- ------------
ALTER SESSION NO
CREATE CLUSTER NO
CREATE DATABASE LINK NO
CREATE SEQUENCE NO
CREATE SESSION NO
CREATE SYNONYM NO
CREATE TABLE NO
CREATE VIEW NO

RESOURCE:

PRIVILEGE ADMIN_OPTION
---------------------------------------- ------------
CREATE CLUSTER NO
CREATE INDEXTYPE NO
CREATE OPERATOR NO
CREATE PROCEDURE NO
CREATE SEQUENCE NO
CREATE TABLE NO
CREATE TRIGGER NO
CREATE TYPE NO


Something weird is happening in my case ..

Anyway Thanks a lot.. I know it should work properly as it is working in u r case and in Jeff's case.. Should work in my case also.. May be my its time to go home! :-)

Thanks

Sameer

drop scott, recreate him and then see.
Do it tomorrow, i'm too getting out of my office..;)

Thats the only option..

sometime simple things are difficult to understand

ciao

Sameer