首页 » ORACLE » sqlplus 限制用户delete(product_user_profile) sp2-0544

sqlplus 限制用户delete(product_user_profile) sp2-0544

SQL> conn system/manager
Connected.

SQL> insert into product_user_profile(product,userid,attribute,char_value)
2 values(‘SQL*plus’,’ANBOB’,’DELETE’,’DISABLEd’);

1 row created.

SQL> insert into product_user_profile(product,userid,attribute,char_value)
2 values(‘SQL*plus’,’zwz’,’DELETE’,’DISABLEd’);

1 row created.

SQL> commit;

Commit complete.

SQL> select userid,attribute from product_user_profile;

USERID ATTRIBUTE
—————————— ——————–
ANBOB DELETE
zwz DELETE

SQL> show user
USER is “SYSTEM”
SQL> conn / as sysdba
Connected.
SQL> select userid,attribute from product_user_profile;

no rows selected

SQL> show user
USER is “SYS”
SQL> conn anbob/anbob
Connected.
SQL> select userid,attribute from product_user_profile;

USERID ATTRIBUTE
—————————— ——————–
ANBOB DELETE

SQL> show user
USER is “ANBOB”
SQL> select * from test;

ID NAME
———- ———-
1 anbob.com

SQL> delete test where id=1;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> conn system/manager
Connected.
SQL> select userid,attribute from product_user_profile;

USERID ATTRIBUTE
—————————— ——————–
ANBOB DELETE
zwz DELETE

SQL> insert into product_user_profile(product,userid,attribute,char_value)
2 values(‘SQL*Plus’,’zwz’,’DELETE’,’DISABLED’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> COL CHAR_VALUE FOR A10
SQL> RUN
1* select userid,attribute,CHAR_VALUE from product_user_profile

USERID ATTRIBUTE CHAR_VALUE
—————————— ——————– ———-
ANBOB DELETE DISABLEd
zwz DELETE DISABLEd
zwz DELETE DISABLED

SQL> insert into product_user_profile(product,userid,attribute,char_value)
2 values(‘SQL*Plus’,’ANBOB’,’DELETE’,’DISABLED’);

1 row created.

SQL> commit;

Commit complete.

SQL> select userid,attribute,CHAR_VALUE from product_user_profile;

USERID ATTRIBUTE CHAR_VALUE
—————————— ——————– ———-
ANBOB DELETE DISABLED
ANBOB DELETE DISABLEd
zwz DELETE DISABLEd
zwz DELETE DISABLED

SQL> conn anbob/anbob
Connected.
SQL> select userid,attribute,CHAR_VALUE from product_user_profile;

USERID ATTRIBUTE CHAR_VALUE
—————————— ——————– ———-
ANBOB DELETE DISABLED
ANBOB DELETE DISABLEd

SQL> select * from test;

ID NAME
———- ———-
1 anbob.com

SQL> delete test where id=1;
SP2-0544: Command “delete” disabled in Product User Profile

note: 数据只能用system用户查看,sys查看product_user_profile都为空,而且anbob只能查看自己的记录,最重要的是区分大小写,这个表只能限制sqlplus 上的命令操作

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Mark Blose | #1
    2011-12-21 at 06:19

    Is there anywhere to buy this online as it seems very good.