Excellant Answer
A reader, June 03, 2002 - 12:47 pm UTC
This is a very good solution....
How can I do the same for "Alter user"
Anitha Madhur, June 03, 2002 - 3:48 pm UTC
Hi Tom,
I have the same situation .I would like the application administer be able to alter users ( lock ,unlock,password change) in the app_users table .
How can I do this ?
Thanks in advance,
Anitha
Package to add, drop, alter users
Johan Snyman, June 04, 2002 - 3:02 am UTC
Would it not be better to create a package with procedures for creating, dropping and altering users. Create the package with definers rights (the default) as a user with all the required privileges to create/alter users and just grant the application administrator execute privilege on the package.
The drop procedure in the package can now check that only application users are dropped, the create procedure can ensure that only the required privileges are assigned to a new user and the alter procedure can be limited as required.
This just seem simpler than having to create several different triggers. Also, in another post you indicated that alter user is a very powerful privilege which can potentially open the entire database to the user.
June 04, 2002 - 6:48 am UTC
The trigger relieves the concern of opening the database up -- they can only alter the set of users they are supposed to alter -- not users like SYS for example.
The package would work just as well, very well. It would require about the same amount of code (3 procedures, 3 triggers). I view it as 6 one way, 1/2 dozen the other. Sometimes it is nice to be able to do the DDL (create, alter, drop) rather than call a procedure.
So yes, the package would be a very clean way to do this as well. Now, you just have to worry about the package OWNER having "alter user" ;)
BEFORE TRUNCATE
abz, November 14, 2006 - 10:31 am UTC
How can I disable a specific DDL command for the owner.
Problem is that we have a script in which there are a lot of TRUNCATE commands among other things, for some reason we cannot comment the TRUNCATE command or exclude them in the script. We want that the script should run but the TRUNCATE command should be ignored by the database. I thought of BEFORE TRUNCATE event, I can achieve this but then the script will be terminated. I want the script to be continued.
November 15, 2006 - 6:36 am UTC
You cannot disable a specific DDL command for the owner.
You cannot achieve your goal as stated (thank goodness!! think of the horrible side effects if such a thing could be done - "what, you mean the commands I ran and got return codes of zero didn't actually run" - ouch, hurts my head)
Fix your code.
There is NOT ANY reason on the planet why your code cannot be fixed.
drop user
Irina, November 23, 2010 - 3:17 am UTC
Hi, Tom
I created user
declare
s VARCHAR2(100);
begin
S:='CREATE USER "'||'''Login'||'" IDENTIFIED BY "'||'Pwd'''||'"';
execute immediate(S);
end;
How I can drop user "'Login"?
Regards, Irina
November 23, 2010 - 1:14 pm UTC
ops$tkyte%ORA11GR2> declare
2 s VARCHAR2(100);
3 begin
4 S:='CREATE USER "'||'''Login'||'" IDENTIFIED BY "'||'Pwd'''||'"';
5 dbms_output.put_line( s );
6 execute immediate(S);
7 end;
8 /
CREATE USER "'Login" IDENTIFIED BY "Pwd'"
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> drop user "'Login";
User dropped.