I would like to apply column level security in a table to an owner using PUP (product _user_profile). In this case, once I fix the DROP attribute for this user, the user can't DROP the table.
But the user can still drop the table by column through "alter table drop column (col.name)." This is possible for one column. After that, the user can create one more temporary column for that particular table and DELETE the rest of the first column. So how do I secure the table through PUP?
If your problem is that you don't want anyone to be able to ALTER or DROP the table, then create another database user and grant only INSERT, UPDATE, DELETE and SELECT privileges to that user. Typically, it is bad practice to use the schema owner for application connectivity. In fact, in just about every database I work with (except for vended packages that have specific requirements), I lock the schema owner's database account so that it cannot be used for logon.
If you've figured out how to use the PUP table to disable the DROP command, then you just need to also disable the ALTER command so that the user is not allowed to use the ALTER statement. The SQL*Plus User's guide provides detailed documentation on how to configure the PUP table entries.
I'll also add a word of caution regarding PUP. The PUP table is not a sufficient security measure in any case. If you have a need to protect tables or their data, you need to use database privileges to provide the protection. I can download a free copy of TOAD or use ODBC drivers to bypass the PUP table protections (which are only read and obeyed by SQL*Plus). Therefore, all your users have to do is use a different tool to connect to the database (and there are many free tools available for download), and they've bypassed the "security" that you've set up with PUP.
This was first published in December 2004