Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Help with Analytic Function

JambalahotJul 20 2012 — edited Jul 22 2012
Here is a sample data from table : 
ID    NAME             Start                  
1	SARA	        01-JAN-2006	
2	SARA	        03-FEB-2006	
3	LAMBDA	        21-MAR-2006	
4	SARA	        13-APR-2006	
5	LAMBDA	        01-JAN-2007	
6	LAMBDA	        01-SEP-2007
I would like to get this : 
Name        Start               Stop
SARA        01-JAN-2006    20-MAR-2006
LAMBDA      21-MAR-2006     12-APR-2006
SARA        13-APR-2006     31-DEC-2006
LAMBDA      01-JAN-2007      <null>
I Have tried using partition and lead function but partition over name is combining all Sara rows and Lambda rows into one group/partition which is not I am trying to get.
Is there any analytic function or other way of doing this to combine the date ranges only when same person is appearing conescutively?
Thanks.
This post has been answered by Boneist on Jul 20 2012
Jump to Answer

Comments

SomeoneElse

> GRANT SELECT ON ALL VIEWS OF USER_1 to USER_2.

No such thing.  Grants must be made one at a time.

However, this is fairly easy to do with a script and dynamic sql.

TSharma-0racle

There is no such thing. You can give "SELECT ANY TABLE" privilege to that user but its not good to give this privilige to anyone unless you know what you are doing. But this privilege should be able to gi ve access to views or future views. You can always test this.

Frank Kulash

Hi,

As the others have said, each GRANT only applies to a single object.  Unfortunately, Oracle dosn't have any way to group objects for prinvileges.  User_1 shopuld just include a GRANT statement (or a call to a grant script) in every scriopt that creates a view.

You could write a database trigger to do this automatically, but it's probably not worth the effort.

If a user is continually creating new views (or tables), that could be a symptom of a poor design.  What exactly is uesr_1 doing?  What arre the views for? 

900163

You can run the below a query where you will  get the lost of view names so that you can run them as a script/

DECLARE

   v_sql    VARCHAR2 (4000);

   v_sql1   VARCHAR2 (4000);

   v_SQL2   VARCHAR2 (4000);

BEGIN

   FOR i IN (SELECT table_name

               FROM ALL_VIEWS

              WHERE owner = 'USER1')

   LOOP

      v_sql :=

            ' GRANT DELETE, INSERT, SELECT, UPDATE, DEBUG  ON USER1. '

         || i.table_name

         || 'USER2';

  

      EXECUTE IMMEDIATE v_sql;    

      v_sql := '';

  

   END LOOP;

END;

Kapil

The alternative solution is create a ROLE, grant all access to the role and then grant this role to the schema. As and when a new view is created, grant the access to the role and it will give access to all those schemas who have access to role.

unknown-7404 
Kapil wrote:




The alternative solution is create a ROLE, grant all access to the role and then grant this role to the schema. As and when a new view is created, grant the access to the role and it will give access to all those schemas who have access to role.

Except that won't allow ANY access within named PL/SQL blocks since roles are disabled within such blocks.

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 19 2012
Added on Jul 20 2012
6 comments
637 views