Solved

Creating Roles in Oracle 8i

Posted on 2003-12-05
7
1,156 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:AdilK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 

Expert Comment

by:kp397
ID: 9884146
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.
0
 

Author Comment

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

Expert Comment

by:kp397
ID: 9884392
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AdilK
ID: 9884634
Well for example I dont want to the user to select from view SYS.ALL_USER  

Adil
0
 

Expert Comment

by:kp397
ID: 9884688
Connect to SYS and Revoke permissions from user.
0
 

Author Comment

by:AdilK
ID: 9884780
Revoke what Permissions...!!! Please explain !
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 100 total points
ID: 9885559
by default,

A user account created has not a single sys or object privileges, not to mention to select sys tables.

For example, when you issue
SQL>create user test identified by test;

up to this point , you cannot even connect to the database using 'test' account,

but once you assign "CONNECT" to it, it can see a certain number of tables, like tables starting with "ALL_" and "USER_" ...because these tables when created, are granted SELECT to PUBLIC

so if you want to revoke all the select on ALL_USERS table, you have to do:

log in as sys

SQL>revoke SELECT on ALL_USERS from PUBLIC;

then user account "test" lost the select privilege on SYS.ALL_USERS...

Hope this helps a bit
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question