Privileges on Oracle roles - Limit?


I am working on a system that will provide some basic roles to the user. As the data in the application grows, the number of (object) privileges for each role in expected to grow to about 2000 or more.
Does oracle have a limitation on the number of privileges that can be granted to a role

Could there be a hit on the performance if each role has these high number of privileges granted to it.

Posted On: Thursday 1st of November 2012 04:36:33 AM Total Views:  155
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




privileges do not work through roles

Hi I have created role role_A and granted select any table system privileges to role_A. I have also created user A. I grant the role_A to user A. However, when I login to user A I could not select any tables. What's wrong here Is there a scrip I need to run to enable this feature. It seems like all of the privileges I grant through a role do not work. I have to specifically grant privileges to user. Please help!
VIEWS ON THIS POST

126

Posted on:

Thursday 1st November 2012
View Replies!

privileges

please help me to understand the problem here. CREATE OR REPLACE VIEW MDSYS.USER_SDO_GEOM_METADATA ( TABLE_NAME, COLUMN_NAME, DIMINFO, SRID ) AS SELECT SDO_TABLE_NAME TABLE_NAME, SDO_COLUMN_NAME COLUMN_NAME, SDO_DIMINFO DIMINFO, SDO_SRID SRID FROM SDO_GEOM_METADATA_TABLE, (select username from user_users) WHERE sdo_owner = username / GRANT DELETE ON MDSYS.USER_SDO_GEOM_METADATA TO PUBLIC / GRANT INSERT ON MDSYS.USER_SDO_GEOM_METADATA TO PUBLIC / GRANT SELECT ON MDSYS.USER_SDO_GEOM_METADATA TO PUBLIC / GRANT UPDATE ON MDSYS.USER_SDO_GEOM_METADATA TO PUBLIC / I have three users with DBA privileges. one of them can access the data in this view but the others two are not. Can someone explain to me what was the problem here \t and what I should do to make the other two users access the data on this view.
VIEWS ON THIS POST

221

Posted on:

Thursday 1st November 2012
View Replies!

Users privileges

Somebody can help me with this: I want to have the list of the users who have the privileges of creating objects. There is a mess here, I have to clean up a database which has been without control for several years.
VIEWS ON THIS POST

223

Posted on:

Thursday 1st November 2012
View Replies!

privileges to backup controlfiles

I want to set up an user so I can use him to backup the control files; beside granting "ALTER DATABASE" to this user, is there any other roles or privileges I can grant him so he can backup the control files. "ALTER DATABASE" and too powerful for this user.
VIEWS ON THIS POST

274

Posted on:

Friday 2nd November 2012
View Replies!

privileges managing

Suppose that we have u1, u2 and a role SELECT_U1_TAB (grant select on U1's tabs) granted to U2. Suppose that we have a U2's view :V2 (using an U1 tab). U2 must have an object privilege on U1 tab used by the view. Deing have a role BY SCHEMA with select privilege (Role 1: SELECT_U1_TAB, Role2 : SELECT_U2_TAB) in order to manage privilege with role (and not with explicit object privilege), this is not always possible as it is the case when a view is using a tab in another schema (U2 view using an U1 tab). Anyone have a proposition or an idea to avoid managing explicit privilege (Indeed, we want to have something like the privilege SELECT ANY TABLE but BY SCHEMA and not for all SCHEMAS).
VIEWS ON THIS POST

222

Posted on:

Friday 2nd November 2012
View Replies!

Public Synonym - Insufficient privileges

Hi all, I am trying to create a public synonym for my tables. When I write the command 'CREATE PUBLIC SYNONYM....' on the sql prompt, I am able to get it done but when I try to do it using an "Execute immediate ('create public syonym....') " in a stored procedure, I get a "ORA-01031: Insufficient privileges" error. Does anyone know what I am missing. This user has connect & resource roles and I even granted him the 'Create any synonym' privilege too.
VIEWS ON THIS POST

242

Posted on:

Friday 2nd November 2012
View Replies!

privileges that a role has

can anyone tell me the dba_views that I can use to find out what priviledges that a role has Example if I have a role called data_role, how do I find out the priviledges that has been assigned to the data_role
VIEWS ON THIS POST

206

Posted on:

Friday 2nd November 2012
View Replies!

system privileges

Hi DBAs, I am in the situation to restrict to create any new tables in my user. I tried the following.... REVOKE CREATE TABLE FROM USER; But i got the following error msg. ORA-01952: system privileges not granted to 'USER' could anyone give me a script for this. Thanx in advance.
VIEWS ON THIS POST

215

Posted on:

Saturday 3rd November 2012
View Replies!

reg privileges -- asap

I have question regarding privileges It is basically ,i want to restrict people from using ddl -- create ,alter ,drop dml -insert update and delete . I should give only select statement for some of them . I am using toad(quest software ) ,is there any way i can restrict on tad or by granting privileges . Onemore thing is ,we have built in software taod with our system ,when people log in they can get directly connected to database by clinking the toad option . what way i can restrict the user from the above options
VIEWS ON THIS POST

367

Posted on:

Saturday 3rd November 2012
View Replies!

Ran Catexp.sql (prior to export) : Insufficient privileges?

Ran this as SYSTEM on a new 9.2.0.1 database, and the script runs but as it finishes the last few messages echoed back are: UPDATE prop$ SET value$ = 8 WHERE name = 'EXPORT_VIEWS_VERSION' * ERROR at line1: ORA-00942: table or view does not exist Throughout the session feedback shows: Ora-01031: insufficient privileges. As what user should I run catexp.sql
VIEWS ON THIS POST

217

Posted on:

Saturday 3rd November 2012
View Replies!

ORA-1031: insufficient privileges

Dear all, I am trying to connect my asp.net app to a database as sysdba and i get "ORA-1031: insufficient privileges". I can login as sysdba using sqlplus, pl/sql developer and toad, so I am guessing that I am missing some kind of dodgy Oracle setting. I have tried 2 different connection strings TNS :Data Source = server; User Id = sys; Password = pwd; DBA Privilege = SYSDBA; Direct:Data Source = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521))) (CONNECT_DATA = (SID = ORCL))); User Id = sys; Password = pwd; DBA Privilege = SYSDBA; Any ideas
VIEWS ON THIS POST

293

Posted on:

Saturday 3rd November 2012
View Replies!

Re: ORA-01031: insufficient privileges when conn sys/password as sysdba

Any idea It is newly copied db. I can connect localy. I get the error when I try to connect as sys remotely. other user can connect okay, except sys. Any idea
VIEWS ON THIS POST

219

Posted on:

Sunday 4th November 2012
View Replies!

When selecting from a view ORA-01031: insufficient privileges

As DBA I have granted select privileges on a view to a user, but whenever I login as that user and try to select from the view I get the message 'ORA-01031'. When I run the query in the view as the user I do not have problem and can see the results. This view joins tables from more than one schema and does a distinct on the resultset. The user has select permission on the view and a default role that has reference on all the objects on the other schemas. Schema owner of the view has SELECT privileges on all the tables from other schemas referenced in the view.
VIEWS ON THIS POST

211

Posted on:

Wednesday 7th November 2012
View Replies!

What table objet privileges cannot be granted to a role

Can someone tell me what table object privilieges cannot be granted to a role. Is it ALTER and INDEX
VIEWS ON THIS POST

259

Posted on:

Wednesday 7th November 2012
View Replies!

user roles and privileges

Hi DBAs, Does anyone have a scipt to find out the various oracle roles and privileges for users in the database
VIEWS ON THIS POST

245

Posted on:

Wednesday 7th November 2012
View Replies!

user privileges and grant

In which table or view i can find out the privileges granted to a user account
VIEWS ON THIS POST

335

Posted on:

Wednesday 7th November 2012
View Replies!

Update privileges

let's say if I grant the UPDATE privilge on the table to a role. and the role is granted to the user. Why do I have to specificly grant the same UPDATE to the user in the body of the procedure again\t\t
VIEWS ON THIS POST

255

Posted on:

Wednesday 7th November 2012
View Replies!

Triggers and privileges

I'm running Oracle 8.0.6 on HP-UX 11.0. I have a table (monitor_table) created by SYSTEM, and a trigger (pvx_tr) created by SYSTEM on a table owned by BOB, that inserts records into system.monitor_table whenever the trigger criteria are met. The user BOB has not been granted either "insert any table" or "insert on system.monitor_table". According to the Oracle documentation: " If the trigger issues SQL statements or calls procedures or functions, then the owner of the schema to contain the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner, rather than acquired through roles." However, when the user BOB modifies the data in the table with the trigger, the trigger successfully inserts new records into the system.monitor_table. My question is, why does this work I would expect it to fail with an "insufficient privilege" error.
VIEWS ON THIS POST

327

Posted on:

Wednesday 7th November 2012
View Replies!

sqlplus ORA-01031 Insufficeint privileges

I am trying to start sqlplus on Oracle 10gR2 (on Sun server). Keep getting error ORA-01031 Insufficient privileges. Here's how I am executing this from the start as oracle user: login as: bill Using keyboard-interactive authentication. Password: Last login: Tue Oct 24 13:47:48 2006 from 192.168.3.38 Welcome to the Sun Fire-V240 Server "Jacob"! -bash-3.00$ su oracle Password: $ cd /export/ORACLE_HOME/bin $ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 24 13:54:11 2006 Copyright (c) 1982, 2005, Oracle. rights reserved. SQL> connect sys as sysdba Enter password: ERROR: ORA-01031: insufficient privileges SQL> connect oracle/oracle as sysdba ERROR: ORA-01031: insufficient privileges SQL> I've tried several combinations of login attempts but keep getting the same error. I've also referenced others' posts here at this site but don't see any solution that I can extract . Any ideas from anyone who has experienced this problem, I'd appreciate some help.
VIEWS ON THIS POST

214

Posted on:

Wednesday 7th November 2012
View Replies!

user privileges

Hi I have a procedure owned by user1. A global temporary table and a package are also a part of the procedure. I wanted to give user2 , privilege to execute this procedure. So, i granted execute privilege on both package and procedure and also, select, delete etc on the global temp table. Still, when I try to execute the procedure as user2, it comes out with the message that "ORA-00942: table or view does not exist". What else is needed to be done to execute the procedure as user2 I am running 8.1.6 on compaq tru-64
VIEWS ON THIS POST

220

Posted on:

Wednesday 7th November 2012
View Replies!