asked on

Creating Roles in Oracle 8i

I am having problems creating a Role. I am new to all this DB stuff so please be patient.
I want to create a Role type in Oracle and restrict this role to only be able to do SELECTS on certain Schema. Following is some Script that I wrote :

CREATE ROLE "APP_USER" NOT IDENTIFIED;
GRANT SELECT ON "APP"."CUSTOMER" TO "APP_USER"
GRANT SELECT ON "APP"."SHIPMENT" TO "APP_USER"
GRANT SELECT ON "APP"."V_INVLIST" TO "APP_USER"
GRANT "CONNECT" TO "APP_USER";

Where APP is another User and the tables are the User Schema Tables.
Where I run this script from SQLPLUS I get :
ERROR at line 1:
ORA-01031: insufficient privileges
I tried logging in as SYS, SYSTEM, and Internal with SYSDBA privl.

Can anyone help.

Thanks
Adil

kp397

I donot understand why you are giving Connect previlege to a role here.

GRANT "CONNECT" TO "APP_USER";
This is assigns connect previleges to a database user.
You don't need this stmt for your purpose.

AdilK

ASKER

Ok My fault. Also, can anyone tell me how can I restrict a user from selecting from the SYS or SYSTEM tables (Data Dictionary)

kp397

Do you have any specific tables.Normally thought it will show table, basic your cannot be able to select data from some of the tables.

AdilK

ASKER

Well for example I dont want to the user to select from view SYS.ALL_USER

Adil

kp397

Connect to SYS and Revoke permissions from user.

AdilK

ASKER