GRANT update on VIEW!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: GRANT update on VIEW!

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    Thumbs up GRANT update on VIEW!

    Code:
    D
    Hello,

    If someone can help me out in this would be greatly appreciated.

    I have three tables with their primary keys being the foreign keys in other tables.

    Code:
    create table employee_list (
    EMP_LIST_ID    	    NUMBER(3) NOT NULL,
    USERID                    VARCHAR2(25),
    EXTENSION              VARCHAR2(5),
    DEPT                       NUMBER(2),
    CONSTRAINT PK_ID_EMP PRIMARY KEY (EMP_LIST_ID),
    CONSTRAINT FK_DEPT_DEPARTS FOREIGN KEY (DEPT)
    	REFERENCES DEPARTMENTS(ID));
    
    CREATE TABLE DEPARTMENTS (
    ID 		NUMBER(14) NOT NULL,
    DEPT_NAME	VARCHAR2(50),
    CONSTRAINT 	PK_ID_DEPART PRIMARY KEY (ID));
    
    CREATE TABLE DROP_CALLS_LOG
    (
    EMP_ID		NUMBER(3) NOT NULL,
    ID		NUMBER(14) NOT NULL,
    PHONE 		VARCHAR2(12) NOT NULL,
    CALL_DATE           DATE NOT NULL,
    TIME 		DATE NOT NULL,	
    DESC_TX		VARCHAR2(500),
    CONSTRAINT PK_ID_DCL PRIMARY KEY (EMP_ID, ID),
    CONSTRAINT FK_EMP_DC FOREIGN KEY (EMP_ID)
    	REFERENCES EMPLOYEE_LIST(EMPLOYEE_LIST_ID));
    I am trying to create an updatable VIEW based on EMPLOYEE_LIST AND DROP_CALLS_LOG TABLES.

    THIS IS MY VIEW:

    create or replace view drop_calls_info as
    select e.employee_list_id, d.phone, e.userid, d.call_date, d.time, d.in_out_bound, d.ld_line, d.checked_y_n, d.desc_tx
    from drop_calls d, employee_list e
    where e.employee_list_id = d.emp_id

    I think I have followed all the rules of updatable views. My (not key preserved table - Employee_list table) and (key preserved Drop_calls_log)
    has everything correct in order to create my view.

    I'm a little confused about grant select and update privilidges to my view.
    If anyone understand what I have explained, please shed some light, thanks.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are you able to grant directly on the view? If not, grant on the underlying tables I guess.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2004
    Posts
    125
    I tried to grant on view but it wouldn't let me grant from the same user. I did the grant update and select but, how do i do the grant from the same user as myself. Thanks.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    how do i do the grant from the same user as myself
    What do you mean by this?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width