| Unable to "write-lock" a table/schema/database fror a user [message #52803] |
Wed, 14 August 2002 16:33  |
Travis
Messages: 15
Registered: March 2001
|
Junior Member |
|
|
I have a user that only read from a database
I am trying to prevent a certain user from being able to make any changes to a table. I have removed all but the connect role, and all but the "Select Any Table" privlege, but logging in as the user I can still commit deletes. What am I missing? Should I focus on the tablespace or the schema?
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: Unable to "write-lock" a table/schema/database fror a user [message #52829 is a reply to message #52803] |
Thu, 15 August 2002 12:41  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Run this to find out what the user has:
set echo off
set verify off
set pages 200
col granted_role form a20
col owner form a12
col table_name form a27
col privilege form a27
ACCEPT username prompt 'Enter Username : '
spool privs_&username..lst
PROMPT Roles granted to user
SELECT granted_role,admin_option,default_role
FROM dba_role_privs
WHERE grantee=UPPER('&username');
PROMPT Table Privileges granted to a user through roles
SELECT granted_role, owner, table_name, privilege
FROM ( SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
UNION
SELECT granted_role
FROM role_role_privs
WHERE role in (SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
)
) roles, dba_tab_privs
WHERE granted_role=grantee;
PROMPT System Privileges assigned to a user through roles
SELECT granted_role, privilege
FROM ( SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
UNION
SELECT granted_role
FROM role_role_privs
WHERE role in (SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
)
) roles, dba_sys_privs
WHERE granted_role=grantee;
PROMPT Table privileges assigned directly to a user
SELECT owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee=UPPER('&username');
PROMPT System privileges assigned directly to a user
SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee=UPPER('&username');
spool off
|
|
|
|
| Re: Unable to "write-lock" a table/schema/database fror a user [message #52831 is a reply to message #52803] |
Thu, 15 August 2002 15:34 |
Travis
Messages: 15
Registered: March 2001
|
Junior Member |
|
|
I get the following in response to your recommended script:
PRIVILEGE ADM
--------------------------- ---
SELECT ANY TABLE NO
SQL> @C:user_perm.sql
Enter Username : user
Roles granted to user
GRANTED_ROLE ADM DEF
-------------------- --- ---
CONNECT YES YES
Table Privileges granted to a user through roles
no rows selected
System Privileges assigned to a user through roles
GRANTED_ROLE PRIVILEGE
-------------------- ---------------------------
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE DATABASE LINK
CONNECT CREATE SEQUENCE
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE TABLE
CONNECT CREATE VIEW
8 rows selected.
Table privileges assigned directly to a user
no rows selected
System privileges assigned directly to a user
PRIVILEGE ADM
--------------------------- ---
SELECT ANY TABLE NO
I have removed everything from CONNECT except ALTER SESSION and CREATER SESSION. Still I can delete.
I haven't explicitly granted object permissions on the tables either. Am I back to just write locking the tablespace? Is the schema owner just implicitly allowed to have their way with the table?
|
|
|
|
| Re: Unable to "write-lock" a table/schema/database fror a user [message #52834 is a reply to message #52803] |
Thu, 15 August 2002 16:43 |
Travis
Messages: 15
Registered: March 2001
|
Junior Member |
|
|
I get the following in response to your recommended script:
PRIVILEGE ADM
--------------------------- ---
SELECT ANY TABLE NO
SQL> @C:user_perm.sql
Enter Username : user
Roles granted to user
GRANTED_ROLE ADM DEF
-------------------- --- ---
CONNECT YES YES
Table Privileges granted to a user through roles
no rows selected
System Privileges assigned to a user through roles
GRANTED_ROLE PRIVILEGE
-------------------- ---------------------------
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE DATABASE LINK
CONNECT CREATE SEQUENCE
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE TABLE
CONNECT CREATE VIEW
8 rows selected.
Table privileges assigned directly to a user
no rows selected
System privileges assigned directly to a user
PRIVILEGE ADM
--------------------------- ---
SELECT ANY TABLE NO
I have removed everything from CONNECT except ALTER SESSION and CREATER SESSION. Still I can delete.
I haven't explicitly granted object permissions on the tables either. Am I back to just write locking the tablespace? Is the schema owner just implicitly allowed to have their way with the table?
|
|
|
|
| Re: Unable to "write-lock" a table/schema/database fror a user [message #52844 is a reply to message #52803] |
Fri, 16 August 2002 04:57 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
The owner of the schema has all rights to objects it has created. If you do not want a user to have this kind of access you need to create another schema and you keep the password. Grant select on the table(s) that should be read only. For example say you have projectx. Create the schema projectx. Create all your tables and objects and load your data. Then grant access to the data.
SQL> grant select on projectx.tabe1 to scott; -- read only
SQL> grant update,delete,insert,select on projectx.tabe1 to adm; -- full access
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
