SQL & PL/SQL :: How To Restrict User At Data Level

Feb 17, 2010

Is it possible that we restrict user at data level? For Example 'A' user can only query employeess of deptno 10 only. He can not query employees of others dept.

View 6 Replies


ADVERTISEMENT

Forms :: Restrict User To Enter Serial Numbers Of Product In 3 Level Block

Apr 24, 2012

I have a sale invoice form with 3 data blocks.

Data block 1- master_blk : For date/customer of sale invoice
Data block 2- detail_blk1 (detail of the master block - For products and qty)
Data block 3- detail_blk2 (detail of DETAIL_BLK1 For entering serial numbers of products)

My requirement is that whatever quantity user enter in data block 2 against each product he must enter equal number of serial numbers of that product in data block 3.

For this I have created on item (cnt_iteml : to count product's serial numbers in block3 ) in data block 2, and on summary item (t_serial_no ) in block3.

Whenever user changes in quantity, cnt_iteml: item is populated with t_serial_no in block3 of that product by following trigger on quantity column.

POST-Change Trigger ( quantity column )

:stock_transactions.cnt_itl:=:serial_numbers.t_serial_no;

Following trigger is written on block level at data block-3 to populate cnt_iteml with t_serial_no.
PRE-RECORD

IF GET_BLOCK_PROPERTY('SERIAL_NUMBERS',STATUS) IN ('CHANGED') THEN
:stock_transactions.cnt_itl:=:serial_numbers.t_serial_no;
END IF;

Above triggers are fulfilling my requirement except following condition.

If user after entering serial numbers in block 3 and without saving goes back to block2 and try to navigate to another record he gets a message asking him to save changes by forms. At this time if user presses no then cnt_itl item is not been populated with t_serial_no item's value.

What I want in above condition is that if user was inserting new record cnt_it item should be populated with 0, so that he shouldn't be able to save this record. And If he was updating then cnt_itl item should be populated with actual no of records in database against that product.

View 2 Replies View Related

SQL & PL/SQL :: How To Restrict Data Dictionaries To A User

Jun 21, 2011

I have a user ( for example Scott) and he doesn't belongs to DBA group i want to restrict accessing data dictionaries like dba_% to this Scott user. how to restrict Data dictionaries to this user.

View 9 Replies View Related

SQL & PL/SQL :: How To Restrict User (Schema) From Deleting Data From Table

Nov 2, 2012

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 10 Replies View Related

PL/SQL :: How To Restrict User (Schema) From Deleting Data From A Table

Nov 2, 2012

I have scenario here.

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 6 Replies View Related

Product User Profile - Restrict User From Running Queries On DB From Third Party Tools

Apr 25, 2011

There is a requirement in my database that I want to restrict the user from directly running queries on database from third party tools such as pl/sql developer and toad.

There is a utility in SQL product_user_profile through which this can be done but it is only restricted if you run the query through sql plus. If I want to restrict and (give suppose select,insert) to a user for directly running queries through PL/SQL.

View 1 Replies View Related

Restrict User In PL/SQL

Apr 30, 2011

Suppose there is customers table and the user (Scott) is having select,update,insert privelege on the customers table.

a.)From the form i.e GUI he is having all the access,we want that when he logs on with sql plus directly then he would only be able to run select query only.

b.)When we are using third party tools such as pl/sql developer tools and toad then we are not able to restrict the user by running only the select queries.The utility which was used previously was only used for SQL Plus.

Resolution:

a.)I am able to restrict the user when he logs on with sqlplus and he is able to run anly select queries.
b.)How we can restrict the user so that in pl/sql he is able to issue only the select query.

Trigger the user when logs on session id is captured and user can be restricted in logging in to the plsql developer and toad applications

View 3 Replies View Related

Forms :: Restrict User To Access Records

Mar 26, 2012

I am using Forms 6i,Oracle Apps... I have database block.... generally we can retrieve the all the records by press F11 and Ctrl F11.... but my requirement is i have to restrict the user that they can access the records based on the condition ...

For example....let we take emp table....I have to restrict the user that they can query dept no 10 employees only.

View 11 Replies View Related

Forms :: How To Restrict User On Key Next Item Trigger

Sep 16, 2011

SQL Plus version Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.1.0 Production
Forms Version : 6i
Reports Version: 6i
O/S : Microsoft Windows Xp professional Version 2002 Service Pack 3

With reference to the above version details here is the requirement. How do I restrict user on key next item trigger i.e I want the cursor to move to next field only when user presses "ENTER" button on the key board any other key like mouse should not allow user to move to the next field.

View 23 Replies View Related

Restrict Concurrent User And / Or Session From A Client?

Nov 5, 2012

We have an application with database Oracle 10g.

I want to add a new validation to restrict concurrent user and/or session from a client. (we have almost 60 client firms using the software to enter daily trasnactions). All users from all clients are connecting to the database using a common functional ID.

What I did was:
1) Add a column 'user_logged_in' in the master table for client and update it as Y when user from that client logged on to the system,
2) Insert the application logon details (we can figure out the client details from this) into a global temp table,
3) Create a logoff trigger to update the 'user_logged_in'flag in client master table by using values from global temp table when session logged off and
4) Restrict the users from same client if the flag is 'Y'

But the problem in this case is logoff trigger will not be executed in case if the session got killed or terminated abnormally.

View 3 Replies View Related

SQL & PL/SQL :: Function Code To Restrict User From Entering More Than 30 Characters

May 24, 2011

Need a code which prevents the User from entering more than 30 characters in a field. Although the variable can accept more than 30 characters. My requirement is to validate the field by restricting up to 30 characters.

View 6 Replies View Related

Restrict User To Login On Primary Database Using Discoverer

Dec 14, 2012

We are using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 and Oracle Discoverer 9i desktop & Administrator,We have configured a physical standby database. We have ask users to connect to standby thru discoverer thru a different tns entry, but user can also connect on primary db.

We want to restrict the specified users to connect on primary database thru discoverer.

View 5 Replies View Related

Forms :: Tabular Form - Restrict User To Enter Only 10 Rows

Dec 18, 2012

i have master and detail form. In detail(tabular form) i want to restrict user to enter only 10 rows.if he tries to go for 11th row he shud get a message (Only 10 records can be created at a time).

View 3 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 :: User Restriction On Database Level?

Dec 13, 2011

For the list of userid's, how to find the list of OS/Restricted Shell ID's at the database level ?

View 7 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 :: Schema Level Import By Dropping Existing User That Contain Tables

Jan 20, 2011

Our Testing DB is running in No archive log mode. I did a schema level import by dropping the existing user that contain tables, recreate the user and finished the import. Now they want the old tables back.Is there is any way to recover the old tables?

View 5 Replies View Related

Any Data Compression Wizards That Automatically Suggest Level At Which Data Should Be Compressed

Jun 29, 2011

whether Oracle has any capability of automatically checking which lossless compression algorithm it should apply by analyzing a data stream on data load? Does Oracle have any compression advisors/wizards that would make recommendations as to type and level of compression?

View 3 Replies View Related

Export/Import/SQL Loader :: Schema Level Export For User Sample 1

Jul 11, 2012

I have schema level export for user SAMPLE1(Default tablespace USERS) on oracle 9.2.0.1 production database. I want to import into another 9i database on another server, so do i nneed to Create SAMPLE1 user and USERS tablespace in new database again.

View 5 Replies View Related

Forms :: User Privileges - Assign Permissions To One User To Add / Delete / Edit Data?

May 28, 2011

how can i assign permissions to one user to add,delete,edit data and other user should be able to perform all functions or selected functions

View 12 Replies View Related

PL/SQL :: Create Trigger To Insert Data From One User To Another User In Same Database?

Apr 16, 2013

I Created One Trigger as Follows

CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT
ON table1
FOR EACH ROW

[code]......

Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist

View 3 Replies View Related

SQL & PL/SQL :: Generating (time Taken) Data At Row Level?

May 1, 2013

I have a requirement where i have data spread in rows which i have to pivot kind off operation to find the time taken using a Log table.

below is the existing data is present.

col0 col1 col2 col3 col4
30003695/1/2013 8:41Batch Startedproc_unalloc1345
30003705/1/2013 8:41blah proc_unalloc1345
30003715/1/2013 8:46blah proc_unalloc1345
30003725/1/2013 8:46blah proc_unalloc1345
30003735/1/2013 8:56blah proc_unalloc1345
30003745/1/2013 8:57Unalloc Endedproc_unalloc1345

[code].....

Below is what I am trying to find from the above data.

col1Unalloc Alloc Metric
13450.16 1.07 1.3

the result is Hours.Minutes.

I am trying to minus the two dates and find the time taken for a particular section( eg, unalloc ).

I tried using three union queries .

Select block,timestamp From (
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Batch Started' And col3 = 'proc_unalloc' And
Parms Like '1345'
Union
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Unalloc Ended' And col3 = 'proc_unalloc' And

[code].....

I will minus the start and end time for unalloc to get time taken for un alloc. Then i will minus end time of unalloc and end time for alloc to get time taken for alloc... like wise for metric.

View 6 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 :: User Data Selection Between 2 Databases With Same User Name?

Mar 25, 2013

I have two databases (say DATABASE 1 with user USER1 with table A and DATABASE2 with user USER1 with table A). I want to select the table A in USER1 DATABASE2 from DATABASE1 USER1.

View 5 Replies View Related

Possible To Move Data At Storage Level Without Using Convert Command?

Apr 12, 2011

We are migrating database from HP to AIX. Data is all on SAN.I can do it through RMAN convert. What I want to know is if it is possible to move data at storage level without using Convert command?

View 2 Replies View Related

Security :: Data Encryption From Transaction To Backup Level?

Apr 26, 2013

Our client is having requirements that data should be encrypted through-out the process (from transaction level to backup level).

Requirements are as below -

1) Data should be encrypted and can be access only through application (through front end).
2) Even if pl/sql, sql installed, developer user cannot able to fetch data from database.
3) Even DBA, should not have access to fetch the data from the database.
4) Only assigned DBA, should fetch details from database (provided he/she should have details of database as well as application's user / password).
5) It should be restricted by ip and user details (such as ip of application server with user/password of user).
6) Changes should be purely done from database level only.

View 5 Replies View Related

Data Guard :: Find Out Number Of Standby Databases Configured For Primary Database From OS Level?

Nov 14, 2013

How can find the number of standby databases configured for primary database from os level

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

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

How To Restrict Clients Access

Jan 9, 2013

I am having one requirement that i have to restrict the access of oracle database from clients.

suppose i am having 10 users and they have all information related login like username, password, database servers details etc but then also they should not get login into my database, i have to restrict some of them like i have to provide access to 5 users only out of that 10 users.

View 3 Replies View Related







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