CODEWITHSUNDEEP
oracle-database
Luis Alberto Gonzalez Diaz
Luis Alberto Gonzalez Diaz

Reputation: 89

How can i restrict a user from creating tables in their own schema?

My user only has the privilege to create session, manage scheduler. But I can create tables in the schema, can I change that?

Thank you.

Upvotes: 0

Views: 522

Answers (1)

EdStevens
EdStevens

Reputation: 3872

The if the user is able to create a table, then he has more privs than you claim. Here I create a user, grant him ONLY the privs you say, then connect as that user and try to create a table:

SQL> show user
USER is "SYSTEM"
SQL> create user beetle identified by bailey;

User created.

SQL> grant create session to beetle;

Grant succeeded.

SQL> grant manage scheduler to beetle;

Grant succeeded.

SQL> select privilege
  2  from dba_sys_privs
  3  where grantee='BEETLE';

PRIVILEGE
----------------------------------------
MANAGE SCHEDULER
CREATE SESSION

2 rows selected.

SQL> select granted_role
  2  from dba_role_privs
  3  where grantee='BEETLE';

no rows selected

SQL> select owner ||'.'||table_name,
  2         privilege
  3  from dba_tab_privs
  4  where grantee='BEETLE';

no rows selected

SQL> -- -------- create the tables
SQL> conn beetle/bailey@pdb01
Connected.
SQL> create table my_test(dob date);
create table my_test(dob date)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> -- -------- load tables
SQL> -- -------- Do the query
SQL> -- -------- clean up
SQL> conn system/halftrack@pdb01
Connected.
SQL> drop user beetle cascade;

User dropped.

Upvotes: 1

Related Questions