Getting Role and Permissions from DB

Getting Role and Permissions from DB

Post by Manoj Goe » Fri, 01 Jan 1999 04:00:00



Hi Guys,

  We have to many times create/drop/modify the tables in our db,as still db
changes
  are going thru. But whenever this happens the permissions are lost on
certain roles.

 Also soon we may have to move the db to a new server for testing for
QA's.But this
 time I wanted that we don't have to go thru the same pain and loose two
days to again figure out
 what objects lost the permissions in certain roles.

Is there any commands that we can use to restore the same privilges whenever
we move to
 another db or do chnages in same db.??

Please let me know

Thanks for any help

Manoj

 
 
 

Getting Role and Permissions from DB

Post by Thomas Ky » Fri, 01 Jan 1999 04:00:00



(if that email address didn't require changing)


>Hi Guys,

>  We have to many times create/drop/modify the tables in our db,as still db
>changes
>  are going thru. But whenever this happens the permissions are lost on
>certain roles.

> Also soon we may have to move the db to a new server for testing for
>QA's.But this
> time I wanted that we don't have to go thru the same pain and loose two
>days to again figure out
> what objects lost the permissions in certain roles.

sounds like you might need a change management tool, you might consider looking
into some of those.  

Quote:>Is there any commands that we can use to restore the same privilges whenever
>we move to
> another db or do chnages in same db.??

well, the following will help somewhat:

- use "create or replace" as opposed to drop/create whenever possible

- use "views" instead of tables as the thing you grant on.  Don't grant on
tables, don't access table.  Views can be 'created or replaced', tables cannot.
You might also find you have to drop columns/rebuild tables much less frequently
(you can save them up) as you can simply default the column value in the real
table, remove the column from the view and the apps will be none the wiser.
Same with adding columns and so on (these can be altered into the table and
added to the right views).

- use exp .. rows=no to get your schema and grants moved from one database to
another.

Quote:>Please let me know

>Thanks for any help

>Manoj

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

Getting Role and Permissions from DB

Post by sui.. » Mon, 04 Jan 1999 04:00:00


Hi Thomas:

Please explain further. I have the following questions.

: - use "create or replace" as opposed to drop/create whenever possible

: - use "views" instead of tables as the thing you grant on.  Don't grant on
: tables, don't access table.  Views can be 'created or replaced', tables cannot.
: You might also find you have to drop columns/rebuild tables much less frequently
: (you can save them up) as you can simply default the column value in the real
: table, remove the column from the view and the apps will be none the wiser.
: Same with adding columns and so on (these can be altered into the table and
: added to the right views).

Some people don't like to use view because they said that it could be very
slow. In my previous job, the project manager didn't allow people to
create view. But some people are in favour of using views. They created
views almost on everything.

So, I'm very confused. Would you mind to guide us how to use view
effectively, please?

Best regards,

 
 
 

Getting Role and Permissions from DB

Post by Thomas Ky » Mon, 04 Jan 1999 04:00:00



(if that email address didn't require changing)

>Hi Thomas:

>Please explain further. I have the following questions.


>: - use "create or replace" as opposed to drop/create whenever possible

>: - use "views" instead of tables as the thing you grant on.  Don't grant on
>: tables, don't access table.  Views can be 'created or replaced', tables cannot.
>: You might also find you have to drop columns/rebuild tables much less frequently
>: (you can save them up) as you can simply default the column value in the real
>: table, remove the column from the view and the apps will be none the wiser.
>: Same with adding columns and so on (these can be altered into the table and
>: added to the right views).

>Some people don't like to use view because they said that it could be very
>slow. In my previous job, the project manager didn't allow people to
>create view. But some people are in favour of using views. They created
>views almost on everything.

A simple view, as I describe above "select c1, c2, c3 from T" will not affect
performance (except perhaps during the initial parse phase of the query but that
will be neglible).

the 'problem' people sometimes see with views is that they create join or
aggregate views.  Then, they create join or aggregate views on top of these and
so on...  Then, people take 2 or 3 of these views at runtime and query them
together.  the optimizer has a hard time merging these views together and
pushing predicates down.  A 'hand written' query with no views in this case will
probably be more performant.

It can be hard to tune systems with lots of complex underlying views and queries
against these views.  I'm not suggesting that however, far from it.

Quote:>So, I'm very confused. Would you mind to guide us how to use view
>effectively, please?

In your case, your main problem is the grants.  Using simple:

SQL> create or replace view my_view as select * from t;

with no joins, no aggregates, etc should not have any impact on the query plans
(i've never seen any with simple views).

Quote:>Best regards,

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

Getting Role and Permissions from DB

Post by sui.. » Tue, 05 Jan 1999 04:00:00


Hi Thomas:

Thank you very much for your detail explanation. I still have some
questions.

: A simple view, as I describe above "select c1, c2, c3 from T" will not affect
: performance (except perhaps during the initial parse phase of the query but that
: will be neglible).

: the 'problem' people sometimes see with views is that they create join or
: aggregate views.  Then, they create join or aggregate views on top of these and
: so on...  Then, people take 2 or 3 of these views at runtime and query them
: together.  the optimizer has a hard time merging these views together and
: pushing predicates down.  A 'hand written' query with no views in this case will
: probably be more performant.

: It can be hard to tune systems with lots of complex underlying views and queries
: against these views.  I'm not suggesting that however, far from it.

If I have a query with a lot of joints and it may be called by a group of
people e.g. developers for billing related process, will it be benefit to
put it into a view?

: In your case, your main problem is the grants.  Using simple:

: SQL> create or replace view my_view as select * from t;

: with no joins, no aggregates, etc should not have any impact on the query plans
: (i've never seen any with simple views).

Yes, this one very clear. I got it.

Once again, tahnk you veyr much for your time.

Best regards,

 
 
 

Getting Role and Permissions from DB

Post by Thomas Ky » Tue, 05 Jan 1999 04:00:00



(if that email address didn't require changing)

>Hi Thomas:

>Thank you very much for your detail explanation. I still have some
>questions.


>: A simple view, as I describe above "select c1, c2, c3 from T" will not affect
>: performance (except perhaps during the initial parse phase of the query but that
>: will be neglible).

>: the 'problem' people sometimes see with views is that they create join or
>: aggregate views.  Then, they create join or aggregate views on top of these and
>: so on...  Then, people take 2 or 3 of these views at runtime and query them
>: together.  the optimizer has a hard time merging these views together and
>: pushing predicates down.  A 'hand written' query with no views in this case will
>: probably be more performant.

>: It can be hard to tune systems with lots of complex underlying views and queries
>: against these views.  I'm not suggesting that however, far from it.

>If I have a query with a lot of joints and it may be called by a group of
>people e.g. developers for billing related process, will it be benefit to
>put it into a view?

Lets think about it this way -- you use the views DBA_*, USER_*, ALL_* all of
the time to query the data dictionary.  Just for grins, why do you:

SQL> set long 5000
SQL> select text from all_views where view_name = 'ALL_OBJECTS';

that view is pretty large and does lots of stuff (all_objects not only presents
you with data but enforces a security policy -- you can only see objects that
you either OWN or you have privelege to see via a grant or a role)....

Was it of benefit to put that in a view?  YES, in this case it was.  It serves
many purposes.  First and firemost, it answers a frequently asked question (what
objects are they and what are their types).  It does this in a way that doesn't
make everyone learn the entire Oracle schema and join tables themselves (tedious
and error prone).  Secondly, it 'optimizes' the display of data by turning
internal codes into meaningful names.  We use decode to do that. So, it can
reformat data for display as well as make it easy to get the data to display.
Lastly, it enforces a pretty strict security policy -- you see what you are
allowed to see and nothing more.

So, the entire data dictionary is nothing more then a big set of views (and some
extremely complex ones at that).  The nice thing is that as you upgrade the
oracle versions, you get the same set of views over time -- even though the base
tables might change (their column names might change, their size and shape and
values change over time).

Long story short:  Views are a tool just like stored procedures are and triggers
are.  You can and should use them were you deem appropriate.  There will be
times when a view will 'let you down' since what you want to do with it won't
perform very well -- thats when the developers will tune that one query.  Rather
then have a blanket rule that says "No views", have a rule that says "use the
views unless they don't perform, then we will look at it".  I've written my own
views against the base tables in the data dictionary to do things they can't or
don't (or just perform too slowly).  I think twice before doing so cause every
time you do that, you add another piece that might break when you upgrade --
they same would be true of your application -- every time you query against the
base tables, you have linked your application to that schema, if you feel the
need to change the schema, you have to fix the application.  Not too hard if you
do everything in pl/sql as the dependency mechanism makes it easy to find all of
the broken code, but if you have a large app where some of the sql lives in the
application, it can get harder.

Quote:>: In your case, your main problem is the grants.  Using simple:

>: SQL> create or replace view my_view as select * from t;

>: with no joins, no aggregates, etc should not have any impact on the query plans
>: (i've never seen any with simple views).

>Yes, this one very clear. I got it.

>Once again, tahnk you veyr much for your time.

>Best regards,

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

Getting Role and Permissions from DB

Post by sui.. » Tue, 05 Jan 1999 04:00:00


Hi Thomas:

Thank you very much again for your valuable guidance.

Sorry, I don't understand the last paragraph - Long story short .... Would
you mind giving some examples and alert points when we add another piece
to the view that might break the upgrade, please?

Thanks a lot,

: Long story short:  Views are a tool just like stored procedures are and triggers
: are. <Snip>

: <Snip> I've written my own
: views against the base tables in the data dictionary to do things they can't or
: don't (or just perform too slowly).  I think twice before doing so cause every
: time you do that, you add another piece that might break when you upgrade --
: they same would be true of your application -- every time you query against the
: base tables, you have linked your application to that schema, if you feel the
: need to change the schema, you have to fix the application.  Not too hard if you
: do everything in pl/sql as the dependency mechanism makes it easy to find all of
: the broken code, but if you have a large app where some of the sql lives in the
: application, it can get harder.

 
 
 

Getting Role and Permissions from DB

Post by Thomas Ky » Tue, 05 Jan 1999 04:00:00



(if that email address didn't require changing)

Quote:>Hi Thomas:

>Thank you very much again for your valuable guidance.

>Sorry, I don't understand the last paragraph - Long story short .... Would
>you mind giving some examples and alert points when we add another piece
>to the view that might break the upgrade, please?

An example would be:

- you rename a base table, you must recode the application to use the new base
table.  if you had a view, the view would be changed on the server and the
application would be none the wiser.

- you rename a column in a base table, see above.

- you decide to vertically partition (split) a table.  you must recode the
application to join the information (maybe -- maybe it only accessed columns
from the one table).  A view would make it so you don't have to.

things like that...

>Thanks a lot,


>: Long story short:  Views are a tool just like stored procedures are and triggers
>: are. <Snip>

>: <Snip> I've written my own
>: views against the base tables in the data dictionary to do things they can't or
>: don't (or just perform too slowly).  I think twice before doing so cause every
>: time you do that, you add another piece that might break when you upgrade --
>: they same would be true of your application -- every time you query against the
>: base tables, you have linked your application to that schema, if you feel the
>: need to change the schema, you have to fix the application.  Not too hard if you
>: do everything in pl/sql as the dependency mechanism makes it easy to find all of
>: the broken code, but if you have a large app where some of the sql lives in the
>: application, it can get harder.

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

Getting Role and Permissions from DB

Post by sui.. » Tue, 05 Jan 1999 04:00:00


Hi Thomas:

Thank you very very much for your valuable sharing. I'll keep all of these
treasure in mind.

Have a nice day,

: An example would be:

: - you rename a base table, you must recode the application to use the new base
: table.  if you had a view, the view would be changed on the server and the
: application would be none the wiser.

: - you rename a column in a base table, see above.

: - you decide to vertically partition (split) a table.  you must recode the
: application to join the information (maybe -- maybe it only accessed columns
: from the one table).  A view would make it so you don't have to.

: things like that...

 
 
 

1. DB roles, NT groups and PERMISSIONS

I've been using the PERMISSIONS function in SQL Server 7 to check to
see if the current user is allowed to perform a certain function in
the database.

This works correctly for permissions assigned to the public role, and
it works if the NT user is assigned to any other role (which has
permission to perform the required function) directly.  However, it
doesn't work if the NT user is a member of a group which is assigned
to a role other than public.

eg.
   DB role 'viewers' has access to SP 'get_stuff'
     DOMAIN\Joe member of 'viewers'
         -> PERMISSIONS() = 32
     DOMAIN\Betty member of 'DOMAIN\All Users' member of 'viewers'
         -> PERMISSIONS() = 0      However, she CAN access it...  

So, this has been pretty frustrating lately...  If anyone has any
ideas of what I'm doing wrong, or a different way of doing it, I would
be interested. :)

Thanks in advance,
-Dave

2. US-TN-Memphis Fed Ex Database Specialist Security Issues

3. Trouble getting write permission on an Access DB / Table

4. sql question - need help on julian date conversion

5. how can one prevent db roles access to a db

6. FoxPro 2.6 Professional

7. how can one prevent db roles such as db_sysadmin access to a proprietary db

8. Pick R83 install - protection violation?

9. Getting Errors when Access DB gets large

10. Reading Role Permissions via DSO and VB

11. fails while setting role permissions using DSO

12. Reading Role Permissions via DSO and VB

13. Permission/role setting?