No question at this time
DBA Top 10
1 M. Cadot 6700
2 B. Vroman 3900
3 A. Kavsek 2700
4 J. Péran 1700
5 T. Boles 600
5 D. Johnson 600
7 T. P 500
8 P. Wisse 400
9 S. Spb 300
10 V. Gupta 200
10 M. Jain 200
10 K. Pingeli 200
10 R. Pastre 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48649
Total active users1380
Act. users last 24h1
Act. users last hour1
Registered user hits last week81
Registered user hits last month279
Go up

privliges
Next thread: Query restriction by ROWNUM
Prev thread: Generation of Store Procedure Usage Reports

Message Score Author Date
Hello All, Just i need a suggestion from your e...... rishaan ahamed Nov 05, 2013, 11:02
The answer is no if you explicitly named the obj...... Michel Cadot Nov 05, 2013, 11:10
Hello Mike, Thanks for your inputs, but i am no...... rishaan ahamed Nov 05, 2013, 11:18
Can you explain what you mean by "I am giving a ...... Michel Cadot Nov 05, 2013, 11:24
Hello Mike, It is like "grant select on A.table...... rishaan ahamed Nov 05, 2013, 11:50
as you can see you cannot name a non-existent ta...... Michel Cadot Nov 05, 2013, 12:03
Hi, the only RDBMS that I know that supports su...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek Nov 05, 2013, 12:23
Hello Mike and Ales, thanks for your inputs. ...... rishaan ahamed Nov 05, 2013, 12:34
Hello Rishan, have a look at "<i>Automatic near...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Nov 05, 2013, 12:43
Hello Rishan, about "<i>shall we give ADMIN acc...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Nov 05, 2013, 12:52
Hello Bruno, this is really good, but to implem...... rishaan ahamed Nov 05, 2013, 12:58
If you want my opinion, on production, you REALLY ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts jacques roelens Nov 05, 2013, 14:00

Follow up by mail Click here


Subject: privliges
Author: rishaan ahamed, India
Date: Nov 05, 2013, 11:02, 2954 days ago
Os info: AIX
Oracle info: 11.2.0.2
Message: Hello All,

Just i need a suggestion from your end.

I am having a user

A and B.

I am giving a SELECT permission to all the objects of A to B.

Suppose if the user A creates any objects or VIEWS after the permission i gave to B.

My question is:

B should access all the newly created objects or VIEWS by A after given the permission in future.

Is there anyother way to do it, Thanks in Advance.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: Michel Cadot, France
Date: Nov 05, 2013, 11:10, 2954 days ago
Message:
The answer is no if you explicitly named the objects on which you granted the privileges.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: rishaan ahamed, India
Date: Nov 05, 2013, 11:18, 2954 days ago
Message: Hello Mike,

Thanks for your inputs, but i am not able to get your inputs.

Can you demonstrate me, whether is it possible or not
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: Michel Cadot, France
Date: Nov 05, 2013, 11:24, 2954 days ago
Message:
Can you explain what you mean by "I am giving a SELECT permission to all the objects of A to B. "

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: rishaan ahamed, India
Date: Nov 05, 2013, 11:50, 2954 days ago
Message: Hello Mike,

It is like "grant select on A.table or View name to B;
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: Michel Cadot, France
Date: Nov 05, 2013, 12:03, 2954 days ago
Message:
as you can see you cannot name a non-existent table in this statement. So you cannot name a future table and your B will not have the privileges on the future tables.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: Ales Kavsek, Slovenia
Date: Nov 05, 2013, 12:23, 2954 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

the only RDBMS that I know that supports such "feature" is PostgreSQL. In PG you can change schema default privileges that'll be granted on newly added tables:


alter default privileges in schema scott grant select on tables to foe;


ALTER DEFAULT PRIVILEGES is not defined by ANS SQL standard, so this is PG "proprietary" implementation.

Oracle does not support that and this is actually a GOOD thing. Imho, this feature is something that I would love to see being removed from future PG versions, it's just seats there asking for trouble...I mentioned this just in case before someone propose a DDL trigger or some other trick as a workaround for Oracle ;-).

Regards,
Ales


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: rishaan ahamed, India
Date: Nov 05, 2013, 12:34, 2954 days ago
Message: Hello Mike and Ales,

thanks for your inputs.

shall we give ADMIN access to A user, so that whatever objects he creates in future, can give access to other users know.

Is there any restriction in it or if i want to set A user to give only SELECT permission to other users.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: Bruno Vroman, Belgium
Date: Nov 05, 2013, 12:43, 2954 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Rishan,

have a look at "Automatic near-real time maintenance of privileges",
  http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=5888
I think that this is what you need.

HTH,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: Bruno Vroman, Belgium
Date: Nov 05, 2013, 12:52, 2954 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Rishan,

about "shall we give ADMIN access to A user, so that whatever objects he creates in future, can give access to other users..."
Things are maybe easier than what I thought.

If the user A creates new objects and views in a controlled way, then it is possible to grant the privileges immediately (no need for the solution via trigger that I mentioned in my other reply)
The owner of an object can always grant privileges on his objects. Just make sure that when account A does
  CREATE TABLE mytable ... or CREATE [OR REPLACE] VIEW myview ...
then he does also
  GRANT SELECT ON mytable TO <<users or roles, but preferably roles>>

Remark: if there is only ONE user "B" needing the access you might think that the work is the same between
  GRANT SELECT ON mytable TO userB;
and
  GRANT SELECT ON mytable TO myreader_role;
assuming that userB has been granted the role, but you will appreciate the role if you have more users, or if the list of users can vary over time (example: revoke myreader_role from userB and grant it to userC and userD, and all the privileges on the [hundreds of] objects of A are OK)

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: rishaan ahamed, India
Date: Nov 05, 2013, 12:58, 2954 days ago
Message: Hello Bruno,

this is really good, but to implement this in production, without any proper CHANGE record, will leads us to panic.

Is possible to give ADMIN access to A user, by getting the RISK Exception letter from the customer to give permission by his own to B user.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: privliges
Author: jacques roelens, France
Date: Nov 05, 2013, 14:00, 2954 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: If you want my opinion, on production, you REALLY should grant minimum needed privileges, meaning here granting privileges on minimum set of views and tables, once any of those are created, checked, double checked and validated.
If you try to bypass that, believe me, sooner or later, you'll come into trouble :D

Regard
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here