• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 50
  • Last Modified:

only INSERT permission

Hi experts

this user only have INSERT to table RRHH_CONFIRMACION
i execute this
GRANT INSERT ON RRHH_CONFIRMACION TO DB_BOLETAS_PAGO_SMI;

Nevertheless, the user can create table and drop any table
0
enrique_aeo
Asked:
enrique_aeo
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Likely best to first drop all current GRANTs + then only had those you desire.

Note: There might be some sort of wildcard GRANT you also have to remove. I work with MariaDB, so unsure how Oracle's GRANT system works.

If there are global GRANTs (which apply to all users), be very careful when changing these, as you may cause many other user's to start failing basic SQL operations.
0
 
enrique_aeoAuthor Commented:
this DB_BOLETAS_PAGO_SMI only should be INSERT to RRHH_CONFIRMACION
0
 
slightwv (䄆 Netminder) Commented:
The user must have more permissions granted other than just "create session".

Given only these two permissions, they should not be able to create tables:
grant create session to DB_BOLETAS_PAGO_SMI
GRANT INSERT ON RRHH_CONFIRMACION TO DB_BOLETAS_PAGO_SMI;


I also need to mention that you MUST remove ALL other permissions first.  The easiest way is to just drop and recreate the user:
drop user DB_BOLETAS_PAGO_SMI;
create user DB_BOLETAS_PAGO_SMI identified by some_password;

Then the two grants above.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
johnsoneSenior Oracle DBACommented:
2 things not mentioned so far.

Look for grants to PUBLIC.  Those apply to all users.

What user owns the table?  If DB_BOLETAS_PAGO_SMI owns the object, they can do anything they want to it.  You cannot restrict the owner.
0
 
enrique_aeoAuthor Commented:
i try execute this
drop user DB_BOLETAS_PAGO_SMI;

i have this message
ORA-01922 - CASCADE MUST BE SPECIfied
ora-01922.png
0
 
Ganesh GuruduSenior ConsultantCommented:
it seems User already have permissions to Create and drop the table or the table itself have full permission to all users.

You can check the object/user permissions in below tables.
 
SELECT * FROM USER_SYS_PRIVS where USERNAME=''; 
SELECT * FROM USER_TAB_PRIVS; -- Make sure your table should not have GRANTEE to CREATE and DROP object to public 
SELECT * FROM USER_ROLE_PRIVS; 


-- And try to execute the below code and check once.

REVOKE ALL  ON RRHH_CONFIRMACION  FROM DB_BOLETAS_PAGO_SMI; 

grant  insert on RRHH_CONFIRMACION to DB_BOLETAS_PAGO_SMI;

Open in new window

0
 
Ganesh GuruduSenior ConsultantCommented:
To drop the user and all his objects..
use like this : drop user DB_BOLETAS_PAGO_SMI cascade;
0
 
enrique_aeoAuthor Commented:
i connect with this user cn01

SELECT * FROM USER_SYS_PRIVS where USERNAME='DB_BOLETAS_PAGO_SMI';
0 rows

SELECT * FROM USER_TAB_PRIVS where table_name = 'RRHH_CONFIRMACION';
         GRANTEE      OWNER      TABLE_NAME      GRANTOR      PRIVILEGE      GRANTABLE      HIERARCHY
1      DB_BOLETAS_PAGO_SMI      CN01      RRHH_CONFIRMACION      CN01      INSERT      NO      NO

SELECT * FROM USER_ROLE_PRIVS;
1      CN01      ADM_ACTIVO      YES      YES      NO
2      CN01      ADM_COMERCIAL      YES      YES      NO
3      CN01      ADM_COSTOS      YES      YES      NO
4      CN01      ADM_FSOLUTION      YES      YES      NO
5      CN01      ADM_GESTION      YES      YES      NO
6      CN01      ADM_LOGISTICA      YES      YES      NO
7      CN01      ADM_MP2      YES      YES      NO
8      CN01      ADM_OTROS      YES      YES      NO
9      CN01      ADM_PLANILLAS      YES      YES      NO
10      CN01      ADM_PRESUPUESTO      YES      YES      NO
11      CN01      ADM_PRODUCCION      YES      YES      NO
12      CN01      ADM_TEJIDOS      YES      YES      NO
13      CN01      CONNECT      NO      YES      NO
14      CN01      DBA      NO      YES      NO
15      CN01      QUEST_SLP_658_USER      NO      YES      NO
16      CN01      QUEST_SPC_APP_PRIV      NO      YES      NO
17      CN01      RESOURCE      NO      YES      NO
18      CN01      USR_ACTIVO      YES      YES      NO
19      CN01      USR_COMERCIAL      YES      YES      NO
20      CN01      USR_COSTOS      YES      YES      NO
21      CN01      USR_FSOLUTION      YES      YES      NO
22      CN01      USR_GESTION      YES      YES      NO
23      CN01      USR_LOGISTICA      YES      YES      NO
24      CN01      USR_MP2      YES      YES      NO
25      CN01      USR_OTROS      YES      YES      NO
26      CN01      USR_PLANILLAS      YES      YES      NO
27      CN01      USR_PRESUPUESTO      YES      YES      NO
28      CN01      USR_PRODUCCION      YES      YES      NO
29      CN01      USR_TEJIDOS      YES      YES      NO
30      PUBLIC      PLN_AST      NO      YES      NO
31      PUBLIC      PLN_IPSS      NO      YES      NO
32      PUBLIC      PLN_PLLA      NO      YES      NO
33      PUBLIC      PLN_SEGV      NO      YES      NO


i connect with this user DB_BOLETAS_PAGO_SMI

SELECT * FROM USER_SYS_PRIVS where USERNAME='DB_BOLETAS_PAGO_SMI';
         USERNAME      PRIVILEGE      ADMIN_OPTION
1      DB_BOLETAS_PAGO_SMI      CREATE VIEW      NO
2      DB_BOLETAS_PAGO_SMI      CREATE TABLE      NO
3      DB_BOLETAS_PAGO_SMI      CREATE PROCEDURE      NO
4      DB_BOLETAS_PAGO_SMI      UNLIMITED TABLESPACE      NO

SELECT * FROM USER_TAB_PRIVS where table_name = 'RRHH_CONFIRMACION';
         GRANTEE      OWNER      TABLE_NAME      GRANTOR      PRIVILEGE      GRANTABLE      HIERARCHY
1      DB_BOLETAS_PAGO_SMI      CN01      RRHH_CONFIRMACION      CN01      INSERT      NO      NO

SELECT * FROM USER_ROLE_PRIVS;
         USERNAME      GRANTED_ROLE      ADMIN_OPTION      DEFAULT_ROLE      OS_GRANTED
1      DB_BOLETAS_PAGO_SMI      CONNECT      NO      YES      NO
2      DB_BOLETAS_PAGO_SMI      RESOURCE      NO      YES      NO
3      PUBLIC      PLN_AST      NO      YES      NO
4      PUBLIC      PLN_IPSS      NO      YES      NO
5      PUBLIC      PLN_PLLA      NO      YES      NO
6      PUBLIC      PLN_SEGV      NO      YES      NO
0
 
Ganesh GuruduSenior ConsultantCommented:
Still having the issue?
and did you tried below SQLs?
REVOKE ALL  ON RRHH_CONFIRMACION  FROM DB_BOLETAS_PAGO_SMI;
grant  insert on RRHH_CONFIRMACION to DB_BOLETAS_PAGO_SMI;
0
 
slightwv (䄆 Netminder) Commented:
>>ORA-01922 - CASCADE MUST BE SPECIfied

That means the user owns objects in the database.  Before dropping with the CASCADE option mentioned above, you need to know what those objects are.  They may be important.  For example:  I create synonyms for my app users so they don't have to fully qualify table names.

If you drop those, apps might break when you recreate the user without them.

log in as DB_BOLETAS_PAGO_SMI and issue the following:
select object_name, object_type from user_objects;

See if any of those objects are necessary.  We cannot answer that question for you.

>>2      DB_BOLETAS_PAGO_SMI      RESOURCE      NO      YES      NO

There is the reason the user can create tables.  That is what the RESOURCE role gives them.

>>and did you tried below SQLs?

That will not help solve the problem of DB_BOLETAS_PAGO_SMI being able to create tables.  Granting only insert is easy but we need to resolve the question asked first.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now