PL/SQL :: Granting Object Level Privileges

Aug 28, 2012

I want grant object level privileges to some user so that he can view(select) any object that resides under another user.Not select any table option.

View 5 Replies


ADVERTISEMENT

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

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 :: 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

Forms :: User Privileges On Application Level?

Nov 15, 2011

Is there any way of allowing the some menu items to access by the users, not the whole menu. The problem is that in the system where I am working is that the users are on application level not on database level. If the users are on db level then by using roles and grants one can set privileges for each db user. Is there any way to block user from accessing some part of menu or from the forms itself?

View 1 Replies View Related

Server Utilities :: What Privileges Are Required To Perform Only Schema Level Export And Import

Feb 20, 2012

Quote:The EXP_FULL_DATABASE and IMP_FULL_DATABASE, respectively, are needed to perform a full export and import.

what privileges are required to perform only schema level export and import?

View 3 Replies View Related

Application Express :: Object Type As Application Level Variable

Jan 2, 2013

My question is, if I define an object type (create type ... as object ...) is it possible to hold a variable of this type as the session level? For example, if I am creating an order using multiple web pages, it would be much easier (and intuitive) to create and manipulate an order object than to hold the data in generic collections.

If that is not possible, is there some way to make a generic Apex collection appear more application specific? By this I mean some way to map the columns C001, C002, ... to more intuitive names such as CUST_NO and ORDER_DATE. I had considered possibly creating a view of the APEX_COLLECTIONS view which would rename the columns but then I would also have to create a bunch of instead of triggers if I wanted to update it as well.

View 2 Replies View Related

SQL & PL/SQL :: Granting Privilege To A Role

Aug 18, 2010

I have created a role, when i try to grant privilege to that role, it give me insufficient privilege error. After granting privileges to this role, i have to grant this role to other. what type of privileges should i have.

Create Role cb_select;
Grant select on atable to cb_select; (Got error)

View 7 Replies View Related

SQL & PL/SQL :: Granting Permission On All The Tables To Another User?

Jan 19, 2011

I have 3 users in one schema. One user is having all the tables, packages,triggers etc and this is the super user.

I want to access all the tables, packages etc without prefix the username in other two users.

View 15 Replies View Related

SQL & PL/SQL :: Granting Permissions For Create Type / Procedure?

May 18, 2011

I have a question regarding how to grant create type and create procedure roles correctly.Currently if I do this:

create user vackar_temp identified by "123456789";
grant create type to vackar_temp ;

Then as vackar_temp:

create type temp_col as table of number;

I get the following error:

ORA-01031: insufficient privileges

I've also tried:

grant resource to vackar_temp ;

But that doesn't work either If I run this:

select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,

[code]...

I can see that the user does have the create type role:

User, his roles and privileges
------------------------------
VACKAR_TEMP
CREATE TYPE
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE

[code]...

View 8 Replies View Related

Security :: New Procedures Not Working Even After Granting EXECUTE

Sep 2, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have created two new procedures. When I log in as ADMIN, I can execute these procedures.. but when i log in as WC_GUEST or WC_TECH, I cannot.. One of the procedure name is WCL_RPRTD_PARTS_REP_SEARCH, to which, I have given this: grant execute on WCL_RPRTD_PARTS_REP_SEARCH to WC_GUEST,WC_TECH;

Also, I have changed the ROLE of ADMIN, to the same role as these two users and tried.. it works fine.

View 5 Replies View Related

View A Stored Procedure Without Granting Execute Permissions?

Jul 13, 2012

Is there a way to grant rights to view a stored procedure without granting execute permissions?

View 2 Replies View Related

SQL & PL/SQL :: Row Level To Column Level Data Transposition?

May 14, 2010

I have a data like,

1) manual_temp_master

auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010

2)manual_temp_detl

auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010

[code]....

I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:

sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010

View 8 Replies View Related

SQL & PL/SQL :: DBMS_STATS For Table Level Vs Partition Level

Nov 17, 2010

what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.

View 1 Replies View Related

PL/SQL :: Error In Modifying Object Value In Triggers Defined On Object Tables

Sep 26, 2012

While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.

create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........

I executed the below insert statement, and I got the following error.

SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......

SQL> insert into person_obj_tab values (10,'Object','Original');

1 row created.Question:

1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?

View 1 Replies View Related

TimesTen In-Memory :: OracleDataReader - Object Reference Not Set To Instance Of Object

Jan 25, 2013

I have a problem with executing oracleCommand.ExecuteReader() method. Whatever I try it always returns null and it won't create OracleData reader object. I'm using ODAC 1120320_x64 for .net 4.0 and timesten112241.win64. Don't sure what to do. Debugger is showing strange thing in OracleConnection object : ConnectionState = Closed, but output of ttStatus shows connection to TimesTen data store and ExecuteNonQuery() command works just fine with or without (in or out) parameters. But when I try to execute some query with multile output such as select *, I can't get any result.

I also have a strange problem with connection.Open() When I execute Open() i throws AccessViolationException that can be handled with [Handle ProcessCorruptedStateExceptions] attribute, but connection is established after that and my application works fine until I try to instance OracleDataReader object.

Here is the code:
OracleCommand select = null;
OracleDataReader reader = null;

select = new OracleCommand(selectStmt, connection);
select.CommandType = CommandType.Text;
try
{
reader = select.ExecuteReader(); // this line throws NullReferenceException
if (reader.HasRows)
{
[code]....

Just to mention, I tried it with different queries (pl/sql, plane sql, stored procedure) and all of them works fine in SQL Developer, but not in app.

View 10 Replies View Related

Find Dependent Object Details For Any Object

Dec 24, 2012

Is there any query to find the dependent object details for any object. Like if mview is built on a table, then i should be able to find the table name with out checking code of the mview. similar way for view and functions or procedures etc...

View 5 Replies View Related

Backup & Recovery :: Incremental Backup Level 1 Or Level 0 Without Archivelogs?

Oct 22, 2012

Can you take an incremental backup level 1 or level 0 without archivelogs?

syntax would bebackup as compressed backupset cummulative level 1 database.

The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.

View 20 Replies View Related

PL/SQL :: How To Access Type Object Variable Declared Inside Another Type Object

Mar 29, 2013

I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.

CREATE OR REPLACE TYPE typeObj1
AS OBJECT
   (
      SYSTEM_IDENTIFER                    VARCHAR2(50),
      PROCESS_TYPE                          VARCHAR2(50),
      abc                                            typeObj2
     
   )
/

[Code]...

/I have tried to access the type-object in where clause in following way

FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))

AND (ADTINF.ADDTN_RQST_TYP_VAL_DT  IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE(     ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM  IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE  IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )

In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.

I passed the values like this in proc execution.

T_T_A_I_V  :=  typeObj3('asdasd',NULL,NULL),
                       typeObj3('String654',NULL,NULL),
                       typeObj3('abcdef',NULL,NULL));                                    
T_T_A_I_I  :=  typeObj2('CampusCode',T_T_A_I_V),
                          typeObj2('PlanNumber',T_T_A_I_V);

What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct

View 4 Replies View Related

SQL & PL/SQL :: Row Level And Statement Level

Apr 11, 2013

between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.

which will execute first on update DML event ?

View 2 Replies View Related

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

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

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 :: 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







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