SQL & PL/SQL :: Privileges For AWR Report

Apr 24, 2012

I have a doubt, I wanted to set the AWR report in scheduler. So i have created the below procedure in SYS LOGING and working fine.

But when i trying to create in another user after grant required privileges, still it shows error message.

CREATE OR replace PROCEDURE Create a wr reports
AS
v_instance_number v$instance.instance_number%TYPE;
v_instance_name v$instance.instance_name%TYPE;
v_dbid v$database.dbid%TYPE;
v_file utl_file.file_type;
[code].........

View 7 Replies


ADVERTISEMENT

Grants And Privileges?

Jul 14, 2011

how to give grants and privileges that are assign to schema(clerk) to new schema(manager).

View 1 Replies View Related

Check All Object Privileges

Sep 6, 2011

How can i list all the object privileges from n user?

for example:

I granted to USER1:

GRANT ALL on my_table1 to USER1;
GRANT ALL on my_table2 to USER1;
GRANT ALL on my_tableN to USER1;

How can i list all privileges from this user over all tables?

I don't know if this is possible, but i wanna to list:

USER1 ----- UPDATE, INSERT, DELETE ON my_table1
USER1 ----- UPDATE, INSERT, DELETE ON my_table2
USER1 ----- UPDATE, INSERT, DELETE ON my_tableN

Is this possible?

View 1 Replies View Related

Insufficient Privileges To Start DB?

May 25, 2011

I have installed oracle on my linux machne recently as an oracle2 user. To day i found my listener up when start my as database ( by logging in to the sqlplus ) sqlplus /nolog - i get into sqlplus

When i startup the database

SQL>startup - I get this error ORA-01031: insufficient privileges

I tried to check if my oracle user is in the dba and oinstall group. it wasn't

I added the user to the group by doing usermod -G dba,oinstall oracle2

i chek in the group to see if the user has been added to the group i see the user added but i still get the same error ORA-01031: insufficient privileges when i try to start the database.

I read some where that you can try to uncomment the SQLNET.AUTHENTICATION_SERVICES line on sqlnet.ora file but on by my file (SQLNET.ORA) there is only one line written (NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT))

View 7 Replies View Related

ORA-01031 - Insufficient Privileges

Jul 14, 2010

i have three scheme in one instance for example

x,y and z

i create view in x as x_view

next step create view in y as y_view select from x_view ;

and finished, create another view in z as z_view select from y_view;

i gave grants to all user , but when i try this query with x user as select * from z.z_view get this message ORA-01031: insufficient privileges;

attention that connect as x and try select * from z.z_view

View 3 Replies View Related

SQL & PL/SQL :: Find Out Privileges On A Role

Oct 20, 2010

How can i find out or list all the privileges that were given to a role with sqlplus cmd?

View 2 Replies View Related

SQL & PL/SQL :: How To Get Total Object Privileges

Jun 1, 2010

Is there any data dictionary table to get the object grants for total objects in the database?

Using DBA_TAB_PRIVS, i get object grants for tables only. But i'm looking for remaining objects also. The list of remaining objects is below.

view, or materialized view
Sequence
Procedure, function, or package
User-defined type
Synonym for any of the preceding items
Directory, library, operator, or indextype
Java source, class, or resource

provide me the other data dictionary tables for querying.

View 3 Replies View Related

SQL & PL/SQL :: Packages - Roles And Privileges?

Aug 9, 2005

I'm not sure if this belong to this place. move it to the proper place.

I'm having an annoying problem: (I'm not sure if it is a problem)
- In a package body I'm trying to delete/update/insert/select the contents of a table in other schema.
- The respective synonym exists.
- I had created a role with the respective privileges over the synonym.
- I granted the role to the package's owner.
- I try to compile the package, but it keeps returning compilation errors. (Not table found)
- In standard SQL, I can delete/insert/update/select over the table.
- The only way to compile the package, without errors, is to grant the privileges directly to the package's owner.

Is it supposed to work in this way?

View 7 Replies View Related

Security :: Privileges For Stats

Aug 31, 2012

My tables are in ers_stg schema and code which collects state on these table are in etls_ers schema, what permission i need in order to get the stats collected from etls_srs schema. i am getting in sufficient privilege error.

View 1 Replies View Related

SQL & PL/SQL :: ORA-01031 / Insufficient Privileges

Dec 28, 2007

i have created a context from schema 'Schema1' using "accessed globally" option and created on package to set the values for conext. It is working fine.

When i deploy the same package on schema2 (remember the context is on schema1) and try to execute it on schema2 then it is giving me the following error.

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> exec krishna_pkg.MyProcedure
BEGIN krishna_pkg.MyProcedure; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "LGE2008DEV.KRISHNA_PKG", line 6
ORA-06512: at line 1

View 14 Replies View Related

SQL & PL/SQL :: Grant Privileges Remotely

May 25, 2010

Say we have db1 and db2 two databases installed on two different servers.For internal needs, I have to insert some data from a table t1 on db1 to a table t2 in db2. This can be done by issuing from a user on db1 :

insert into t2@dblink2
select *
from t1;

where dblink2 is a correct database link that points to u2 (the t2 owner for example) on db2.

Now what I want to do is to grant privileges remotely.Is there a way to issue somthing like

execute@dblink2 'grant select on t2 to public';

It would be like u2 has issued the statment...

I don't want to use OS scripts (.sh or .bat).

View 14 Replies View Related

What Is Meant By Roles And Privileges

Jun 27, 2012

What is meant by Roles and privileges?

View 4 Replies View Related

PL/SQL :: Viewing Privileges For A Role

Sep 9, 2013

I have a role R.I want to see what privileges have been granted to this Role R. Where should i query? 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE    11.2.0.3.0    Production"TNS for Solaris: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production

View 2 Replies View Related

PL/SQL :: ROLE Privileges In Oracle

Aug 1, 2012

How CONNECT Role in oracle works.

I have my schema "SchemaABC" with ROLES created as CONNECT and RESOURCE. Also it doesn't have DBA role (to say).

SQL> Select * from  User_Role_Privs;

USERNAME                       GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
SCHEMAABC                       CONNECT                        NO           YES          NO
SCHEMAABC                       RESOURCE                       NO           YES          NO

SQL>Currently we are able connect to this schema through code as well as from PL/SQL Developer. And uses it for all coding purposes like Create, Insert, Update, Stored Procedures, functions, triggers etc.

Now my question is, can this schema be changed to a role/privilege where all the above said activities can be done through code, but not from PL/SQL developer (making it to Read-only)? Yet, I will be using the same schema Name in code as well to connect from PL/SQL Developer.

I understand Connect role has big share in this. But if I remove the connect privilege(role), then will that work when I connect to it through code?

View 1 Replies View Related

PL/SQL :: Insufficient Privileges / Set ORACLE_SID

Aug 28, 2013

I installed 11g in my system.

Then I used to login as username@admin/password.

It returns error as ORA-12154: TNS:could not resolve the connect identifier specified. 

After that I tried sqlplus "/ as sysdba"

it returns as ORA-01031: insufficient privileges

and then i set ORACLE_SID and then try sqlplus "/ as sysdba"

it returns ORA-12560: TNS:protocol adapter error. 

View 1 Replies View Related

SQL & PL/SQL :: What Are The Privileges The User Is Having On A Object

Oct 2, 2010

How can we find out what are the privileges the user is having on a object.

For example I want to find what are the privileges the "uesr1" is having on "stud_table".

View 3 Replies View Related

SQL & PL/SQL :: No Privileges On Tablespace User

Jul 25, 2013

> conn sys as sysdba;
enter password:TIGER
>create user ram identified by ram;
>grant create session to ram;
>conn scott/tiger
>create table ram.emp2
as select * from emp;
>ora-error:- 01520 NO PRIVILEGES ON TABLESPACE 'USERS'

this is the query and i want to create emp2 table for user ram while i am connected to scott,
ealier i was able to do this but now oracle shows error 01950,

then i connected to sys again and ran this command >alter user scott quota unlimited on scott;
but it also not worked

View 2 Replies View Related

Spool All Privileges Of A Particular User

Nov 21, 2012

I am trying cleaner and simple way to spool out all privileges of a user "ABC" in a way that it generates grant statement for them.

View 6 Replies View Related

List All Users That Have Privileges On A Table

Aug 6, 2011

Is possible to list all users that have any privilege on a specific table?

View 2 Replies View Related

REVOKE ROLE - Disable All Privileges?

Mar 1, 2012

I am observing a strange behavior for the ROLES:

The DBA granted the RESOURCE role for my user USER1:

GRANT RESOURCE TO USER1;
The USER1 logged and activated the role RESOURCE.
CONN USER1/USER1
SET ROLE RESOURCE;

... it has some privileges in database ( UNLIMITED TABLESPACE for example that grant to him create table in any tablespace).

Now I created a new tablespace, but the user1 cannot use this new tablespace.So I will to revoke the RESOURCE for the USER1:REVOKE RESOURCE TO USER1;

It seems ok, but the user USER1 can still create tables in the new tablespace.I repared that UNLIMITED TABLESPACE still active, and the REVOKE RESOURCE just will have effect in the next LOGON of the USER1.

How can I REVOKE the ROLE and disable all the privileges from it immediately?

View 3 Replies View Related

Privileges Of Create Trigger - Procedures?

Aug 16, 2013

i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"

how can user "A" create trigger(etc.) on tables of user "B".

View 1 Replies View Related

Error / ORA-01031 / Insufficient Privileges

Sep 27, 2013

I am facing problems during login through sqlplus prompt.

It occurs in all the Linux boxes, with 10g and 11g databases, whereas in Windows it works good.

Sqlplus / as sysdba works good ( So there is no problem in OS Authentication)

Sqlplus sys/xxxx@SID as sysdba works good( Ther is no problem with password file)

But Sqlplus /@SID as sysdba is not working, It gives me an error message whereas in my windows box it works without any problems.

Error: ORA-01031 : Insufficient priveleges

I verified the service id entry in tnsnames.ora, i did not find any problem.

View 1 Replies View Related

SQL & PL/SQL :: Role Dependency - Additional Privileges

Aug 6, 2012

I want to find role dependency on another role.

1. If its a basic role (made of priviliges), what data dictionary view I should query?

2. If the role is made of some other role and additional priviliges, what query I should fire to find that?

3. Role is granted to which users?

View 15 Replies View Related

SQL & PL/SQL :: How To Know Schemas In A Role And Privileges On Objects

Jul 14, 2011

I have a role mfg_grp .I want to know, to whom the role is granted and which objects and which privileges on those objects are assigned to this role.

View 4 Replies View Related

Server Administration :: Privileges From One Schema To Other

Feb 10, 2012

I have 10 important schemas in my database,and i want to give only select privileges from SCHEMA PRODUSER to other 10 schemas. And also want that the new objects that are created in PRODUSER after granting the privileges are also have select privileges. Is it possible that i should directly grant select privileges from one schema to other without granting via individual objects(script to grant individual privileges from individual objects)

Details are as below:
database version: 9.2.0.8.0
OS version:
Microsoft Windows Server 2003
Standard Edition
Service Pack 2

View 1 Replies View Related

Security :: ORA-01929 / No Privileges To GRANT

Jun 28, 2011

When i try to grant all privilege to a object i get error

SQL> GRANT ALL PRIVILEGES ON tab1 TO PUBLIC;
GRANT ALL PRIVILEGES ON tab1 TO PUBLIC
*
ERROR at line 1:ORA-01929: no privileges to GRANT

how to check the user has privilege to use grant privilege or grant all privileges to object and what privileges exist in ALL PRIVILEGES.

View 5 Replies View Related

Security :: History Of Privileges Changes Done For A DB User

Jan 1, 2013

As how do I query, as when was the last time or rather list of privileges changes done for a db user and what was the changes made?

View 1 Replies View Related

Security :: Grant Privileges To A User?

Dec 10, 2010

I am a java developer and would like to install data locally in my computer for testing purpose.

I login as sysdba in sqlplus, created a user.

In the installation documentation it said :

This user should have at least the following privileges.

CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE USER, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION CREATE TRIGGER, AND CREATE TYPE.

If you choose, you can grant DBA role to this user.

Note: Coeus Schema owner should have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA.

1)How to grant all this prvileges to that user ?

2)How this user will have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA ?

View 5 Replies View Related

SQL & PL/SQL :: What Kind Of Privileges Can't Be Assigned To Roles

May 2, 2010

I read that few privilegs can not be granted to roles like 'UNLIMITED TABLESPACE ' and they need to granted to user directly.

1. Is there any reason behind that few privileges cant be granted to roles or is it just oracle design.
2. what kind of privileges cant be assigned to roles.

View 7 Replies View Related

SQL & PL/SQL :: Getting ORA-01031 / Insufficient Privileges Error

Aug 2, 2012

I've some metadata tables on one schema (abcref@db1) and users updates this table everyday. I need to replicate these tables to other schemas on same database (defdev@db1) and different database (deftest@db2) everyday. So i created materialized views on target schemas and refresh these through a pl/sql procedure. Now i'm getting error while refreshing the materialized view from defdev@db1 and the materialized view refreshes successfully from deftest@db2.

Granted select access on abcref@db1 tables to defdev@db1 and deftest@db2 tables via role.

Getting below error from defdev@db1 schema.

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 361
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 443
ORA-06512: at line 1

Table on abcref@db1 schema
===========================
SQL> create table REF_PROD_CD
2 (
3 prod_cd VARCHAR2(5),
4 division_cd VARCHAR2(1),
5 brand_cd VARCHAR2(2)
6 );

[code]....

Now Using below procedure to refresh materialized views from Target schemas.

SQL> CREATE OR REPLACE PROCEDURE PR_REFRESH_MV IS
2 BEGIN
3 DBMS_MVIEW.REFRESH('REF_PROD_CD','C');
4 END;
5 /
Procedure created

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved