This content has been marked as final. Show 11 replies
if you mean from sqlplus:
select 'grant select on '||table_name||' to user_name;' from user_tables;
will create list of statements then spool to file
and run the script.
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 )
Have a look at dynamic SQL for this if you really feel it is required to do such things.
I never did that. I always had roles to which I added new tables manually.
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
for i in (select table_name from all_tables) loop
execute immediate 'grant select on ' || i.table_name || ' to ' || user_name;
tnx Amie D.
i've already created the required procedure.
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.
justin can you please elaborate on your comment please?
i 've not created a ddl trigger before and definately it will be really interesting to try this out