Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Preventing the usage of SELECT *

Jan 8 2015 12:00AM by Madhivanan   

One of my friends asked "Is it possible to prevent users from using SELECT * from a table in the queries?" Well. The one way I can think of is to create a view that has all the columns from the table and add an extra column as an invalid derived expression. 


Let us consider the following table
create table employee
(
emp_id char(10),
emp_name varchar(100),
dob datetime,
address varchar(100),
phone varchar(20)
)

insert into employee(emp_id,emp_name,dob,address,phone)
select 'EMP0000001','Madhivanan','20000101','Chennai,India','+91299200200'

You can now use * in the SELECT statement
select * from employee
To prevent this create the following view
create view emp_view
as
select *,cast('@@@' as datetime) as error_col from employee 

Now you can not use SELECT * on this view
select * from emp_view 
The above results to
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
So you need to explicitly type out the required column names to get the data.
select emp_id,emp_name,dob,address,phone from emp_view
The result of the above is
emp_id     emp_name        dob                     address           phone
---------- --------------- ----------------------- ----------------- -----------------
EMP0000001 Madhivanan      2000-01-01 00:00:00.000 Chennai,India     +91299200200
You need to also DENY SELECT permission to the users on that table and GRANT SELECT permission to the view

If you know any other methods, post it in the comment section

Tags: 


Madhivanan
2 · 40% · 13039
3
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

4  Comments  

  • Nice Tip Madhivanan. The same concept can be applied to tables by use of computed columns.

    create table t1
    ( c1 int
    , c2 nvarchar(100)
    , c3 as cast(c2 as int))
    
    insert into t1 (c1,c2)
    select 1,'test'
    
    -- this will fail
    select * from t1
    
    -- this will work
    select c1,c2 from t1
    
    commented on Jan 8 2015 6:33AM
    Chintak Chhapia
    40 · 5% · 1477
  • Thanks Chintak. But sometime it may not be feasible to change the table structure. In such cases, you can use views

    commented on Jan 8 2015 7:11AM
    Madhivanan
    2 · 40% · 13039
  • Yes Very true. Also people who use "select *" may be also using "Insert" without mentioning columns.

    commented on Jan 8 2015 7:29AM
    Chintak Chhapia
    40 · 5% · 1477
  • I think the classic solution is to use a stored proc with ownership chaining. Deny the user permission to select against the table but create the stored proc using an account with permissions to query the table. Then grant the user permission to run the stored proc. Ownership chaining takes care of it.

    commented on Jan 8 2015 8:50AM
    Michael Coles
    163 · 1% · 304

Your Comment


Sign Up or Login to post a comment.

"Preventing the usage of SELECT *" rated 5 out of 5 by 3 readers
Preventing the usage of SELECT * , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]