can you please inform me if i can create a script that dynamiccally grants access on tables to users.
Obviously the following statement does not work but i want to generate something similar to it.
grant select on
(select table_name from user_tables)
thank you in advance
i am already using this method.
However i want to find out if there is a way of directly granting access to users via one sql statement such as the one (obviously it does not work as it is) that i provided in my exampe.
a method like the one i am trying to find out if it exists will limit the time effort needed for similar tasks . (e.g grant select on views, directories packages etc).
well the way i do it
is i have a scripts that create the table to be granted as
I wrote above and run the created script.
very fast , simple and clean ...:)
or you can write a store procedure if u want do use
dynamic sql for the above ( I personally don;t see much point in that )
Dear Guido ,
Is there a good tutorial or a link from which i may have a look?
So far i was working manually having the appropriate roles as you did.
However there is a case where an external user shall have only select priviledges on my database. since the database changes frequently (tables, views,sequences, packages) i want to grant priviledges dynamically without having to create manually the scripts all the time.
doing the job manually is error-prone according to my opinion and needs to be done very carefully.
any way tnx for your response.
Dynamic sql is part of plsql,
as I mentioned above you will need to create pl sql block for it
any way in the pl sql guide below you will find the dynamic syntax
Note that you can also create a DDL trigger that will submit a job that grants different users/ roles different privileges on newly created tables depending on logic you write. That can reduce the administrative overhead and is a life-saver when you have a third-party application that insists on dynamically creating new tables.