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


ADVERTISEMENT

Server Utilities :: Will Schema Export All Roles / Procedures / Packages / Synonyms / Functions And Triggers

Sep 21, 2010

i want to do a schema export from Database A. There are hundreds of users under this schema.I have to import this schema into other database say B. My question's are:

1) Do i need to pre-create only schema user or all the users under it.

2) Will the schema export all the roles,procedures,packages,synonyms,funsctions and triggers?

View 1 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

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

Security :: Find A List Of Roles And Privileges In Database?

Feb 24, 2011

How can I list all the roles and privileges of roles in Oracle? Can I get the details of the activities they perform?

View 12 Replies View Related

Server Administration :: ORA-28031 Maximum Of 148 Roles Enabled Roles Exceeded

Sep 14, 2010

SQL> connect USER/Pass
ERROR:
ORA-28031: maximum of 148 enabled roles exceeded

Getting the error like this..!

SQL> show parameter max_enable
NAME TYPE VALUE
-----------
max_enabled_roles integer 150
SQL> alter system set max_enabled_roles=200 scope=spfile;

[code]....

Still am not able to connect to the db

View 8 Replies View Related

SQL & PL/SQL :: How To Debug The Packages

Nov 2, 2011

Any ways for Debugging in PLSQL.

Ex:In a package or procedure i have thousands lines of Code how can i Debug.

View 1 Replies View Related

PL/SQL :: Display Names And Packages

Jul 17, 2013

NAME         package

  ---------- --------------

Anand         basic           Anand         pascal    Juliana       cobol           Kamala        dbase     Mary          oracle    Mary            c         Partick        c++             Qadir         assembly        Qadir            c               Ramesh         dbase     Remitha          c               Remitha        assembly        Revathi         pascal    Revathi         basic          

View 5 Replies View Related

SQL & PL/SQL :: Roles Given To Objects

Feb 27, 2011

I have granted execute, select, insert, update, delete privilege on objects to roles. Now i want to check status. we have around 2000 objects.

Require out put should be like this.

Object_name Object_Type Role_Granted
----------- ----------- ------------
Table1 Table ABC_ROLE
Table2 Table CDE_ROLE
PROCE1 PROCEDURE PROC_ROLE
Func1 FUNCTION FUN_ROLE

View 3 Replies View Related

RAC & Failsafe :: Oracle Asm Packages For RHEL5.3 32 Bit?

Jul 3, 2011

I am unable to locate the asm packages for rhel 5 :2.6.8-128 kernel of 32 bit.

Only 64 bit rpm are availbale on OTN.Can ayone suggeet me the URL for 32 bit>

View 1 Replies View Related

SQL & PL/SQL :: Packages Component Full Details

May 19, 2011

Get following output using user_source(or other) view?

Package_nameProcedure_nameline_startline_end
ABCXYZ1022

In procedure_name column it could be any procedure, function name or any object name exist in package body.

View 6 Replies View Related

PL/SQL :: How To Know A Trigger Gets Called By Which All Packages / Procedures

Sep 11, 2012

Wanted to know whether it is possible to know a trigger is getting fired due to which all packages/procedures. If it has got fired for a particular transaction say multiple times, then wanted to get details of the packages/procedures which caused an event so that trigger got fired?

View 2 Replies View Related

PL/SQL :: ROLES And Table Access

May 23, 2013

I am using Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production.I have a table D_GEOGRAPHY_25729 in RDS schema and I have created a role ACC_D_TABLES logging as ADMIN.

Now I have given GRANT SELECT access for table D_GEOGRAPHY_25729 to role ACC_D_TABLES.

There is another schema TRANS in the same database which has the role ACC_D_TABLES. But after logging in this schema, I am not able to query directly from table D_GEOGRAPHY_25729. I need to use like below to access the table.

select * from RDS.D_GEOGRAPHY_25729 Having the correct role ACC_D_TABLES to TRANS schema, why should I use RDS.D_GEOGRAPHY_25729 ? Is it not possible without prefixing the RDS schema name?

It is possible when I create a public synonym for D_GEOGRAPHY_25729 in RDS schema, but if i do so everyone can access this table.

View 7 Replies View Related

ORA-04068 - Existing State Of Packages Has Been Discarded

Feb 4, 2011

We are experiencing an issue with ORA-04068: existing state of packages has been discarded. We're running Oracle 10.2.0.4 on Solaris 10.

I'll provide the timeline of what occurred:

1.) Last night during a maintenance window I recompiled a package and package body. I ran "create or replace" statements from a script via sqlplus to recompile both package and package body. Because I am required to do all DDL under my own login (and not the schema owner login) the scripts require that the schema name be included in the "create or replace" statements. Otherwise an object will be incorrectly created in my schema. This is what happened with the package body. I discovered this when the package body would not compile.

2.) I ran a "drop package body" statement to drop the package body from MY schema.

3.) I added the proper schema name to the script and recompiled both the spec and body under the proper schema.

4.) I ran a script to check for any invalid objects under the application user schema. There were none.

5.) During the night a batch process ran which executed the package. There were several failures with the error ORA-04068: existing state of packages has been discarded.

6.) During the early AM today the error stopped. Not knowing this at the time and believing that the issue was still occurring I began searching the internet for information regarding this issue. One site I found said this could be due to DDL being done on the object (which was the case for us) and there could be an entry in the shared pool marked as invalid which our application's session may be accessing. It recommended running "alter package recompile" for the spec and body. I did this but it seems to have triggered the event again.

I also ran utlrp.sql.

I found some information suggesting the use of DBMS_SESSION.MODIFY_PACKAGE_STATE and/or RESET_PACKAGE but I'm not seeing mention of the risks/implications.

I can verify that the object is no longer in my schema and that the proper object owner has no invalid objects. I am not sure what to do to resolve the issue or keep it from occurring again.

View 4 Replies View Related

ORA-04068 / Existing State Of Packages Has Been Discarded

Feb 6, 2007

I am trying to use dbms_jobs in Oracle 8i database.

This is the syntax I am using:

begin
dbms_job.submit(:jobno, 'SYSTEM.SHRINK_ROLLBACK_SEGS', '07-FEB-2007', NULL);
commit;
end;

i have defined the variable jobno as number!

And i am getting the error :

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_JOB" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

I then go and look at the Sys.dbms_job package and see its not compiling

I have this error:

(1): PLS-00201: identifier 'ANY_INSTANCE' must be declared
(2): PL/SQL: Item ignored
(3): PLS-00801: internal error [21076]
(4): PL/SQL: Item ignored

View 2 Replies View Related

JDeveloper, Java & XML :: Parsing In PL/SQL Without Any DBMS_XML Packages?

Jun 15, 2012

i dont have access to any Oracle XML related pacakges, however I have to pull the data from the following xml.Eg: The alternativeIdentifier.Name should be a column and the value should be the data for the column. Same applies to other tags under characteristics tag.

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"

[code]....

View 29 Replies View Related

PL/SQL :: Difference Using Cursor In Specifications And Body In Packages

Jun 24, 2013

what is the difference using cursor in specifications & body in packages?

View 1 Replies View Related

Forms :: Dynamic Value (get Roles) In Combo Box

Feb 12, 2010

i want to get user (dba roles in combo box runt time).i have write down this code in when new form instance.

declare
rg_id recordgroup := find_group('RG') ;
ln_error number ;
begin
if not id_null(rg_id) then
ln_error := populate_group(rg_id) ;
if ln_error = 0 then
populate_list('BLOCK3.T' , rg_id) ;
else
message('Error in Populating Group') ;
end if ;
end if ;
end ;

attached file.when i run it gives the follwoing error. frm-41334-invalid record group.

record group query is: SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='MTSYS'

View 6 Replies View Related

SQL & PL/SQL :: Grant All Roles Of One User To Another Through Script

Nov 24, 2010

I am trying to create a script where a user can be granted all the roles that another user has. I created a script:

create user yyyyy identified by zzzz default tablespace ahspace temporary tablespace temp_data;

grant connect to yyyyy;

declare
cursor grantpriv is
select granted_role from dba_role_privs where grantee='xxxxx'; -- existing user
cursor_name integer;
statement varchar2(200);
Begin
cursor_name :=dbms_sql.open_cursor;

[Code]....

View 3 Replies View Related

Security :: Grant Privs To ROLES?

Feb 18, 2013

I am working on the roles and privs. I want to provide the privs on any object to any user through the roles. not a direct grant.I am having 150 users and 50 roles, I want to do this using the script is few strokes. for this I have tried to create a script but I am not able to make that.

For example There is 2 user scott and test

A. test is having select privs on scott.emp.
B. Test is having insert,delete,update privs on scott.emp;
c. DB is having 2 roles
scott_edit - insert,delete,update privs on tables
scott_read - select privs on tables

I want to revoke separate privs from test and want to given only scott_edit and scott_read (which(roles) is having all privs)

View 2 Replies View Related

Server Administration :: SQL For Generating Roles

Dec 18, 2012

I am looking for the below one.SQL for generating roles needs to be spooled by automated script.

View 4 Replies View Related

SQL & PL/SQL :: Keep Logging Track Of Each Task Performed In Procedure Packages

Nov 26, 2010

we have a database application which is done frequently.in these we load data throught Sql loader, we create an DB instances, we do several DML operation on the database.

now for such task in an application we need to keep an logging track of each task performed in PL/SQl procedure packages.

View 4 Replies View Related

Cloud Service :: Adding Packages To Cart In SQL Developer

Jan 14, 2013

I am really wondering about data loading to the cloud, I've just read the documentation, but regarding the packages, I have more than 100 package and I have to "add to cart" one by one the problem is that the package body is not added by default, so you have to expand the packages one by one then select the package body right click and add to cart. This task is very tedious, is there any other options.”

View 8 Replies View Related

RAC/ASM Clusterware Installation :: 11g Release 2 Install Missing Packages?

Aug 8, 2012

I'm trying to install 11g Release 2 on a CentOS (4.9) box, but the pre-req check shows that I'm missing the following packages (I had 9 to begin):

gcc-3.4.6 (X86_64)
gcc-c++-3.4.6(X86_64)
libstdc++-devel-3.4.6

Tried to use the yum command "yum install gcc" but get the following error.
Error: cannot find a valid baseurl for repo:update

I think this is due to the "CentOS-Base.repo" file, which I've tried to update by changing the mirror URL's but no success.Then tried to find the rpms online ([URL]...

INFO: Start output from spawned process:
INFO: ----------------------------------
INFO:
INFO: rm -f ntcontab.*

[code]....

View 2 Replies View Related

Generating Script For Roles For Schema Import?

May 19, 2011

I want to import schema1 from sourcedb to targetdb. For this I need to generate create sripts and grant scripts for roles related to schema. For example schema1 has role1; rol1 has rol2; rol2 has rol3 and some system privileges and object privileges. How can I generate this script from sourcedb?

View 1 Replies View Related

Server Administration :: Assign User Roles To Uses?

Aug 19, 2011

Is there any default way to assign user roles to uses ?

Suppose I want assign to user different roles to different users on views, tables etc.

I wan to implement Access control list & Access request objects (ACL/ARO).

View 7 Replies View Related

Client Tools :: Unable To Drop Roles

Jan 10, 2012

When I try to drop the roles in dbartisan tool, I get an error saying "Drop DDL for **_***********_ROLE has not been generated because it is a system object", but if i go to sql prompt and say "drop role rolename", it is working. How do I remove the role in dbartisan itself?

View 9 Replies View Related

Server Administration :: How To Spot Which Oracle Packages Are Installed / Licensed

Feb 22, 2011

I need to figure out which Oracle Database packages are installed on a certain server.

First problem: no Enterprise Manager available. So I need some command line tools or SQL statements.

I've already found some with them I was able to spot RAC, partitioning and so on. But I'm still missing a option/command to spot

- diagnistic pack
- tuning pack
- web logic
- web server

View 3 Replies View Related

Distinguish Between Oracle Predefined Users / Roles - Customized Ones?

Apr 18, 2012

I am looking for a query which can select all existing Oracle predefined users and roles from db.I need it because one of my other scripts needs to know a given user name or role is a customised one or a system one.It looks like sys.user$ does not have any column that can be used to distinguish predefined users/roles.

I could find all the list of Oracle predefined users and roles by googling and hardcoded them in a query. I can also collect all the customised users and roles as well. However, I prefer a query which can automatically reports that and minimise the maintenance cost as new names and roles, either Oracle predefined or customised, may come out at anytime.

View 6 Replies View Related

SQL & PL/SQL :: Security - Grant Roles To Session On Time Of Connection?

Jan 18, 2012

When I use Forms_ddl('set role My_Role IDENTIFIED BY PWD'); in form's "When-New-From-Instance" to grant a role to a user.

What should I do for the same for a report?

Is there any way to grant roles to a session on the time of connection?

View 6 Replies View Related







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